Using DQL in Doctrine2, how do I select Products and their Tags only for Products that have a specific Tag?

Question

I have a simple data model: a Product can have many Tag objects.

The Tag model has a tag field and a value field, both strings (value is not important here).

The following DQL query gets me all Products and their tags:

SELECT p, t FROM Product p LEFT JOIN p.tags t

However, how do I only select Products that have a certain tag (e.g. "blue")? It is important that I get all the tags for the returned product, so I cannot simply do a WHERE t.tag = 'blue'.

As a side question; do you have any thoughts on whether it would be better to implement this using a separate Tag table, and then having a ProductTag table knitting them together (proper many-to-many relation)?

Answer 1

You can use a DQL query like this:

"SELECT p, t FROM Product p LEFT JOIN p.tags t WHERE p.id IN (
    SELECT sp.id FROM Product sp INNER JOIN sp.tags st WHERE st.tag = 'blue'
)"

This will return all product object where a Tag.tag = 'blue' is found in their collection of tags, but also other tags are joined.

I tried this out in a Symfony2 project and it worked like this. Because I got all the information used for this from the Doctrine Documentation I think it will work too in the standalone version.

If it doesn't work tell me the error and I will take a close look at this problem.