php - 如何在 LeftJoin querybuilder 中进行选择查询

Question

如何将以下查询转换为 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

这是我的代码:

    $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);

我报错了:

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

我已经知道我可以在主查询中设置子查询，虽然在这种情况下我不想在 Where 表达式中使用子查询。对在学说的 LeftJoin 中使用 select 有什么建议吗？

已编辑:我尝试在我的查询中使用 DQL:

    $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                                                                                                                                                                                                 

                    ');

但是我又报错了:

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

是否可以在 Doctrine 中使用 select in left join？

EDITED 2 : 我读了 similar question我决定用另一种方式写:

    $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);

再次出现错误:

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

Answer 1

你想做的事是不可能的:

https://github.com/doctrine/doctrine2/issues/3542 (2013 年 11 月，但 doctrine 概念没有改变，因为 doctrinebot 于 2015 年 12 月 7 日关闭了它)

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)

您可以找到解决方法，例如原始 sql，或重新考虑您的查询。