I have the following tables:
occupations <- occupation_user -> users
occupations <- occupation_vacancy -> vacancies
And I want to find all vacancies that the current user shares occupations with...
I have it working by querying the occupation_user table to retrieve the users occupations and then using whereIn with the resulting array on the vacancies table.
I have a nagging feeling there may be a better way but I can't see it?
Can anyone advise?
public function scopeFilteredForUser($query, $user_id, $university_id) {
$query->with('occupations')->select('id','university_id','title','employer_name','remuneration','city','state','country','commencement_date','num_positions','expire','contract_type','contract_hours')
->whereDoesntHave('users', function($q) use($user_id) {
$q->where('user_id', $user_id);
})
->withOccupationsFilter($user_id);
}
public function scopeWithOccupationsFilter($query, $user_id) {
$user_occupations = DB::table('occupation_user')
->select('occupation_id')
->where('user_id', '=', $user_id)
->lists('occupation_id');
if (empty($user_occupations)) {
return $query;
}
return $query->whereIn('id', function ($query) use ($user_occupations)
{
$query->select('vacancy_id')
->from('occupation_vacancy')
->whereIn('occupation_id', $user_occupations);
});
}
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire