Level 73
It's always easier if you show us your migrations, your data and the expected result.
Jan 19, 2021
11
Level 1
WhereIn clause to check a Model contains all array elements
I have two models RaterType and QuestionType and they have a many-to-many relationship. I have a select element with multiple attribute turned on to get RaterTypes those exactly have the question_type_ids selected with the select field.
Here is what I mean, one RaterType might have two or more QuestionType. So, when I select one QuestionType from the select element I wanted to get all RaterType with selected QuestionType and if I selected another one I wanted to get all RaterType with both selected QuestionTypes.
I tried using whereIn clause but am not getting what I was looking, wehere clause just only checks one value not an array. How am I going to achieve this?
Level 1
Here are my migrations for both models
Schema::create('rater_types', function (Blueprint $table) {
$table->id();
$table->string('name');
});
Schema::create('question_types', function (Blueprint $table) {
$table->id();
$table->string('name');
});
// Pivot Table
Schema::create('question_type_rater_type', function (Blueprint $table) {
$table->foreignId('question_type_id')->constrained()->cascadeOnDelete();
$table->foreignId('rater_type_id')->constrained()->cascadeOnDelete();
});
Example Data:
// RaterType
| id | name |
|----|-------------|
| 1 | Teacher |
| 2 | Leader |
| 3 | Supervisor |
| 4 | Student |
// QuestionType
| id | name |
|----|-----------------|
| 1 | Multiple Choice |
| 2 | True or False |
| 3 | Write |
// pivot table
| question_type_id | rater_type_id |
|------------------|---------------|
| 1 | 1 |
| 1 | 2 |
| 3 | 1 |
| 3 | 3 |
| 2 | 2 |
| 4 | 1 |
| 4 | 2 |
Now if I select from my dropdown Multiple Choice option I should get Teacher, Supervisor, and Student and if I add True or False to my selected options I should get only Teacher and Student and if I add Write to my selected options I should get none since there is no RaterType related with all QuestionType.
Keep in mind that my select box is a multiple select box. Now I guess I made my self clear.
Level 27
If I understand you correctly, a subquery could do the trick:
RaterType::query()->whereHas('questionTypes', function(Builder $subQuery) use ($questionTypeIds) {
$subQuery->whereIn('question_type_id', $questionTypeIds);
}, '=', count($questionTypeIds));
But in general it's way easier to give assistance if you keep in mind what @tray2 said.
Level 1
Thanks a lot for your quick response and I already replied to @tray2 's request, can you please take a look at it one more time?
Level 27
Assuming you have a relationship "questionTypes" in your RaterType model the above should work.
Level 1
Nope. It doesn't work. Here is the code:
$selectedQuestionTypeIds = $this->questionTypeFilter;
$this->raterTypesData->whereHas('questionTypes', function($query) use ($selectedQuestionTypeIds) {
// $query->whereIn('question_type_id', $this->questionTypeFilter);
foreach ($selectedQuestionTypeIds as $questionTypeId)
$query->where('question_type_id', '=', $questionTypeId);
});
Level 27
What exactly doesn't work? Is your $selectedQuestionTypeIds an array/collection containing the IDs?
And what does $this->raterTypesData return?
Level 1
The collection that I'm getting, as a result, isn't the one I was looking for. It works well if there is only 1 selected item but if you select another one it gives me an empty RaterType result.
I am using Livewire and $selectedQuestionTypeIds this holds the modeled data of selected Ids of QuestionTypes
Level 27
Okay, I was a little bit confused tbh. I tweaked the query a little bit, hopefully that's what you are looking for.
Level 1
@bitzsalex Thanks for this code. I got great help. I have a question. Hope you can help me. I have a similar code. what I want is to retrieve data if only $selectedQuestionTypeIds and all question_type_id in questionTypes are the same. Based on the above code i will get data in a subset of selectedQuestionTypeIds exists in questionTypes
Level 1
If I understand correctly what you want this should work:
$selectedQuestionTypeIds = $this->questionTypeFilter;
$this->raterTypesData->whereHas('questionTypes', function($query) use ($selectedQuestionTypeIds){
$query->select(\DB::raw('count(distinct id)'))->whereIn('question_type_id', $selectedQuestionTypeIds);
}, '=', count($selectedQuestionTypeIds))->get();
Please or to participate in this conversation.