php - how to group by the relation entity in laravel
Get the solution ↓↓↓i have 2 modelsorder
andcity
and eachorder
has onecity
now what i want to do is to show the user how many orders per each city he has . and the result for example would be some thing like below :
Illuminate\Support\Collection {#2220 в–ј
#items: array:17 [в–ј
"new-york" => 80
"city-number2" => 20
"city-number3" => 10
"city-number4" => 5
]
}
what i have tried so far is to work with the laravelswithcount
on order like below :
$orders = Order::withCount('orders')->where('shop_id', $shop->id)->get();
but no luck with it because it returns the count of all cities not each city individually . thanks in advance
Answer
Solution:
You can do this query from the other direction easily:
$cities = City::withCount(['orders' => fn ($q) => $q->where('shop_id', $shop->id)])->get();
foreach ($cities as $city) {
echo "{$city->name}: {$city->orders_count}";
}
If you need to restrict which cities are listed you can constrain that further if needed.
Answer
Solution:
this problem is based on one to many relationships in DBMS, in laravel there is an inbuild option that comes with eloqut (one city has many orders and one order belongs to one city) first of all, you need to implement the cities model and order model inside cities model define a function like this
public function orders()
{
return $this->belongsTo(Order::class, 'city_id');
}
and in order model define a function like this
public function city()
{
return $this->hasMany(City::class, 'order_id');
}
then in the controller to find how many orders per each city use the query like this with laravel eloquent
$cities= City::withCount(['orders' => fn ($q) => $q->where('shop_id', $shop->id)])->get();
foreach ($cities as $city) {
echo $city->orders_count;
}
The withCount method which will place a {relation}_count attribute on the resulting models:
Answer
Solution:
the controller to find how many orders per each city use the query like this with laravel eloquent.
$orders = Order::with('cities')
->select('city_id',\DB::raw('count(*) as city'))
->groupby('city_id')
->get();
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: the browser (or proxy) sent a request that this server could not understand.
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.