• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[Solved] Complicated MySQL Query (Sets)

I'm having a bit of trouble coming up with a complicated MySQL query.

I have three tables:

* 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:

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.

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?

You should consider using an ORM libary like DMZ.
It really simplifies the task of handling related objects.

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.

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.

@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?

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:
$product = new Product();
$tag = new Tag();


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

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

Nice. And funny to see that both our solutions are in there. So we both had a pretty good idea Smile
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!

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  

  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.