CakePHP 3.6 Query Builder复杂嵌套AND/OR条件构建问题

Got it, let's work through building those complex nested conditions for your query. From your code snippet, it looks like you're using CakePHP's ORM—so here are two straightforward ways to construct the nested AND/OR logic you need.

First, let's assume your target SQL looks something like this (adjust the conditions to match your actual requirements):

SELECT * FROM vehicle_brand_models 
WHERE status = 1 
AND (
    brand = 'Toyota' 
    OR (
        model = 'Civic' 
        AND year > 2020
    )
);

Method 1: Array-Based Nested Conditions (Simpler for Basic Nesting) #

CakePHP's Query Builder supports nested arrays for AND/OR groups directly in the where() method. This is great if you prefer a more declarative, array-driven approach:

// Start with your base condition
$query = $this->VehicleBrandModels->find()
    ->hydrate(false)
    ->select($this->VehicleBrandModels)
    ->where([
        'VehicleBrandModels.status' => 1,
        // Nested OR group
        'OR' => [
            'VehicleBrandModels.brand' => 'Toyota',
            // Nested AND group inside the OR
            [
                'AND' => [
                    'VehicleBrandModels.model' => 'Civic',
                    'VehicleBrandModels.year >' => 2020
                ]
            ]
        ]
    ]);

$VBM_data = $query->toArray();

Method 2: Closure-Based Expressions (Better for Complex/Dynamic Logic) #

If your conditions are dynamic (e.g., you need to add clauses conditionally based on user input), using a closure with the Expression object gives you more flexibility:

$query = $this->VehicleBrandModels->find()
    ->hydrate(false)
    ->select($this->VehicleBrandModels)
    ->where(function ($exp) {
        // Base condition: status = 1
        $baseCondition = $exp->eq('VehicleBrandModels.status', 1);
        
        // Build the nested OR group
        $orGroup = $exp->or_(
            // First OR clause: brand = 'Toyota'
            $exp->eq('VehicleBrandModels.brand', 'Toyota'),
            // Second OR clause: nested AND group
            $exp->and_(
                $exp->eq('VehicleBrandModels.model', 'Civic'),
                $exp->gt('VehicleBrandModels.year', 2020)
            )
        );
        
        // Combine base condition with the OR group using AND
        return $exp->and_($baseCondition, $orGroup);
    });

$VBM_data = $query->toArray();

Key Notes #

• You can mix and match both approaches depending on your use case.
• For dynamic conditions (like adding clauses only if a filter is set), the closure method is easier to work with—you can use if statements inside the closure to append conditions as needed.
• Always use the expression methods (eq(), gt(), like(), etc.) or the array syntax with operators (e.g., 'year >' => 2020) to ensure proper SQL escaping and avoid injection risks.

内容的提问来源于stack exchange，提问作者Chinmaya Panigrahi