CakePHP find list with related model conditions

4
votes

I'm trying to get a list of IDs (a la find('list')), but can't figure out how to do it with related model conditions.

My associations are: Product hasAndBelongsToMany Category Product belongsTo Manufacturer

I want to find a list of product IDs that are in a list of category IDs and have a certain manufacturer seo_url. I've tried tons of contains with conditions, conditions referencing other models, even tried using find('all') to see if my conditions would work, but couldn't get anything.

I managed to get one with Product->query(), but it's not in the format of a list. I could cycle through the array and grab IDs or whatever, but it feels awfully un-Cakey. I want to make sure Cake still does all its security checks on my queries and whatever other automagic it does. Here's the query I got working:

$all_results = $return['Category']['all_results'] = $this->Product->query('
    SELECT `Product`.`id`
        FROM `categories_products` as `CategoriesProduct`
        JOIN `products` as `Product` ON (`CategoriesProduct`.`product_id` = `Product`.`id`)
        JOIN `manufacturers` as `Manufacturer` ON (`Product`.`manufacturer_id` = `Manufacturer`.`id` AND `Manufacturer`.`seo_url` = \''.$manufacturer.'\')
        WHERE `CategoriesProduct`.`category_id` IN ('.implode(',', $search_categories).')
');

this returns

[all_results] => Array
    (
         [0] => Array
            (
                [Product] => Array
                    (
                        [id] => 101787
                    )

            )

        [1] => Array
            (
                [Product] => Array
                    (
                        [id] => 100781
                    )

            )

        [2] => Array
            (
                [Product] => Array
                    (
                        [id] => 101887
                    )

            )

        [3] => Array
            (
                [Product] => Array
                    (
                        [id] => 101888
                    )

            )

    )

Note: $search_categories is the list of category ID's (ie: array(12,42,24,5))

3
phpmysqlcakephprelational-database

3 Answers

3
votes

The problem with your wanted result is, that Cake won't give you back a stripped result array if you use conditions on your related models.

This happens because Cake will only use these conditions on your related model and give you back the results with conditions being true on your related models.

If you want get back products with only specific Category you need to query through the Category-Model as this gives you the possibility to use the conditions on your Products. This could look something like this:

$this->Category->find('all', array('conditions' => array('Category.id' => 2));

This will give you back only the wanted Category and its associated Products. However, this is not very satisfying if you want a list, because you would have to manually do the conversion.

I would rather take a look at the Linkable Plugin which should give you exactly your wanted functionality as it extends Cake in using joins as you did in your query. This makes it possible on getting results with conditions on your related models.

3
votes

CakePHP can be odd about conditions for related models. Especially with HABTM relationships. Even when recursive is set to the highest value (i.e. 2). Check out the docs for more detail about HABTM.

Try the following. Although from the above, I don't think it will work:

$conditions = array('Category.id' => $category_ids, 'Manufacturer.seo_url' => $manufacturer);
$this->Product('list', array('recursive' => 1, 'conditions' => $conditions));

Also, avoid query() whenever possible. The whole point of MVC is to insulate data from display from logic. Using things like query() just breaks that.

0
votes

Do a normal condition and change into list format after.

$this->loadModel('AlertStatuses');  
$lists = $this->AlertStatuses->find('all', [ 
'conditions'=>['AlertStatuses.id > 2']]);
$alert_statuses  = array();
foreach($lists as $list) {
  $alert_statuses[$list->id] = $list->name;
}
$this->set('alert_statuses', $alert_statuses);