将多表关联SQL转换为Laravel查询时遇GROUP BY错误求助

问题场景 #

关联accounts、mailboxes、mailbox_edi_profile三张表（关联条件：accounts.id = mailboxes.account_id、mailboxes.id = mailbox_edi_profile.mailbox_id），将原始SQL转换为Laravel查询构造器代码后，触发报错：

SQLSTATE[42000]: Syntax error or access violation: 1055 'laravel.mailboxes.account_id' isn't in GROUP BY

原始SQL #

SELECT
    accounts.id,
    NAME,
    GROUP_CONCAT(
        mailbox_with_profile_nos SEPARATOR "<br>"
    ) AS mailbox_with_profile_nos
FROM
    accounts
LEFT JOIN(
    SELECT
        mailboxes.id,
        account_id,
        IF(
            GROUP_CONCAT(
                mailbox_edi_profile.profile_no SEPARATOR "<br>"
            ) IS NULL,
            username,
            CONCAT(
                username,
                " (",
                GROUP_CONCAT(mailbox_edi_profile.profile_no),
                ")"
            )
        ) AS mailbox_with_profile_nos
    FROM
        mailboxes
    LEFT JOIN mailbox_edi_profile ON mailbox_edi_profile.mailbox_id = mailboxes.id
    GROUP BY
        mailboxes.id
) AS mailboxes
ON
    accounts.id = mailboxes.account_id
GROUP BY
    accounts.id;

尝试的Laravel代码 #

$list = DB::table('accounts')
            ->select('accounts.id', 'NAME', DB::raw('GROUP_CONCAT(mailbox_with_profile_nos SEPARATOR "<br>") as mailbox_with_profile_nos'))
            ->leftJoin(DB::raw('(SELECT mailboxes.id, account_id, IF(GROUP_CONCAT(mailbox_edi_profile.profile_no SEPARATOR "<br>") IS NULL, username, CONCAT(username," (",GROUP_CONCAT(mailbox_edi_profile.profile_no),")")) as mailbox_with_profile_nos FROM mailboxes LEFT JOIN mailbox_edi_profile ON mailbox_edi_profile.mailbox_id = mailboxes.id GROUP BY mailboxes.id) mailboxes'), 'accounts.id', '=', 'mailboxes.account_id')
            ->groupBy('accounts.id', 'mailboxes.account_id'); 

错误原因 #

1. 外部查询的groupBy中额外添加了mailboxes.account_id，但原始SQL仅按accounts.id分组，且mailboxes.account_id与accounts.id是关联字段，重复分组无意义，反而触发MySQL的ONLY_FULL_GROUP_BY模式校验。
2. 子查询中未将非聚合列account_id、username加入GROUP BY，同样违反ONLY_FULL_GROUP_BY规则，可能潜在触发报错。

修正后的Laravel代码 #

$list = DB::table('accounts')
    ->select('accounts.id', 'accounts.NAME', DB::raw('GROUP_CONCAT(mailbox_with_profile_nos SEPARATOR "<br>") as mailbox_with_profile_nos'))
    ->leftJoin(
        DB::raw('(
            SELECT 
                mailboxes.id, 
                mailboxes.account_id, 
                mailboxes.username,
                IF(
                    GROUP_CONCAT(mailbox_edi_profile.profile_no SEPARATOR "<br>") IS NULL,
                    mailboxes.username,
                    CONCAT(mailboxes.username," (",GROUP_CONCAT(mailbox_edi_profile.profile_no),")")
                ) as mailbox_with_profile_nos 
            FROM mailboxes 
            LEFT JOIN mailbox_edi_profile ON mailbox_edi_profile.mailbox_id = mailboxes.id 
            GROUP BY mailboxes.id, mailboxes.account_id, mailboxes.username
        ) mailboxes'), 
        'accounts.id', '=', 'mailboxes.account_id'
    )
    ->groupBy('accounts.id', 'accounts.NAME');

修正说明 #

• 子查询中添加mailboxes.account_id、mailboxes.username到GROUP BY，符合ONLY_FULL_GROUP_BY对非聚合列的要求。
• 外部查询仅保留accounts.id和accounts.NAME到GROUP BY（因为SELECT中包含NAME，必须加入分组），移除多余的mailboxes.account_id，解决当前报错。
• 给NAME字段加上表前缀accounts.，避免字段歧义。

内容的提问来源于stack exchange，提问作者Tack Suttipong Kamijou