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.