CODEWITHSUNDEEP
laravel
seema patil
seema patil

Reputation: 61

laravel group by query : Syntax error or access violation: 1055

I am writing query in laravel 5.7 which gives following error

SQLSTATE[42000]: Syntax error or access violation: 1055 'demoecommerce.orders.id' isn't in GROUP BY (SQL: select table_users.name, orders.*, orders_details.orders_id from orders_details inner join orders on orders.id = orders_details.orders_id inner join products on orders_details.product_id = products.product_id inner join table_users on table_users.id = orders.user_id group by orders_id)

In mysql it run correctly.

 $orders = DB::table('orders_details')
                    ->join('orders','orders.id','=','orders_details.orders_id')
                    ->join('products','orders_details.product_id','=','products.product_id')
                    ->join('table_users','table_users.id','=','orders.user_id')
                    ->select('table_users.name','orders.*','orders_details.orders_id')
                    ->groupBy('orders_id')
                    ->get();

Upvotes: 1

Views: 2577

Answers (2)

Pramod Kumar
Pramod Kumar

Reputation: 21

step 1. go to the config folder and open database file and change ... 'strict' => true`, to false.
step 2. go to Providers folder and open Appserviceprovoder file and do it

A. use Illuminate\Support\Facades\DB;

B. inside public function boot(): void function write below line

  DB::statement("SET SQL_MODE= ''");

Upvotes: 2

Jaswinder Singh
Jaswinder Singh

Reputation: 731

If you have groupBy in your SQL query, you can only select columns that are part of groupby in SQL else you must have some aggregation(sum, count, average..) on the columns

You can add some more details, about what you need from this query, order count? or order details per user.

Also, you can try looking at GroupBy on collection. Laravel documentation GroupBy on Collection

Upvotes: 1

Related Questions