Tranfrom SQL to query in laravel-CodePudding

I want to join 3 table with accounts, mailboxes, mailbox_edi_profile. I join accounts to mailboxes with accounts.id and mailboxes.account_id and join mailboxes to mailbox_edi_profile whit mailboxes.id and mailbox_edi_profile.mailbox_id I have query in SQL like this.

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;

I try to this.

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

I get this error , but not work

SQLSTATE[42000]: Syntax error or access violation: 1055 'laravel.mailboxes.account_id' isn't in GROUP BY (SQL: select count(*) as aggregate from (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) mailboxes on `accounts`.`id` = `mailboxes`.`account_id` group by `accounts`.`id`, `mailboxes`.`account_id`) as `aggregate_table`)

CodePudding user response：

It may be related to mysql mode ONLY_FULL_GROUP_BY, which obliges you to have all selected columns in group by. To remove this mode you can access file config/database.php and comment ONLY_FULL_GROUP_BY in mysql modes :

 'mysql' => [
        'driver' => 'mysql',
        'url' => env('DATABASE_URL'),
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'prefix_indexes' => true,
        'strict' => true,
        'modes' => [
            //'ONLY_FULL_GROUP_BY', HERE
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_AUTO_CREATE_USER',
            'NO_ENGINE_SUBSTITUTION'
        ],
        'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',
        'options' => extension_loaded('pdo_mysql') ? array_filter([
            PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
        ]) : [],
    ],