Laravel: Syntax error or access violation: 1055 solution

Question

Why does this query result in an SQLSTATE error?

SQLSTATE[42000]: Syntax error or access violation: 1055 'database.events.date' isn't in GROUP BY

select `events`.`listing_id`, `events`.`date`, `events`.`listing_name`, count(*) as number_sold 
from `events` 
where `events`.`date` >= "2020-07-14" 
group by `events`.`listing_id` 
order by `events`.`date` asc

The query executes without error when I run it manually on the server, and with the expected results.

What specifically is the syntax error and why?

Answer 1

Mostly taken from Solve Query Failures Regarding ONLY_FULL_GROUP_BY SQL Mode. A superb article.

Explanation

As of MySQL 5.7 they have made the syntax stricter to stop semantically incorrect queries from running. One particular new rule is called ONLY_FULL_GROUP_BY and it stops you from making errors when using the GROUP BY clause.

As easy example to understand is this:

Let's say we want to count the most popular pages on a website from this table:

+----+--------------------+---------+---------------------+
| id | page_url           | user_id | ts                  |
+----+--------------------+---------+---------------------+
|  1 | /index.html        |       1 | 2019-04-17 12:21:32 |
|  2 | /index.html        |       2 | 2019-04-17 12:21:35 |
|  3 | /news.php          |       1 | 2019-04-17 12:22:11 |
|  4 | /store_offers.php  |       3 | 2019-04-17 12:22:41 |
|  5 | /store_offers.html |       2 | 2019-04-17 12:23:04 |
|  6 | /faq.html          |       1 | 2019-04-17 12:23:22 |
|  7 | /index.html        |       3 | 2019-04-17 12:32:25 |
|  8 | /news.php          |       2 | 2019-04-17 12:32:38 |
+----+--------------------+---------+---------------------+

You might simply write:

SELECT page_url, user_id, COUNT(*) AS visits 
    -> FROM web_log 
    -> GROUP BY page_url ORDER BY COUNT(*) DESC;

And get the following results:

+-------------------+---------+--------+
| page_url          | user_id | visits |
+-------------------+---------+--------+
| /index.html       |       1 |      3 |
| /news.php         |       1 |      2 |
| /store_offers.php |       3 |      2 |
| /faq.html         |       1 |      1 |
+-------------------+---------+--------+

But what does the user_id column represent? If you think about it, its contents are completely random.

You need to either include it in the group_by clause, or exclude it from your select (or otherwise include it in an aggregate function like count, min, max, etc.)

So for me, I need to include the other non-aggregate columns in my select:

select `events`.`listing_id`, `events`.`date`, `events`.`listing_name`, count(*) as number_sold 
from `events` 
where `events`.`date` >= "2020-07-14"
group by `events`.`listing_id`, `events`.`date`, `events`.`listing_name`
order by `events`.`date` asc

In my situation, this gives the expected dataset.