Query works on phpmyadmin, but not on Laravel

Question

Galley,

Laravel helps a lot, but when he wants to complicate too, it really does. I did a query and tested in phpmyadmin and it works normally, but insert the query in my project Laravel gives error. I already tried with Database using DB::table, DB::select etc., I tried with Eloquent. Every query result anyway I do, works on phpmyadmin, but it doesn’t work on Laravel. Somebody help me, please.

What I need is this:

$sql = "select *, 
        (select order_date from orders where order_number = order_id) as order_date, 
        (select client_id from orders where order_number = order_id) as client_id, 
        (select name from clients where id = client_id) as client_name, 
        (select sum(quant)) as saldo 
        from order_products 
        inner join orders on order_number = order_id 
        where product_id = 5 and complete_order = 0 
        group by order_id 
        order by order_id asc";

I’ve done it in the following ways:

$data = DB::select($sql);

and

$data = DB::table('order_products')
        ->select(['order_date' => Order::select('order_date')->whereColumn('order_number', 'order_id')])
        ->addSelect(['client_id' => Order::select('client_id')->whereColumn('order_number', 'order_id')])
        ->addSelect(['name' => Client::select('name')->whereColumn('id', 'client_id')])
        ->addSelect(DB::raw('sum(quant) as saldo'))
        ->where('product_id', $id)
        ->where('complete_order', 0)
        ->join('orders', 'order_number', 'order_id')
        ->groupBY('order_id')
        ->orderBy('order_id')
        ->paginate(20);

Among many other attempts, including with Eloquent.

When I debug SQL as a result of these attempts, they all work in phpmyadmin, but always give error in Laravel. The last mistake you’re making is this::

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'aptcontrol.orders.client_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select count(*) as aggregate from (select (select `order_date` from `orders` where `order_number` = `order_id`) as `order_date`, (select `client_id` from `orders` where `order_number` = `order_id`) as `client_id`, (select `name` from `clients` where `id` = `client_id`) as `name`, sum(quant) as saldo from `order_products` inner join `orders` on `order_number` = `order_id` where `product_id` = 6 and `complete_order` = 0 group by `order_id`) as `aggregate_table`)

I’m sorry if the explanation is too much, because I tried to do as detailed as possible.

Thanks for your help!

Answer 1

I managed to solve guys, thanks for the tips.

This mistake was happening because of GROUP BY, but I did not go into it, I confess.

I decided as follows:

In the config > Database folder, in the mysql array, within Connections, you have an item called "Strict". It is with the value 'true' I changed to 'false'. Just doing this, no more error and Laravel read my query and returned the result.

According to this link: https://stackoverflow.com/questions/42104412/what-is-the-use-of-strict-in-laravel-config-database/42104555 , Disabling Strict by setting it as 'false' does not make your application less secure if you follow the good practices that Laravel recommends.

I thank you all!