Symfony教义orderby和group by(不同) [英] Symfony doctrine orderby and group by (distinct)

问题描述

我正在尝试获取最新的预订课程(唯一).我已经使用Doctrine querybuilder尝试了以下方法:

I am trying to get the latest booked courses (unique). I have tried the following with the Doctrine querybuilder:

$repository = $this->getDoctrine()->getRepository('AppBundle:Booking');

$query = $repository->createQueryBuilder('b')
->select('(b.course) AS course')
->orderBy('b.id', 'DESC')
->groupBy('b.course')
->setMaxResults(5)
->getQuery();

在没有订单的情况下它可以工作，但是它将遍历预订.我需要最新预订的课程.通过此查询，我收到此错误:

Without the orderby it works but it will traverse the bookings ascending. I need the latest booked courses. With this query I am getting this error:

SQLSTATE [42000]:语法错误或访问冲突:1055表达式#1的ORDER BY子句不在GROUP BY子句中，包含非汇总列...不是功能上取决于GROUP BY子句中的列；这是与sql_mode = only_full_group_by不兼容500 Internal Server错误-DriverException

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ... which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 500 Internal Server Error - DriverException

我还尝试了以下方法:

$em = $this->getDoctrine()->getManager();
$query = $em->createQuery(
   'SELECT DISTINCT c.id
    FROM AppBundle:Booking b
    JOIN b.course c
    ORDER BY b.id DESC'
);

SQLSTATE [HY000]:常规错误:3065 ORDER BY子句的表达式#1不在SELECT列表中，引用列...不在SELECT中列表;这与DISTINCT不兼容

SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column ... which is not in SELECT list; this is incompatible with DISTINCT

现在，我已经搜索了解决方案，并且基本上发现了许多禁用ONLY_FULL_GROUP_BY的建议.禁用ONLY_FULL_GROUP_BY

Now I have searched for solutions and basically found a lot of suggestions to disable ONLY_FULL_GROUP_BY. Disable ONLY_FULL_GROUP_BY

一些评论指出，这样做不符合sql标准是不明智的.那怎么可能以一种可行的方式呢?

Some comments state there that it is not wise to do that in order to comply to sql standards. Then how is this possible in a way that does work?

推荐答案

您可以尝试以下方式:

$query = $repository->createQueryBuilder('b')
->select('(b.course) AS course')
->leftJoin('AppBundle:Booking', 'b2', 'WITH', 'b.course = b2.course AND b.id < b2.id)
->where('b2.id IS NULL')
->orderBy('b.id', 'DESC')
->setMaxResults(5)
->getQuery();

您将只获得每门课程ID最高的预订，因此无需按课程分组.

You'll get only the bookings with greatest id for every course, so no need for grouping by course.

这篇关于Symfony教义orderby和group by(不同)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！