CakePHP find all condition being ignored

Question

I'm using CakePHP 1.3 and am having some trouble converting a SQL query to CakePHP. The concept behind the query is to retrieve all products associated with a specified account and belonging to a certain product category or the specified category's immediate child.

There are three tables involved here, products, product_categories and category_types. product_categories is a link table that joins products to category_types. The category_types table is a tree structure that can join to itself.

The SQL for the query should end up looking something like this if the category's id was 1 and the account's id was 1:

SELECT Product.id, Product.name, Product.account_id
FROM products AS Product
INNER JOIN product_categories AS pc ON (Product.id = pc.product_id) 
INNER JOIN category_types AS ct ON (ct.id = pc.category_type_id) 
WHERE ( 
   ((Product.account_id IS NULL) OR (Product.account_id = '1'))
   AND ((ct.id = '1') OR (ct.parent_id = '1')) 
)

Here is the code I'm using to try and do this, which is located in a function in my model:

$this->find('all', array(
    'joins' => array(
        array(
            'table' => 'product_categories',
            'alias' => 'pc',
            'type' => 'INNER',
            'conditions' => array(
                'Product.id = pc.product_id'
            )
        ),
        // get category type
        array(
            'table' => 'category_types',
            'alias' => 'ct',
            'type' => 'INNER',
            'conditions' => array(
                'pc.category_type_id = ct.id'
            )
        )
    )            
    , 'conditions'=> array(
        'OR' => array(
            'Product.account_id IS NULL'
            , 'Product.account_id' => $account_id
        )
        , 'OR' => array(
            'ct.id' => $categoryTypeId
            , 'ct.parent_id' => $categoryTypeId
        )
    )
));

The problem is that the query is ignoring the account_id OR conditions resulting in SQL like this:

SELECT Product.id, Product.name, Product.account_id
FROM products AS Product
INNER JOIN product_categories AS pc ON (Product.id = pc.product_id) 
INNER JOIN category_types AS ct ON (ct.id = pc.category_type_id) 
WHERE ((ct.id = '1') OR (ct.parent_id = '1'))

How can I alter my php code to get the desired results?

You have in your conditions array, declared the key "OR" twice, so the second one is overwriting the first. You'll need to do something that allows multiple or conditions at once, perhaps something like in stackoverflow.com/questions/13890704/… — Kai

Mark Mark · Accepted Answer · 2013-10-10T20:09:32

Thanks to user2076809 for pointing me in the right direction. The solution is to wrap the two OR conditions each in their own array.

$this->find('all', array(
    'joins' => array(
        array(
            'table' => 'product_categories',
            'alias' => 'pc',
            'type' => 'INNER',
            'conditions' => array(
                'Product.id = pc.product_id'
            )
        ),
        // get category type
        array(
            'table' => 'category_types',
            'alias' => 'ct',
            'type' => 'INNER',
            'conditions' => array(
                'pc.category_type_id = ct.id'
            )
        )
    ),            
    'conditions'=> array(
        array(
            'OR' => array(
                'Product.account_id IS NULL'
                , 'Product.account_id' => $account_id
            )
        ),
        array(
            'OR' => array(
                'ct.id' => $categoryTypeId
                , 'ct.parent_id' => $categoryTypeId
            )
        )
    )
));

CakePHP find all condition being ignored

2 Answers