How to remove parent model data by filtering on child model data?

Question

Task

I'm trying to return a set of data based on a condition in the related model.

The problem

Currently the closest I can get is using Containable to return all matching model data, but only returning child data if it matches the contain condition. This isn't ideal as my data still contains the primary model data, rather than it being removed.

I am using a HABTM relationship, between, for example, Product and Category, and I want to find all products in a specific category.

Inital idea

The basic method would be using containable.

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

Although this will return all products, and just remove the Category dimension if it doesn't match the contain condition.

Closest so far

$this->Product->find('all', array(
    'contain' => false,
    'joins' => array(
        array(
            'table' => 'categories_products',
            'alias' => 'CategoriesProduct',
            'type' => 'LEFT',
            'conditions' => array(
                'CategoriesProduct.product_id' => 'Product.id'
            )
        ),
        array(
            'table' => 'categories',
            'alias' => 'Category',
            'type' => 'LEFT',
            'conditions' => array(
                'Category.id' => 'CategoriesProduct.category_id'
            )
        )
    ),
    'conditions' => array(
        'Product.status_id' => 1,
        'Category.id' => $categoryId
    ),
));

Which generates the following query,

SELECT `Product`.`id`, `Product`.`name`, `Product`.`intro`, `Product`.`content`, `Product`.`price`, `Product`.`image`, `Product`.`image_dir`, `Product`.`icon`, `Product`.`icon_dir`, `Product`.`created`, `Product`.`modified`, `Product`.`status_id` 
FROM `skyapps`.`products` AS `Product` 
LEFT JOIN `skyapps`.`categories_products` AS `CategoriesProduct` ON (`CategoriesProduct`.`product_id` = 'Product.id') 
LEFT JOIN `skyapps`.`categories` AS `Category` ON (`Category`.`id` = 'CategoriesProduct.category_id') 
WHERE `Product`.`status_id` = 1 
AND `Category`.`id` = 12

This query is correct, except that the join conditions are being quoted ' instead of `, which breaks the query.

Manual query

SELECT * 
FROM products
JOIN categories_products ON categories_products.product_id = products.id
JOIN categories ON categories.id = categories_products.category_id
WHERE categories.id = 12

David Yell David Yell · Accepted Answer · 2013-02-20T11:09:08

The problem lay in the way I was defining my join conditions. It's not an associative array but rather a string.

        'conditions' => array(
            'CategoriesProduct.product_id' => 'Product.id'
        )

Changes to

        'conditions' => array(
            'CategoriesProduct.product_id = Product.id'
        )