Shortly, I want to get the action row of each opportunity with the last date and last time.
This is my action table.
This is what I want to get result.
I wrote sql for this algorithm in the below.
Select opportunities.id,
opportunity_actions.id,
opportunity_actions.hour,
opportunity_actions.date
from opportunities
left join opportunity_actions ON opportunity_actions.opportunity_id = opportunities.id
WHERE (
date = (
SELECT MAX(date)
FROM opportunity_actions AS lookup
WHERE lookup.opportunity_id = opportunity_actions.opportunity_id
) AND
hour = (
SELECT MAX(hour)
FROM opportunity_actions AS lookup
WHERE lookup.opportunity_id = opportunity_actions.opportunity_id AND date=(SELECT MAX(date)
FROM opportunity_actions AS lookup
WHERE lookup.opportunity_id = opportunity_actions.opportunity_id)
)
OR opportunity_actions.id IS NULL)
Sql is working correctly. But I want to write this algorithm with db raw method in laravel. I wrote the code below. But It is not working. Values appear more than once. Please help me.
$data = DB::table('opportunities')
->select(
'opportunities.id',
'opportunity_actions.hour',
'opportunity_actions.date',
'opportunity_actions.id',
DB::raw('(SELECT MAX(opportunity_actions.date) FROM opportunity_actions
WHERE opportunity_actions.opportunity_id = opportunities.id OR (opportunity_actions.id IS NULL)
) as dateMax'),
DB::raw('(SELECT MAX(opportunity_actions.hour) FROM opportunity_actions
WHERE( opportunity_actions.opportunity_id = opportunities.id
AND opportunity_actions.date = dateMax
OR opportunity_actions.id IS NULL)
) as hourMax')
)
->leftJoin('opportunity_actions', 'opportunity_actions.opportunity_id', '=', 'opportunities.id')
->get();
