Symfony2 Doctrine SQL object mapping on subselect

Question

I'm trying to use this query: MySQL SELECT DISTINCT by highest value

    SELECT 
    p.*
FROM    
      product p
    INNER JOIN
        ( SELECT 
              magazine, MAX(onSale) AS latest
          FROM
              product
          GROUP BY 
              magazine
        ) AS groupedp
      ON  groupedp.magazine = p.magazine
      AND groupedp.latest = p.onSale ;

Within Symfony2 and DQL.

I have:

$query = $em->createQuery("SELECT p FROM MyBundle:Product p WHERE p.type = 'magazine' AND  p.maglink IS NOT NULL OR (p.type = 'magazine' AND  p.diglink IS NOT NULL) GROUP BY p.magazine ORDER BY p.onSale DESC");

Which works fine with and outputs objects but without the correct MAX(onSale)

Doing:

 $query = $em->createQuery("SELECT p , MAX(p.onSale) FROM MyBundle:Product p WHERE p.type = 'magazine' AND  p.maglink IS NOT NULL OR (p.type = 'magazine' AND  p.diglink IS NOT NULL) GROUP BY p.magazine ORDER BY p.onSale DESC");

Results in non-objects being returned.

This:

$query = $em->createQuery("SELECT
            p.*
            FROM
            MyBundle:Product p
            INNER JOIN
            ( SELECT
                    p.magazine, MAX(onSale) AS p.latest
                    FROM
                    MyBundle:Product p
                    GROUP BY
                    p.magazine
            ) AS groupedp
            ON  groupedp.magazine = p.magazine
            AND groupedp.latest = p.onSale ;");

Throws this error: [Semantical Error] line 0, col 127 near 'SELECT ': Error: Identification Variable ( used in join path expression but was not defined before.

I assume due to this Symfony2 Doctrine query

How can I maintain my mapping while still being able to sort each item by onsale?

score 1 · Answer 1 · answered Jul 25 '12 at 16:56

1

Have you considered splitting this into two queries:

First: Run your query directly against the database connection, and return the IDs of the relevant rows, in the order you want. This separates your complex query from DQL.

Second: Query the rows through Doctrine to get the full entities you want, based on the IDs/orders of the previous query.

answered Jul 25 '12 at 16:56

Adrian Schneider

7,239
1
19
13

While this would obviously work (and I appreciate your answer) , it seems strange that DQL would require 2 hits on the DB for such a seemingly simple query. Right now I'm just using raw SQL rather than DQL for this. – BobFlemming Jul 27 '12 at 11:34

score 1 · Answer 2 · answered Jul 31 '12 at 17:22

This is a shot in the dark, but it seems fairly obvious. Your aliasing two tables to the same alias. Thus when your using p. in the join it thinks your working from the original definition of p from before the join, then you alias the join as p. Change the alias of the join (and the references to that table) so each alias is unique.

Symfony2 Doctrine SQL object mapping on subselect

2 Answers2