CakePHP HABTM association rules

Question

If I want to create a category and be able to link products to it by tags I can like so:

Create the category and product tables.
Create a tags table with tags like: Ruby, Earrings, White-Gold
Create a category_tags and product_tags table to map them
Set category and product to hasAndBelongsToMany tags
Set tags to hasAndBelongsToMany products and hasAndBelongsToMany categories

Now say I have 2 products one with tags: Ruby and Earrings and another with tags: Ruby and Bracelet

Say I want to create a Ruby Earrings category.

I could add the Ruby and Earrings tags to the category. But under normal HABTM Model associations both products will be returned because even though only 1 has an earrings tag they both have a ruby tag.

How can I make it only match products that have ALL of the same tags as the category (products can have more tags but must have all the tags the corresponding category has) in order be returned?

Also, taking that even further, how could I add -tags to a category that the products must NOT have these tags to be returned?

JD Isaacks JD Isaacks · Accepted Answer · 2010-12-21T19:53:07

The script below solved my issue by generating a query like so:

PHP

$data = $this->Designer->find(
    'first', 
    array(
        'conditions' => array(
            'Designer.slug' => $name, 
            'Designer.available' => 1
        )
    )
);

$inc_tag_ids = array();
$exc_tag_ids = array();
foreach($data["Tag"] as $tag)
{
    if( $tag['DesignersTag']['include'] )
    {
        $inc_tag_ids[] = $tag['id'];
    }
    else
    {
        $exc_tag_ids[] = $tag['id'];
    }
}

$ins = ' ';

if( count($inc_tag_ids) )
{
    $inc_tag_id_str = '"' . implode('","',$inc_tag_ids) . '"';
    $ins .= 'AND tags.id IN ('.$inc_tag_id_str.')';
}   

if( count($exc_tag_ids) )
{
    $exc_tag_id_str = '"' . implode('","',$exc_tag_ids) . '"';
    $ins .= '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 ('.$exc_tag_id_str.')
    )';
}   

$prod_qry = '
    SELECT *, COUNT(DISTINCT tags.name) AS uniques 
    FROM products, products_tags, tags 
    WHERE products.id = products_tags.product_id 
    AND tags.id = products_tags.tag_id 
    '.$ins.'
    GROUP BY products.id
    HAVING uniques = '.count($inc_tag_ids).' 
';

echo $prod_qry;

$data["matching_products"] = $this->Designer->Tag->query($prod_qry);

SQL

SELECT * , COUNT( DISTINCT tags.name ) AS uniques
FROM products, products_tags, tags
WHERE products.id = products_tags.product_id
AND tags.id = products_tags.tag_id
AND tags.id
IN (
"8"
)
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 (
"7"
)
)
GROUP BY products.id
HAVING uniques =1

However I feel like this is not the way CakePHP is inteded to be treated, I think maybe this is something that should be handled in the model not in the controller. But I am not sure how to do that.

CakePHP HABTM association rules

1 Answers