Laravel Eloquent to join table and count related

This is more of a MySQL join+group+select trick which includes following steps.

  1. Join your relation table(use join if you want to exclude rows with RoomsCount=0, else use leftJoin)
  2. Use groupBy by primaryKey to avoid duplicates of the join.
  3. 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.