dimanche 22 décembre 2019

How to subtract from sum of relation column

I have a jobs table and an incomes table:

Jobs:

id, date etc, ..., subtotal

Incomes:

id, job_id, amount, etc....

I would like to get the balance owed on each job. I have tried the following:

select `jobs`.*, sum(amount) as paid, (subtotal-paid) as balance
from `jobs` 
inner join `incomes` on `jobs`.`id` = `incomes`.`job_id` 
group by `jobs`.`id` 

but I get the error:

Unknown column 'paid' in 'field list'

In Laravel:

 $q = Job::select(DB::raw('jobs.*, sum(amount) as paid, (subtotal-paid) as balance'))
->join('incomes', 'job_id', '=', 'incomes.job_id')
->groupBy('jobs.id')


via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire