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.

Answer 1

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

'strict' => true,

To

'strict' => false,

Answer 2

Where this occurs add this top of the function

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

Answer 3

run this query after setting connection in database file

SET sql_mode = false;