Laravel：执行使用多个表的位置

Question

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

Answer 1

try this

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

Hope it helps. (Not tested though)