Laravel Eloquent Multiple Where with count

Question

Following is the SQL query that I need to perform on laravel eloquent. SQL returns expected output.

SELECT orders.id, orders.`status`, order_type, COUNT(order_type) as count FROM orders WHERE orders.`status` = 0 && order_type = 1 ORDER BY orders.id DESC

what I have tried on laravel is below

 $receved = Order::select('status', 'order_type')->where('status',0); $relase = $receved->where('order_type', 1)->get(); $bulk = $receved->where('order_type', 2)->get(); $distiribute = $receved->where('order_type', 3)->get(); return response()->json([ 'success' => true, 'message' => 'Statement Updated', 'orderStatment' => [ 'relaseCount' => count($relase), 'bulkCount' => count($bulk), 'distiributeCount' => count($distiribute) ], ], 200);

I seeking recommendation/suggestion to operate this in a correct way

The output I getting on laravel is

 'orderStatment' => [ 'relaseCount' => 14, 'bulkCount' => 0, 'distiributeCount' => 0 ],

the output of expectation and SQL produce is

 'orderStatment' => [ 'relaseCount' => 14, 'bulkCount' => 5, 'distiributeCount' => 4 ],

There are 8 Different type of status and 3 different types of order_type available on Table I want to get each order_type count of every status

@BrianThompson i updated the question and with more information — Mohamed Raza
– Mohamed Raza, Commented Jun 24, 2021 at 16:41

aynber · Accepted Answer · 2021-06-24 16:53:20Z

1

You might have better luck doing it all in one query, then getting the data back out.

$receved = Order::select('status', 'order_type', DB::raw('COUNT(id) as order_count'))->where('status',0) ->groupBy('order_type') ->get();

This will give you a collection of all of the order types and their counts in one query. After that, you can get the data back out.

$bulk = $relase = $distiribute = 0; foreach($receved as $rec) { if($rec->order_type == 1) $relase = $rec->order_count; elseif($rec->order_type == 2) $bulk = $rec->order_count; elseif($rec->order_type == 3) $distiribute = $rec->order_count; }

answered Jun 24, 2021 at 16:53

aynber

23.2k9 gold badges57 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mohamed Raza Over a year ago

which returns an error SQLSTATE[42000]: Syntax error or access violation: 1055

aynber Over a year ago

Can you provide the full error message? I might have mistyped something, but I can't see it

Mohamed Raza Over a year ago

message: "SQLSTATE[42000]: Syntax error or access violation: 1055 'vgc.orders.status' isn't in GROUP BY (SQL: select status, order_type, COUNT(id) as order_count from orders` where status = 0 group by order_type)"

aynber Over a year ago

Ah ha, that darn GROUP BY message. Add DB::statement("set @@sql_mode='ONLY_FULL_GROUP_BY'"); before your query. Or in config/database.php, change strict => false under mysql

aynber Over a year ago

Information at stackoverflow.com/questions/41571271/… and stackoverflow.com/questions/25800411/mysql-isnt-in-group-by

|

Brian Thompson · Accepted Answer · 2021-06-24 16:49:43Z

The problem you're facing is due to the fact that all of the following statements are manipulating the same query builder object:

$receved = Order::select('status', 'order_type')->where('status',0); $relase = $receved->where('order_type', 1)->get(); $bulk = $receved->where('order_type', 2)->get(); $distiribute = $receved->where('order_type', 3)->get();

So the actual queries created will be something like this:

All start with: select status, order_type from orders where status = 0 and

order_type = 1;
order_type = 1 and order_type = 2;
order_type = 1 and order_type = 2 and order_type = 3;

This is why the last two queries return 0. It's expected once you see the resulting query.

You can verify this by logging the query (see this answer for details, or the docs here).

$receved is actually getting the where clauses attached to it each time. So you're not just starting with the original statement, but building onto it each time you call where.

Collectives™ on Stack Overflow

Laravel Eloquent Multiple Where with count

2 Answers 2

6 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Linked

Related