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)
)