SUM calculation via relation

[Bartk314]

hours +---------+-------------+--------+------+---------+-------+ | hourID | Date | worked | sick | jobID | ect | +---------+-------------+--------+------+---------+-------+ | 1 | 2009-10-01 | 8 | 10 | 1 | NULL | | 2 | 2009-10-01 | 10 | 8 | 2 | NULL | | 3 | 2009-10-01 | 0.0 | 6 | 1 | NULL | | 4 | 2009-10-01 | 8 | 5 | 1 | NULL | | 5 | 2009-10-01 | 9 | 8 | 2 | NULL | | 6 | 2009-10-01 | 10 | 1 | 3 | NULL | +---------+-------------+--------+------+---------+-------+

jobs +---------+-------------+------------+-------+---------+-------+ | jobID | startDate | endDate | price | etc | ect | +---------+-------------+------------+-------+---------+-------+ | 1 | 2009-10-01 | 2020-10-01 | 20 | NULL | NULL | | 2 | 2009-10-01 | 2020-10-01 | 21 | NULL | NULL | | 3 | 2009-10-01 | 2020-10-01 | 22 | NULL | NULL | | 4 | 2009-10-01 | 2020-10-01 | 20 | NULL | NULL | | 5 | 2009-10-01 | 2020-10-01 | 20 | NULL | NULL | | 6 | 2009-10-01 | 2020-10-01 | 15 | NULL | NULL | +---------+-------------+------------+-------+---------+-------+

I have a very large database with lots of hour records. With SUM I manage to quickly retrieve the total of different hour types. like this: $hoursTotal = $this->db->hours()->where('date', $date)->sum('worked + sick');

I would like to get 'worked + sick' * price via the jobID relation and retrieve the sum of the calculation by sql.

If u use $hours->jobs()->via('jobID')->fetch() and do the calculation in php the query is too big and takes too long to run.

Can anyone help me?