how to group by the relation entity in laravel

Question

i have 2 models order and city and each order has one city 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 laravels withcount 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

Check here https://stackoverflow.com/questions/18533080/laravel-eloquent-groupby-and-also-return-count-of-each-group#18533772 and see if it solves your issue — Sylvester Oguikpu, Jul 11 '21 at 09:28

Takesha Kaluarachchi · Answer 1 · 2021-07-12T03:34:29.650

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:

lagbox · Accepted Answer · 2021-07-11T12:24:40.900

1

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.

edited Jul 11 '21 at 12:24

answered Jul 11 '21 at 12:19

lagbox

48,571
8
72
83

score 0 · Answer 3 · answered Jul 11 '21 at 09:40

0

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();

answered Jul 11 '21 at 09:40

Ezatullah Tabish

33
1
6

how to group by the relation entity in laravel

3 Answers3