Laravel Database Strict Mode

Question

I'm little wonder please help me out.

My query is :- Invoice::join('orders', 'orders.invoice_id', '=', 'invoices.id')->groupBy('invoices.id')->get();

Then I have got an error: Syntax error or access violation: 1055 'invoices.reference_number' isn't in GROUP BY

When I updated my query according to error then the query is:-

$invoices = Invoice::join('orders', 'orders.invoice_id', '=', 'invoices.id')->groupBy('invoices.id','invoices.reference_number','invoices.customer_address_id','invoices.country_id','invoices.paid_at','invoices.due_date','invoices.created_at','invoices.updated_at','invoices.deleted_at','orders.id','orders.currency_id','orders.user_id','orders.customer_id','orders.created_at','invoices.updated_at')->get();

Now query working but in that case, I can't use select(), It returns columns who is in the groupBy()

So I did google then found if I do database strict mode off then it will work and then I did and it's working good, But I don't want strict mode turned off.

Also, i did the study about database modes https://dev.mysql.com/doc/refman/5.5/en/faqs-sql-modes.html https://dev.mysql.com/doc/refman/5.5/en/sql-mode.html

Then I found query:- SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode;

But the result is blank

If I do in the database - select * from invoices inner join orders on orders.invoice_id = invoices.id where invoices.deleted_at is null group by invoices.id

Then it's working fine means no mode validations trough mySQL it has configured side of Laravel

Also, i haven't found related database modes in laravel documentation

So help me out if I did wrong something.

And i also want to know who's database strict mode validation is enabled in laravel because I have wonder what strict validation giving me an error.

I don't want to ignore or disable or set blank or change my database config. Please give me more information about strict mode. what validation rules are enabled by Laravel? What mySQL rule I haven't followed? — Dheeraj, Mar 12 '18 at 10:29
I'm also facing this problem, and the weird part is that it is working fine in my local computer, but error in my staging server. Executing the query directly via heidiSQL in my staging server is also fine. — Christhofer Natalius, Oct 17 '19 at 13:34

score 9 · Answer 1 · answered Mar 12 '18 at 07:40

9

Change this line in mysql array of config/database.php

'strict' => true,

To

'strict' => false,

answered Mar 12 '18 at 07:40

Sohel0415

9,523
21
30

score 5 · Answer 2 · answered Mar 12 '18 at 07:43

5

Where this occurs add this top of the function

public function index() {
    DB::statement("SET sql_mode = '' ");
    # rest of your Joins
}

answered Mar 12 '18 at 07:43

Abdulla Nilam

36,589
17
64
85

This didnt work for me. – Macdonald Jan 12 '22 at 13:10
@Macdonald why what's the issue – Abdulla Nilam Jan 12 '22 at 21:00
Same result as when i set 'strict' to true – Macdonald Jan 13 '22 at 11:07
@Macdonald show me your code. I'm 100% sure this works. Cz I've used 1000 times. – Abdulla Nilam Jan 13 '22 at 11:08

score 1 · Answer 3 · answered Apr 27 '22 at 10:17

1

run this query after setting connection in database file

SET sql_mode = false;

answered Apr 27 '22 at 10:17

Akram Elhaddad

194
3
7

Laravel Database Strict Mode

3 Answers3

Linked