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

doctrinedqlPHP

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?

Best Answer

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.

Related Topic