How to tell a model how to find its associated models?

First let m开发者_StackOverflowe note that I am new to MVC frameworks so don't assume any knowledge in that area.

I have a Designer model, I want to pull associated products for each designer when I retrieve info from the designer model.

The association isn't simple so cake won't magically do it for me so I need to define this myself. I have been following tutorials and I think I am close but unsure how to tie it all together.

I have a very long query that if you plug in the designer_id in the right places it will select all the associated products.

I think I am supposed to put this query somewhere in the Designer model so that it knows how to retrieve the products.

Here is the query, the parts of the string that look like {$__cakeID__$} need to be replaced with the designer_id:

SELECT *, COUNT(DISTINCT tags.name) AS uniques 
FROM products, products_tags, tags, designers, designers_tags 
WHERE products.id = products_tags.product_id 
AND tags.id = products_tags.tag_id 
AND tags.id IN (
    SELECT t.id
    FROM tags t
    LEFT JOIN designers_tags dt ON dt.tag_id = t.id
    LEFT JOIN designers d ON d.id = dt.designer_id
    WHERE d.id ={$__cakeID__$}
    AND dt.include =1
)
AND products.id NOT IN ( 
    SELECT products.id 
    FROM products, products_tags, tags 
    WHERE products.id = products_tags.product_id 
    AND tags.id = products_tags.tag_id 
    AND tags.id IN (
        SELECT t.id
        FROM tags t
        LEFT JOIN designers_tags dt ON dt.tag_id = t.id
        LEFT JOIN designers d ON d.id = dt.designer_id
        WHERE d.id ={$__cakeID__$}
        AND dt.include =0
    ) 
)
AND designers.id = designers_tags.designer_id
AND designers_tags.tag_id = tags.id
GROUP BY products.id 
HAVING uniques = (
    SELECT COUNT(d.id) AS tag_count 
    FROM tags t
    LEFT JOIN designers_tags dt 
        ON dt.tag_id = t.id
    LEFT JOIN designers d 
        ON d.id = dt.designer_id
    WHERE d.id = {$__cakeID__$} 
    AND dt.include =1
    GROUP BY d.id
)

Also here is the designer model:

class Designer extends AppModel 
{    
    var $name = 'Designer';

    var $actsAs = array('Sluggable' => array('separator' => '-', 'overwrite' => false, 'label' => 'name')); 

    var $hasAndBelongsToMany = 'Tag';

    var $hasOne = 'AlternateName';

    var $hasMany = 'Vote';
}

What would I need to do to make the Designer model use this query to automatically find its associated products?

This query should work using an adhoc join and the Containable behavior:

// this assumes you are in the DesignersController
$this->Designer->Tag->find('all', array(
    'joins' => array(
        array(
            'table' => 'designers_tags',
            'alias' => 'FilterDesignersTag',
            'type' => 'INNER',
            'conditions' => array(
                'FilterDesignersTag.tag_id = Tag.id'
            )
        )
    ),
    'contain' => array('Designer', 'Product'),
    'conditions' => array(
        'FilterDesignersTag.designer_id' => $designer_id,
        'FilterDesignersTag.include' => 1
    )
));

The data will come back oriented around the tags, but you'll get what you need. Also, you'll need to make sure you define the HABTM relationships in all three models, Designer, Product, and Tag (tag should have two HABTM associations).

Also, you'll need to add var $actsAs = array('Containable'); to your models.

EDIT

If you cannot find a CakePHP solution, and end up needing to use a raw query, CakePHP can do it with the model query method. Understand that you are giving up some features when using this method. Also take note of this advice from the cookbook:

If you're ever using this method in your application, be sure to check out CakePHP's Sanitize library, which aids in cleaning up user-provided data from injection and cross-site scripting attacks.