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.

Levorotatory

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? – Levorotatory

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. – Actualize

Answer 1

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

'strict' => true,

To

'strict' => false,

Moulmein

Answer 2

Where this occurs add this top of the function

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

Windpollinated

This didnt work for me. – Nataline

@Nataline why what's the issue – Windpollinated

Same result as when i set 'strict' to true – Nataline

@Nataline show me your code. I'm 100% sure this works. Cz I've used 1000 times. – Windpollinated

Answer 3

run this query after setting connection in database file

SET sql_mode = false; 

Mountfort