On Wed, Jun 30, 2010 at 3:28 PM, Aurimas Likas <[email protected]> wrote:
> I want to ask Im sure a really simple question (at laest for most of you).
> Here is the situation:
>
> I have 3 DB tables: products, tags and products_tags. products_tags has 2
> columns product_id and tag_id (for many-to-many relationship). So the
> question is: how can I select products which have tag1 AND tag2 and tag3 and
> .... tagN? I need a best way in terms of performance. The only way I can
> think of now is to create a query with self joins. The query joins number
> would equal tags number - 1. Is there a better way? Maybe I miss here
> something fundamental (think). My project products need a way to be filtered
> by various tags...
>
> Thanks in advance.
>
> --
> Aurimas L.
>
Does this work?
<?php
$tags = array('foo', 'bar', 'widget');
$inner = $db->select()
->from('product_tags', 'product_id')
->join('tags', '`product_tags`.`tag_id` = `tags`.`tag_id`')
->where('`tags`.`tag` IN (?)', $tags)
->where('`products`.`product_id` = `product_tags`.`product_id`')
->group('`products`.`product_id`')
->having('COUNT(*) = ?', count($tags));
$select = $db->select()
->from('products',
array('product_id', 'product_name', 'price'))
->where('EXISTS (?)', $inner);
?>
It should generate a statement that looks something like this:
SELECT `products`.`product_id`, `products`.`product_name`,
`products`.`price`
FROM `products`
WHERE EXISTS (
SELECT `product_id`
FROM `product_tags`
INNER JOIN `tags`
ON `tags`.`tag_id` = `product_tags`.`tag_id`
WHERE `tags`.`tag` IN ( 'foo', 'bar', 'widget' )
AND `product_tags`.`product_id` =
`products`.`product_id`
GROUP BY `products`.`product_id`
HAVING COUNT(*) = 3
)
It uses an aggregate sub-query to find rows for product IDs that match
all the tags, and then applies the result set to the products table.
Andrew
