I have an Article entity in Doctrine, which has a many-to-many relationship with Tag. That is, an Article can be "tagged" with multiple tags, and they are bound together by the articles_tags table in the database.
Let us assume we wanted to find all Articles that are associated with given Tag. For the case of an example, let us say we wanted to find all Articles associated with the "cars" tag: an article about cars.
Had this been regular SQL (or some flavor of it), I would have written a query in a similar manner to the following:
SELECT * FROM articles_tags WHERE tag_id IN (
SELECT id FROM tags WHERE name = 'cars')
This would give us all article_tags where there is a Tag that goes by the name "cars". Of course, should more than one tags be used in the query at one time, duplicate articles should also be thrown out: perhaps by using a GROUP BY. Furthermore, you could even get rid of the intermediary step of first selecting the article_tags and then going to the Articles, by writing a longer query.
From my current understanding of Doctrine, which ranges no more than a few days, you cannot directly reference intermediary tables; nor does it seem as though you can write subqueries using the DQL. As such, I am at a loss.
Any pointers as to where I should start writing the query from, or any information regarding how one in general might go about handling these types of database retrievals in an ORM such as Doctrine would be highly helpful!
Query in DQL is a bit simpler than pure SQL:
$q = "
SELECT a FROM AppBundle:Article
LEFT JOIN a.tags t
WHERE t.name = :tag";
$result = $em->createQuery($q)
->setParameter('tag', $tag)
->getResult()
;