Laravel Eloquent to join table and count related

eloquent, join, laravel-4, sql

Solution

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 with `RoomsCount=0`, else use `leftJoin`)

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

Problem

How do I use join with Eloquent taking in consideration the following table structure: I have a properies table ``` --------------------- ID | Name --------------------- 1 | Property Name ``` than I have rooms ``` ---------------------- RoomID | Property ---------------------- A-212 | 1 ---------------------- F-1231 | 1 ``` here Property is the foreign key than I want to get all Properties and count how many rooms do they have each The query which retrives all looks like ``` class PropertiesRepository extends EloquentBaseRepository implements PropertiesInterface { use TaggableRepository; /** * Construct * @param Properties $properties */ public function __construct( Properties $properties ) { $this->model = $properties; } /** * Get all properties joining rooms * @return Properties */ public function getAll() { return $this->model->get(); } } ``` How do I extend this query to get the desired result?

Original source