Wednesday, March 29, 2017

Laravel: Perform where using multiple tables

I'm using the Eloquent ORM that Laravel provides and I want to perform a where over two tables. I have 3 models

  • House
  • Occupants
  • Job

The occupants table has a column called pocket_money and the jobs table has a column called income. The occupant can have multiple jobs so I want to select all of the occupants where the sum of their income + pocket_money is greater than X. So the raw query would be

select occupants.* 
from occupants o, jobs j 
where o.pocket_money + sum(j.income) > X
and o.occupant_id = j.occupant_id

This is what I have at the moment but it doesn't work as the occupants table isn't available when it executes the query.

$occupants = House::with(["occupants", "occupants.jobs" => function($query) {
    $query->where('occupants.pocket_money + sum(jobs.income)', '>', 1000);
}])->find($house_id);



via Pattle

Advertisement