Doctrine 2 DQL - how to select inverse side of unidirectional many-to-many query?

Question

I have two classes - Page and SiteVersion, which have a many to many relationship. Only SiteVersion is aware of the relationship (because the site is modular and I want to be able to take away and drop in the module that SiteVersion belongs to).

How would I therefore select pages based on criteria of SiteVersion?

For example, this doesn't work:

SELECT p FROM SiteVersion v JOIN v.pages p WHERE v.id = 5 AND p.slug='index' 

I get the error:

[Doctrine\ORM\Query\QueryException] [Semantical Error] line 0, col -1 near 'SELECT p FROM': Error: Cannot select entity through identification variables without choosing at least one root entity alias. 

Even though I can select "v" with this query.

I think I could possibly resolve this by introducing a class for the relationship (a PageToVersion class) but is there any way without doing that, or making it bidirectional?

Answer 1

There's two ways of handling this in Doctrine ORM. The most typical one is using an IN condition with a subquery:

SELECT     p FROM     SitePage p WHERE     p.id IN(         SELECT             p2.id         FROM             SiteVersion v         JOIN             v.pages p2         WHERE             v.id = :versionId             AND             p.slug = :slug     ) 

The other way is with an additional join with the arbitrary join functionality introduced in version 2.3 of the ORM:

SELECT     p FROM     SitePage p JOIN     SiteVersion v WITH     1 = 1 JOIN     v.pages p2 WHERE     p.id = p2.id     AND     v.id = :versionId     AND     p2.slug = :slug 

The 1 = 1 is just because of a current limitation of the parser.

Please note that the limitation that causes the semantical error is because the hydration process starts from the root of the selected entities. Without a root in place, the hydrator has no reference on how to collapse fetch-joined or joined results.