How to do a select query in LeftJoin querybuilder

Question

How can I convert below query to Doctrine:

SELECT    restaurants.restaurant_name , 
          restaurants.restaurant_id,
          j.LASTPRICE
FROM      restaurants 


LEFT JOIN 
          ( 
                    SELECT    f.food_id                                                       AS fid,
                              f.restaurants_restaurant_id                                     AS rid,                              
                              Max(f.food_last_price) AS LASTPRICE
                    FROM      foods                                                           AS f
                    LEFT JOIN restaurants                                                     AS r
                    ON        r.restaurant_id = f.restaurants_restaurant_id 
                    WHERE     f.food_last_price IS NOT NULL                     
                    GROUP BY  r.restaurant_id) j 
                    ON restaurants.restaurant_id = j.rid

Here is my code:

    $qb = $this->_em->createQueryBuilder();
    $qb2 = $this->_em->createQueryBuilder();

    $getMaxPercentage = $qb2
        ->select(
            'MAX (Food.foodLastPrice) AS LASTPRICE ',
            'Food.foodId AS fId',
            'Food.restaurantsRestaurantId AS rID'
        )
        ->from($this->entityClass,'Restaurant')
        ->innerJoin('Restaurant.foods','Food')
        ->where('Food.foodLastPrice IS NOT NULL')
        ->groupBy('Restaurant.restaurantId')
        ->getDQL();

    $restaurantList = $qb
        ->select('Restaurants.restaurantName, Restaurants.restaurantId , j.LASTPRICE')
        ->from($this->entityClass,'Restaurants')
        ->leftJoin($getMaxPercentage,'j','WITH','Restaurants.restaurantId = j.rID')
        ->getQuery()->execute();
    dd($restaurantList);

I give an error :

SELECT Restaurants.restaurantName,': Error: Class 'SELECT' is not defined.

I've already known I could set sub queries in main query, Although in this case I does not want to use sub query in Where expression. Any suggestion for using select in LeftJoin in doctrine?

EDITED : I've tried to use DQL in my query:

    $query  =  $this->_em->createQuery(
                    '
                        SELECT Restaurants.restaurantName , Restaurants.restaurantId
                        FROM App\\Restaurant AS Restaurants
                        LEFT JOIN (
                            SELECT f.foodId AS fid,
                                   f.restaurantsRestaurantId AS rid, 
                                   Max(f.foodLastPrice) AS LASTPRICE
                            FROM App\\Food AS f
                            LEFT JOIN App\\Restaurant AS r
                            WITH r.restaurantId = f.restaurantsRestaurantId

                            GROUP BY r.restaurantId) AS J 
                        ON Restaurants.restaurantId = j.rid                                                                                                                                                                                                 

                    ');

But I gave an another error :

[Semantical Error] Error: Class '(' is not defined.

Is it possible to use select in left join in Doctrine?

EDITED 2 : I read a similar question and I've decided to write in another way :

    $qb = $this->_em->createQueryBuilder();
    $qb2 = $this->_em->createQueryBuilder();

    $subSelect = $qb2
        ->select(
            array(
                'Food.foodLastPrice AS LASTPRICE ',
                'Food.foodId AS fId',
                'Food.restaurantsRestaurantId AS rId')
        )
        ->from($this->entityClass,'Restaurant')
        ->innerJoin('Restaurant.foods','Food')
        ->where('Food.foodLastPrice IS NOT NULL')
        ->groupBy('Restaurant.restaurantId')
        ->getQuery()->getSQL();

    $restaurantList =
        $qb->select(
            'Restaurant1'
        )
            ->from($this->entityClass, 'Restaurant1')
            ->leftJoin('Restaurant1',sprintf('(%s)',$subSelect),'internalQuery','Restaurant1.restaurantId = internalQuery.rId')
            ->getQuery()->getSQL();
    dd($restaurantList);

Again, I got an error:

near 'Restaurant1 (SELECT': Error: Class 'Restaurant1' is not defined.

for complicated queries, I'm using Doctrine Query Language http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html — bxN5, Feb 22 '17 at 08:43
it is impossible to have subquery in join. if you look at this class method, it expects class name, not another query. it is possible to convert dql to sql and then execute this query. but this would encourage so much bad practices i even wont try to explain it. your best try would be get data without subquery. yes, i can see that group by, but you have to think about your business case and try to solve it differently — omxv, Feb 22 '17 at 08:56
@Roman I've tried to use DQL. may you check my question again? — Farzan Najipour, Feb 22 '17 at 11:13
I think doctrine left join doesn't work in the same way it works in SQL. And did you try this http://stackoverflow.com/questions/34768821/join-subquery-with-doctrine-2-dbal ? — Nico, Feb 22 '17 at 11:42
I'm not sure but your model entity in the query should look like PathtoBundle:Entityname — bxN5, Feb 22 '17 at 11:53
I think doctrine is waiting for a mapped entity in your From clause but you give something not mapped — Nico, Feb 22 '17 at 12:10
I wonder if it's possible to subquery in from clause, in particular to subquery not an Entity mapped, but a scalar data (foodLastPrice, foodId, restaurantsRestaurantId). Doctrine is able to understand entity annotation and doctrine relations, and then is waiting for an entity with doctrine annotation in leftJoin, that is why 'Error class'. for example http://stackoverflow.com/questions/9831985/selecting-from-subquery-in-dql — Nico, Feb 23 '17 at 10:31

Nico · Answer 1 · 2017-02-23T11:55:46.853

What you're trying to do is impossible :

https://github.com/doctrine/doctrine2/issues/3542 (november 2013, but doctrine concept didn't change since and doctrinebot closed this on 7 Dec 2015)

DQL is about querying objects. Supporting subselects in the FROM clause means that the DQL parser is not able to build the result set mapping anymore (as the fields returned by the subquery may not match the object anymore). This is why it cannot be supported (supporting it only for the case you run the query without the hydration is a no-go IMO as it would mean that the query parsing needs to be dependant of the execution mode).

In your case, the best solution is probably to run a SQL query instead (as you are getting a scalar, you don't need the ORM hydration anyway)

You can find workarounds like raw sql, or rethinking your query.

How to do a select query in LeftJoin querybuilder

1 Answers1