[Solved] Complicated MySQL Query (Sets)

[eluser]nzmike[/eluser]
I'm having a bit of trouble coming up with a complicated MySQL query.

I have three tables:

product
* id

product_tags
*product_id
*tag_id

tag
*id

Let's say the user is searching for products that have tags 1 & 2.

How would I construct a query which returns the products which have both tags.

So far my I have tried the following ways:

Code:

SELECT * FROM product, product_tag WHERE product.id = product_tag.product_id  AND product_tag.id IN(1,2);

This kind of worked but it would return results which meet either tags, I need a query which will only return results that have both.

Code:

SELECT * FROM product, product_tag WHERE product.id = product_tag.product_id  AND product_tag.id = 1 AND product_tag.id = 2;

Seemed fine in my head but when I ran in it returned nothing. Once I actually thought about it it's obvious why, how can a product_tag.id be both 1 and 2.

So now I'm some what stuck. The only way I can see of doing this is using the first query and grouping on the product.id then doing a count on the resulting group to make sure it matches the number of tags I am searching on. I thought I'd post here because I might be missing something. Is there an easy way of matching a whole set in MySQL?

[eluser]gyo[/eluser]
You should consider using an ORM libary like DMZ.
It really simplifies the task of handling related objects.

[eluser]mddd[/eluser]
If you need all tags to be linked, you could do something like this. The requested tag id's are 123 and 456 in this example.

Code:

SELECT product.* FROM product
JOIN product_tag tag1 ON tag1.product_id = product.id AND tag1.tag_id = 123
JOIN product_tag tag2 ON tag2.product_id = product.id AND tag2.tag_id = 456

So, you join the product able against the product_tag table several times. Each time on the condition that the tag matches.
This is easy to write in Active Record in CI, no matter how many tags were selected.

[eluser]nzmike[/eluser]
@gyo/suashi - I've briefly look at some ORM libraries but don't like the idea of having the extra overhead. How would an ORM library solve my problem?


@mdd - Ahh yes I can see that would work. It's probably slower than my way but a lot simpler, so I would probably do it your way so far.

Any other ideas?

[eluser]gyo[/eluser]
Adding an ORM library it's definitely overkill if that's the only related table you're going to have.

But you would end up doing something like this:

Code:

$product = new Product();
$tag = new Tag();

$product->tag->get();

foreach ($product->tag->all as $tag)
{
    echo 'Tag name: ' . $tag->name . '<br />';
}

[eluser]nzmike[/eluser]
Just found this fantastic page which has answered all my questions regarding tagging. Check it out - Tagging article

[eluser]mddd[/eluser]
Nice. And funny to see that both our solutions are in there. So we both had a pretty good idea
It says in the page that the 'multiple join' solution is more efficient because 'group by / count' takes a temporary table.
Good to know, I think! So we've both learned from this!