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