Simple version of database.
mortgages
| id | case_id(cases@id) |
| 1 | 1 |
| 2 | 2 |
cases
| id | label |
| 1 | ABC |
| 2 | DEF |
cases_timeline
| id | case_id(cases@id) | event_id(events@id) | notes |
| 1 | 1 | 1 | The case was created. |
| 2 | 1 | 2 | The applicant was contacted. |
| 3 | 2 | 1 | The case was created. |
events
| id | category | type |
| 1 | GENERAL | CREATED |
| 2 | MORTGAGE | CONTACTED |
So I want to be able to create a query in Eloquent that will get a list of products that HAS NOT got MORTGAGE-CREATED event. The following query doesn't work
SELECT mortgages.*
FROM mortgages
LEFT JOIN cases on cases.id = mortgages.case_id
LEFT JOIN cases_timeline on cases.id = cases_timeline.case_id
LEFT JOIN timeline_events on cases_timeline.event_id = timeline_events.id
WHERE
timeline_events.type = 'CONTACTED'
AND
timeline_events.id IS NULL
GROUP BY mortgages.id
Any help would be appreciated a lot.
Last updated
2 years ago.
You want to retrieve mortgages not products
and event to check is MORTGAGE, CONTACTED, right?
Mortgage::whereHas('case', function($q) {
$q->whereHas('events', function($q){
$q->where('type', '=', 'CONTACTED'); // or better check for event id here, up to you
}, '<', 1); // -> not having that event
})->get();
// as long as you have the relations:
// Case model
public function events()
{
return $this->belongsToMany('Event', 'cases_timeline')
}
// Mortgage model
public function case()
{
return $this->belongsTo('Case');
}
Last updated
2 years ago.
Sign in to participate in this thread!
Laravel
Laravel News
Laracasts
Laravel Podcast