Laravel Eloquent to join table and count related
This is more of a MySQL join+group+select trick which includes following steps.
- Join your relation table(use
join
if you want to exclude rows withRoomsCount=0
, else useleftJoin
) - Use
groupBy
by primaryKey to avoid duplicates of the join. - Select
count
of joined table
$this->model->leftJoin('Rooms', 'Properties.ID', '=', 'Rooms.Property')
->selectRaw('Properties.*, count(Rooms.RoomID) as RoomsCount')
->groupBy('Properties.ID')
->get();
Define the relationship on your Property model class:
<?php
namespace App\Models;
class Property extends Model {
public function rooms() {
return $this->hasMany(Room::class);
}
}
$properties = Property::withCount(['rooms'])->get();
This will add a rooms_count to the result.