Laravel whereHas relation where column can be equal to any in array but no other

user_7552631 0
Viewed 830

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.

eloquent laravel php
2 Answers

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.

user_43699190

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.

user_32836980
Related