Group by error on MySQL 5.7

[antonkomarev]

@cmgmyr Hello, Chris! I've found that latest MySQL version turned on ONLY_FULL_GROUP_BY flag by default. This is critical on latest MySQL installations.

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.messenger_participant.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in /www/test/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:77

Next Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.messenger_participant.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select from messenger_thread where exists (select from messenger_participant where messenger_participant.thread_id = messenger_thread.id and user_id in (2, 4) and messenger_participant.deleted_at is null group by thread_id having COUNT(thread_id)=2) and subject = 'test subject' and messenger_thread.deleted_at is null limit 1) in /www/test/vendor/laravel/framework/src/Illuminate/Database/Connection.php:770

More explanation why this could happen: http://stackoverflow.com/a/41184943

[antonkomarev]

It looks like problem is here:

public function scopeBetween($query, array $participants)
{
    $query->whereHas('participants', function ($query) use ($participants) {
        $query->whereIn('user_id', $participants)
            ->groupBy('thread_id')
            ->havingRaw('COUNT(thread_id)=' . count($participants));
    });
}

[cmgmyr]

Can you try using dev-master? This looks similar to the postgres fix that hasn't been tagged yet

[antonkomarev]

@cmgmyr It's working, thanks!