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?

Klaraklarika

Hey Gnuffo1, Could you please accept @Ocramius's answer, I think there is no doubt this solved your issue. – Himyaritic

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.

Carioca

Is this still the best practice for this scenario or is there something for this now in doctrine 2.5? – Haver

Yes, this didn't change. – Carioca

Also I would like to know if this impacts performance (the 1=1 part)? Otherwise I would have to look into another solution for this, I have a lot of DQL queries that need to join like this. – Haver

No, decent SQL planners strip the comparison away before executing the query. – Carioca

Answer 2

I couldn't figure out how to get native queries working, so have resolved in a slightly hacky way:

$id = $em->getConnection()->fetchColumn("SELECT
    pages.id
    FROM
    pages
    INNER JOIN siteversion_page ON siteversion_page.page_id = pages.id
    INNER JOIN siteversions ON siteversion_page.siteversion_id = siteversions.id
    WHERE siteversions.id = 1
    AND pages.slug = 'index'");

$page = $em->find('Page', $id);

I don't like it because it results in more queries to the database (especially if I need to fetch an array of pages instead of one) but it works.

Edit: I've decided to just go with a class for the association. Now I can do this query:

SELECT p FROM Page p, SiteVersionPageLink l
WHERE l.page = p AND l.siteVersion = 5 AND p.slug = 'index'

Klaraklarika

You don't really need the association if this kind of traversing is rare. Yes, it simplifies things, but you get some performance drawbacks when hydrating your objects. – Carioca

Answer 3

I think you need to select the SiteVersion in your query too:

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

You will get an array of SiteVersion entities which you can loop through to get the Page entities.

Halation

This will fetch-join the site pages into the site versions, returning a list of SiteVersion objects, and not just the site pages. This is actually wrong given the expected resultset (as of the question). – Carioca

Answer 4

Try this (or something like it):

SELECT p FROM Page p WHERE EXISTS (SELECT v FROM SiteVersion v WHERE p MEMBER OF v.pages AND v.id = 5 AND p.slug = 'index')

I haven't tested this exactly, but I have gotten something similar to work. The use of EXISTS and MEMBER OF are buried in the DQL Select Examples section of the DQL chapter.

Phillisphilly

Answer 5

I've found a possible solution for this problem here.

According to that page, your query should look something like this:

SELECT p FROM SiteVersion v, Page p WHERE v.id = 5 AND p.slug='index' AND v.page = p;

Does it solve your problem?

Frankhouse

No. The problem is, it's a many-to-many, not one-to-many. It's v.pages, not v.page but even if I do: "SELECT p FROM SiteVersion v, Page p WHERE v.id = 5 AND p.slug='index' AND v.pages = p" that doesn't work either (Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.) – Klaraklarika

Then why not you specify the other side of the relationship in your schema definition? – Frankhouse

Because I want to be able to just drop this module in without affecting anything that's already there. – Klaraklarika

Is using native sql to select at least the page id-s is a viable option for you? Of course it would be much cleaner to use DQL... – Frankhouse