Helping out at the laracasts forum, I often see people running into issues with using groupBy() in queries. If you are running into errors like this

1
SQLSTATE[42000]:  Syntax error 
or
 access violation:
2
 
1055
 Expression  #
1
 of 
SELECT
 list 
is
 
not
 
in
 
GROUP BY
 clause 
and
 contains nonaggregated colum

.. then you are most likely the target of this blog post. Be aware that this post isn’t meant to teach mysql GROUP BY, but rather give you a simple understanding of why the query is failing.

The error often comes from a query like this

1
$
posts 
=
 
Post
::
query
()
2
    
->
groupBy
(
'
category_id
'
)
3
    
->
get
();

Now the idea is most likely that you just want to group the posts into their respective categories with all columns. But that is now how it works on a database level.

To make it easier to understand, lets use a spreadsheet as an example instead

id
name
category_id
author_id
visits

1
Post 1
1
4
32

2
Post 2
1
8
12

3
Post 3
2
12
201

4
Post 4
2
4
3

The database will always return rows, so let us try and run the above query on the data set.
Open a spreadsheet a try to make two rows with all data. It is two rows as category_id can either be 1 or 2.
So which id should it pick? 1 or 2? and the name, slug or even owner?

id
name
category_id
author_id
visits

1 or 2
Post 1 or Post 2
1
4 or 8
32 or 12

3 or 4
Post 3 or Post 4
2
12 or 4
201 or 3

Are you starting to see the problem?

“So how can I fix it?”

The simplest way is to only get only the columns we are grouping by, in this case category_id

1












$
posts 
=
 
Post
::





query
()
2
    
->
select
(
'
category_id
'
)
3
    
->
groupBy
(
'
category_id
'
)
4
    
->
get
();

category_id

1

2

While this works, maybe it isn’t actually what we want. So now we need to plan out exactly what we want for each column. Perhaps we want to know what category id has the most visits. Here we can use what is called an aggregate function.

1
$
posts 
=
 
Post
::
query
()
2
    
->
select
(
'
category_id
'
,
 
\
DB
::
raw
(
'
MAX(visits) as max_visits
'
))
3
    
->
groupBy
(
'
category_id
'
)
4
    
->
get
();

We are using a raw query as there isn’t anyway to specify the MAX function in a select in laravel. Also we are aliasing it to max_visits to make it easy to reference in laravel.

category_id
max_visits

1
32

2
201

“But the query works in my database manager!”

You might indeed experience that the raw group by query actually works in your database manager. This is because laravel runs SQL queries in “strict mode”, while your database manager does not. It is possible to simply disable “strict mode” in laravel, by setting it to false in the database.php config file. While possible I cannot recommend doing so. It is better to spend the time learning how to write proper SQL queries, as the results given by turning “strict mode” off, can be unpredictable and lead to problems down the road.

“But I want all columns!”

Okay so you want to get all columns. Then the trick is to simply not use groupBy() on a database level. Instead you can use it with the returned collection instead. This will group the posts by the category it belongs to as expected (a nested collection)

1
$
posts 
=
 
Post
::
query
()
2
    
->
get
()
3
    
->
groupBy
(
'
category_id
'
);

This is result in a structure like (here shown as a php array to make it easier to read)

 1
[
 2
 
 3
'
1
'
 
=>
 
[
 4
    
[
'
id
'
 
=>
 
1
,
 
'
name
'
 
=>
 
'
Post 1
'
,
 
'
category_id
'
 
=>
 
1
,
 
'
author_id
'
 
=>
 
4
 
'
visits
'
 
=>
 
32
],
 5
    
[
'
id
'
 
=>
 
2
,
 
'
name
'
 
=>
 
'
Post 2
'
,
 
'
category_id
'
 
=>
 
1
,
 
'
author_id
'
 
=>
 
8
 
'
visits
'
 
=>
 
12
],
 6
],
 7
'
2
'
 
=>
 
[
 8
    
[
'
id
'
 
=>
 
3
,
 
'
name
'
 
=>
 
'
Post 3
'
,
 
'
category_id
'
 
=>
 
2
,
 
'
author_id
'
 
=>
 
12
 
'
visits
'
 
=>
 
201
],
 9
    
[
'
id
'
 
=>
 
4
,
 
'
name
'
 
=>
 
'
Post 4
'
,
 
'
category_id
'
 
=>
 
2
,
 
'
author_id
'
 
=>
 
4
 
'
visits
'
 
=>
 
0
],
10
],

Wrap up

So I hope this gave you some idea as to why your query is failing. If not, try asking at https://laracasts.com/discuss