Laravel raw select error; works in mysql

Question

This following sql statement works in mysql query, but gives error SQLSTATE[42000]: Syntax error or access violation: 1055 'dashboard.m.id' isn't in GROUP BY (SQL: SELECT m.id, .... when trying to execute the following in Laravel:

$this->builder = DB::select
 ("SELECT m.id, m.member_num, m.first_name, m.last_name, COUNT(s.member_id) AS members 
FROM sessions AS s 
LEFT JOIN members AS m ON s.member_id = m.id 
WHERE s.created_at >= :dateFrom 
AND s.created_at < :dateTo 
GROUP BY s.member_id",            
['dateFrom' => "date('2017-11-01')", 
'dateTo' => "date('2017-12-01')"]);

Can anyone see the issue considering it works correctly in mysql.

I'm simply trying to get the count of sessions and display the member details that are linked as member_id in sessions

score 2 · Answer 1 · answered Dec 14 '17 at 06:03

2

The error you are seeing in Laravel has to do with that you are selecting non aggregate columns which do not appear in the GROUP BY clause. The reason it worked in MySQL is apparently because that MySQL is running in a lax mode with ONLY_FULL_GROUP_BY turned off, which tolerates such non standard syntax. I suspect you intended to use a query along these lines:

SELECT
    m.id, m.member_num, m.first_name, m.last_name, s.member_count
FROM members m
LEFT JOIN
(
    SELECT member_id, COUNT(*) AS member_count
    FROM sessions
    WHERE s.created_at >= :dateFrom AND
          s.created_at < :dateTo
    GROUP BY member_id
) s
    ON m.id = s.member_id

Since you are aggregating over the members in the sessions table, and counting only a field from that table, I propose to do the aggregation in a separate subquery. Then, join this to members to retrieve the full member records.

answered Dec 14 '17 at 06:03

Tim Biegeleisen

502,043
27
286
360

thanks for such fast answer - I've dropped your query in and while it now works, the member_count is all null ? – yoyoma Dec 14 '17 at 06:08
Hmmm...can you try running just the subquery `SELECT member_id, COUNT(*) ...` ? What does that return? If all `member_count` are coming back `NULL` even there, then it means your data has a problem. – Tim Biegeleisen Dec 14 '17 at 06:09
Another thought: How do you plan to _order_ this result set? It could be that the few records which came back first happen to have no counts. Perhaps you should also have an `ORDER BY` clause. – Tim Biegeleisen Dec 14 '17 at 06:11
thank you I don't believe there is a data issue, but I will go through it thoroughly and come back soon – yoyoma Dec 14 '17 at 06:14
just so u know i'm not crazy - left side is Postman response, right side is mysql query (expecting count to be 3 as shown for this particular member) https://ibb.co/dcqfAR – yoyoma Dec 14 '17 at 06:31
This screen shot doesn't help the situation, because apparently you've ignored my above requests for more information. So again, what happens when you run the subquery in isolation in MySQL? Do you see members and their counts? If you _do_ see this, then error is being introduced by the join, which means the join isn't happening properly. This would likely be due to the data more than anything else. – Tim Biegeleisen Dec 14 '17 at 06:34
ok sorry, yes I tried the subquery and it worked as I expected, thats why I posted the screenshot. I will have another play with the join and come back – yoyoma Dec 14 '17 at 06:38
@yoyoma If you're getting `NULL` values then it means the join failed, and the members from the `members` table could not be matched to anything in the `sessions` table. – Tim Biegeleisen Dec 14 '17 at 06:42

linktoahref · Answer 2 · 2017-12-14T06:49:44.933

0

To overcome ONLY_FULL_GROUP_BY issue, you could use ANY_VALUE() to refer to the nonaggregated column.

DB::table('sessions')
    ->join('members', 'sessions.member_id', '=', 'members.id')
    ->select([
        'members.member_num',
        'members.first_name'
        'members.last_name',
        DB::raw('COUNT(sessions.member_id) AS members'),
        DB::raw('ANY_VALUE(members.id) AS id'),
    ])
    ->where([
        ['sessions.created_at', '>=', date('2017-11-01')],
        ['sessions.created_at', '<', date('2017-12-01')]
    ])
    ->groupBy('sessions.member_id')
    ->get();

edited Dec 14 '17 at 06:49

answered Dec 14 '17 at 06:21

linktoahref

7,812
3
29
51

thanks for responding, gave this a quick try and got error `ANY_VALUE does not exist` – yoyoma Dec 14 '17 at 06:28

Laravel raw select error; works in mysql

2 Answers2