I have a complex setup which includes the following tables.
Customers
Transactions
Jobs
Rounds
Job_Rounds
I want to output all the customers which are overdue. I've done that by using the following query:
$data = Auth::user()->clients()->leftjoin('transactions','clients.id','=','transactions.client_id')
->select(DB::raw('sum(gross) as value, email, first_name, last_name, mobile, clients.id, addressline1'))
->groupBy('id','first_name','last_name','email','mobile','addressline1', 'clients.id')
->havingRaw('SUM(gross) < 0')
->get();
That returns all the customers which are overdue which is great, however I now want to be able to filter the overdue customers by round.
My relationships are as follows:
Customers > Jobs.client_id
Customers > Transactions.client_id
Jobs > Rounds via Jobs_Rounds
I would pass to my controller the round_id of a given round and then try to filter the results based on the round_id. the only place I'm storing the round ID is in the jobs_rounds table and that table only contains job_id & round_id.