Query works on phpmyadmin, but not on Laravel

Asked

Viewed 74 times

-1

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!

mysql laravel phpmyadmin

  • This SQL is bad SQL should rethink how to write, and in relation to Eloquent, is not being translated equal to its pure SQL and so of the errors, is no problem in Eloquent is problem as it was done.

  • 1

    Thanks for the tip. I decided to change in Config > Database > mysql > Strict => true (changed to true), solved but you’re right, I have to improve this query. Stopped giving error and is working, I just do not know if this is gambiarra. Anyway, thank you!

1 answer

0


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!

Browser other questions tagged mysql laravel phpmyadmin

You are not signed in. Login or sign up in order to post.