I have the following situation:
A table product_mappings and a table product_mapping_options. I now want to find all entries within all product_mappings to which an entry product_mapping_options with certain ids is assigned. For example, this could look like this:
"data" => array:4 [
0 => array:6 [
"id" => 4
"product_id" => 1
"article_number" => null
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
"product_mapping_options" => array:3 [
0 => array:5 [
"id" => 4
"product_mapping_id" => 4
"product_option_id" => 1
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
]
1 => array:5 [
"id" => 5
"product_mapping_id" => 4
"product_option_id" => 3
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
]
2 => array:5 [
"id" => 6
"product_mapping_id" => 4
"product_option_id" => 5
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
]
]
]
1 => array:6 [
"id" => 5
"product_id" => 1
"article_number" => null
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
"product_mapping_options" => array:4 [
0 => array:5 [
"id" => 7
"product_mapping_id" => 5
"product_option_id" => 1
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
]
1 => array:5 [
"id" => 8
"product_mapping_id" => 5
"product_option_id" => 3
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
]
2 => array:5 [
"id" => 9
"product_mapping_id" => 5
"product_option_id" => 5
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
]
3 => array:5 [
"id" => 10
"product_mapping_id" => 5
"product_option_id" => 7
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
]
]
]
2 => array:6 [
"id" => 6
"product_id" => 1
"article_number" => null
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
"product_mapping_options" => array:4 [
0 => array:5 [
"id" => 11
"product_mapping_id" => 6
"product_option_id" => 1
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
]
1 => array:5 [
"id" => 12
"product_mapping_id" => 6
"product_option_id" => 3
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
]
2 => array:5 [
"id" => 13
"product_mapping_id" => 6
"product_option_id" => 5
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
]
3 => array:5 [
"id" => 14
"product_mapping_id" => 6
"product_option_id" => 8
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
]
]
]
3 => array:6 [
"id" => 7
"product_id" => 1
"article_number" => null
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
"product_mapping_options" => array:4 [
0 => array:5 [
"id" => 15
"product_mapping_id" => 7
"product_option_id" => 1
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
]
1 => array:5 [
"id" => 16
"product_mapping_id" => 7
"product_option_id" => 3
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
]
2 => array:5 [
"id" => 17
"product_mapping_id" => 7
"product_option_id" => 5
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
]
3 => array:5 [
"id" => 18
"product_mapping_id" => 7
"product_option_id" => 9
"created_at" => "2021-11-26T09:47:25.000000Z"
"updated_at" => "2021-11-26T09:47:25.000000Z"
]
]
]
]
Here I searched via product_mappings with the following search scope:
public function scopeProductOption(Builder $query, ...$productOptionTypeIds): Builder
{
foreach($productOptionTypeIds as $productOptionTypeId) {
$query->whereHas('ProductMappingOptions', function (Builder $query) use ($productOptionTypeId) {
$query->where('product_option_id', $productOptionTypeId);
});
}
return($query);
}
In the example I specified the values 1,3 and 5 (which was then passed into $productOptionTypeIds).
What I would like now: I would like to get 100% exactly displayed which product_mappings match the search, Say I would like to find e.g. only one entry where exactly 1,3 and 5 is mapped and not also 1,3,5 and 7,9 or 11. So the entries which then have 4 relations should fall away, because I don't want to have the entries where something is "over".
Do you have any idea how I can "catch" exactly this one entry? So in the example above I would only want entry 0 from data. If I specify 1,3,5 and 9, I would only want entry 3 from data.
CodePudding user response：
Okay, found a solution and it was really easy:
public function scopeProductOption(Builder $query, ...$productOptionTypeIds): Builder
{
$query->has('ProductMappingOptions', '=', count($productOptionTypeIds));
foreach($productOptionTypeIds as $productOptionTypeId) {
$query->whereHas('ProductMappingOptions', function (Builder $query) use ($productOptionTypeId) {
$query->where('product_option_id', $productOptionTypeId);
});
}
return($query);
}
I've added a counting for relations
CodePudding user response：
Instead of loop statment you can use whereIn function
public function scopeProductOption(Builder $query, ...$productOptionTypeIds): Builder
{
//this will parse the values from spread operator
if ($productOptionTypeIds !== []) {
if (count($productOptionTypeIds) !== count($productOptionTypeIds, COUNT_RECURSIVE)) {
$productOptionTypeIds = iterator_to_array(new \RecursiveIteratorIterator(new \RecursiveArrayIterator($productOptionTypeIds)));
}
$query->whereHas('ProductMappingOptions', function (Builder $query) use ($productOptionTypeIds) {
$query->where('product_option_id', $productOptionTypeIds);
});
}
return ($query);
}