Vfrdtyky

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:

[DoctrineORMQueryQueryException]

[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?

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.

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.

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'

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.

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?