mysql - Laravel:查询直接在 MySQL 上运行，但不能在 DB 上运行

Question

这个问题已经有答案了:

MySQL : isn't in GROUP BY (5 个回答)

已关闭 4 年前。

我有以下查询:

SELECT cn.id, cn.title, cn.type, cn.status FROM (
    SELECT v.concert_id as concert_id, SUM(v.position_votes + v.support_votes) as votes
    FROM (
        SELECT q.concert_id as concert_id, q.position_id as position_id, q.position_votes as position_votes, SUM(q.votes_up + q.votes_down) as support_votes
        FROM ( 
            SELECT qcs.id AS concert_id, p.id AS position_id, p.votes AS position_votes, IF(s.votes_in_favor <=> null, 0, s.votes_in_favor) AS votes_up, IF(s.votes_not_in_favor <=> null, 0, s.votes_not_in_favor) AS votes_down     
            FROM (
                SELECT concert_id AS id 
                FROM positions p 
                WHERE p.content LIKE '%%' 
                GROUP BY concert_id
            ) 
            AS qcs 
            JOIN positions p
            ON p.concert_id = qcs.id
            LEFT JOIN supports s
            ON s.position_id = p.id
        ) AS q
        GROUP BY q.position_id
    ) as v
    GROUP BY v.concert_id
) as r
JOIN concerts cn on cn.id = r.concert_id
ORDER BY r.votes DESC, cn.created_at DESC

当我直接在 MySQL 中进行查询时，我得到了想要的结果。但是，当我使用数据库进行查询时，例如:

$query = "...alll_the_previous_query";
$result = DB::select(DB::raw($query));

我收到以下错误:

local.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1055 'q.concert_id' isn't in GROUP BY ...the rest of the query

我知道避免这种情况的一种方法是更改​​ Laravel 中的数据库配置并更改 strict至false 。但这不是一个选择。

当我将查询传递给 Laravel 时，它有什么问题？

Answer 1

您可以将 q.concert_id 添加到分组依据中。而不是:

GROUP BY q.position_id

用途:

GROUP BY q.concert_id, q.position_id