Laravel: Syntax Error or Access Violation: 1055 Error

Laravel : Syntax error or access violation: 1055 Error

Short answer

In config\database.php --> "mysql" array

Set 'strict' => false to disable all.

.... or

You can leave 'strict' => true and add modes to "mysql" option in

'mysql' => [
       ...
       ....
       'strict' => true,
       'modes' => [
            //'ONLY_FULL_GROUP_BY', // Disable this to allow grouping by one column
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_AUTO_CREATE_USER',
            'NO_ENGINE_SUBSTITUTION'
        ],
 ]

---

Detailed answer

You may not need to disable all strict options ...
Kindly have a look on this answer about this issue.

Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

In SQL when a query contains a GROUP BY clause, all columns returned by the query (in the SELECT part) must be included in the GROUP BY clause itself or aggregated so that MySQL knows which value it has to return.

So the reason of the error message is because the query generated by Eloquent contains (select * from work_experiences.

Now to fix it in your case, I guess that you only have to add $query->select('employee_id'); right before the GROUP BY clause so that the new query will include (select employee_id from work_experiences.

Laravel groupBy is not working throwing error Syntax error or access violation: 1055

In config/database.php

Change 'strict' => true To 'strict' => false
and clear the cache

php artisan config:cache

OR In MySql settings change

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Laravel: Syntax error or access violation: 1055 solution

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.

SQLSTATE[42000]: Syntax error or access violation: 1055 Laravel 5.8 MySQL

When you have a GROUP BY in your query, you can only select columns that are in the GROUP BY, or is an aggregate function like SUM(), MIN(), MAX() etc.

If I understand your data correctly, you can extract the SUM() in a separate query, where you JOIN it into the main query.

DB::table('project_bills as pb')
    ->join('project_deliveryorder as pdo', 'pb.id_deliveryorder', '=', 'pdo.id')
    ->join(DB::raw('(SELECT id_bill, SUM(payment_amount) AS TOTAL
                      FROM project_bills_payment
                      GROUP BY id_bill) AS pbp'), 'pb.id', '=', 'pbp.id_bill')
    ->select('pb.*', 
             'pdo.totalcost', 
             'pdo.deliveryorder_number', 
             'pdo.deliveryorder_subject', 
             'pbp.TOTAL')
    ->orderBy('pb.bill_date', 'DESC')
    ->orderBy('pb.id', 'DESC')
    ->get();

Disabling strict mode or disabling ONLY_FULL_GROUP_BY is in my opinion not a good idea, and if you can avoid it by reworking your query, that's usually much better.

Laravel SQLSTATE[42000]: Syntax error or access violation: 1055

I changed strict = false inside config/database and its worked.

SQLSTATE[42000]: Syntax error or access violation: 1055 when using groupBy with hasManyThrough relation in Laravel

By reading the Error again ,I noted that Laravel use the foreign key as laravel_through_key in the query in some way.

The problem solved by adding laravel_through_key in the groupBy() method like this :

[...
 'employees_vacations'=>$user->empVacations()
       -> groupBy('laravel_through_key','user_num','type')
       -> selectRaw('sum(days) as days,sum(mins) as mins,user_num, type')->get()
 ]

But I still think there is a better solution so I will be happy to know your answers ^_^

---

Related Topics