I'm trying to build an Eloquent query to find a model that has relations where their column is equal to one of the given values, but no other. For example, a model could have several relations those column contains one of the given values, but have another relation that doesn't and should therefore be omitted.
All I have so far is a whereHas query that finds the models whose relations contain the values. This works exactly as it should, but I want to omit the models that contain a relation without these values, even if they have a relation that does.
$query->whereHas('conditions', function($query) use ($category_ids) {
$query->where('conditionable_type', EmployeeCategoryOption::class)
->whereIn('conditionable_id', $category_ids);
});
I've searched the docs but can't find any Eloquant method that does what I'm after.
Thanks for your time.
EDIT (example scenario)
Conditions table:
id | conditionable_id | option_id
1 | 1 | 1
2 | 2 | 1
3 | 3 | 1
4 | 3 | 2
5 | 4 | 3
I would like to get all of the options that have a condition with a conditionable_id equal to 3 or 4 and no others. In the above table this would return option's 2 and 3 as option 1 also has conditionable_id's 1 and 2.
If you have the opposite of the $category_id as $notCategoryIds
$query->whereDoesntHave('conditions', function($query) use ($notCategoryIds) {
$query->where('conditionable_type', EmployeeCategoryOption::class)
->whereIn('conditionable_id', $notCategoryIds);
})->whereHas('conditions', function($query) {
$query->where('conditionable_type', EmployeeCategoryOption::class);
})
If you dont have the invert category list, do it like this.
$query->whereDoesntHave('conditions', function($query) use ($category_id) {
$query->where('conditionable_type', EmployeeCategoryOption::class)
->whereIn(
'conditionable_id',
\DB::table('conditionable_option')->where('conditionable_type', EmployeeCategoryOption::class)
->whereNotIn('conditionable_id', $category_id)
->pluck('conditionable_id')->toArray();
);
})->whereHas('conditions', function($query) {
$query->where('conditionable_type', EmployeeCategoryOption::class);
})
You can change that pluck query inside to a query builder form instead of a fetch result query to make it faster using some joins and aliases.
You can use addSelect() to only add the models you need based on your query.
$query->whereHas('conditions', function($query) use ($category_ids) {
$query->where('conditionable_type', EmployeeCategoryOption::class)
->whereIn('conditionable_id', $category_ids);
})->addSelect(['conditions' => Condition::select('field')
->whereColumn('your_field_id', 'condition.id')
->where('conditionable_type', EmployeeCategoryOption::class)
->whereIn('conditionable_id', $category_ids);
]);
You could refactor the addSelect() to a custom scope function on your model.