Level 75
Jun 7, 2022
11
Level 4
How to write this script?
I have general pivot table which represents data:
+----+------------+--------+
| id | product_id | tag_id |
+----+------------+--------+
| 1 | 56 | 1 |
+----+------------+--------+
| 2 | 56 | 5 |
+----+------------+--------+
| 3 | 56 | 3 |
+----+------------+--------+
| 4 | 89 | 1 |
+----+------------+--------+
| 5 | 89 | 9 |
+----+------------+--------+
| 6 | 89 | 2 |
+----+------------+--------+
| 7 | 89 | 5 |
+----+------------+--------+
Then I have multidimensional array which represents filter:
$selected = [
0 => [5, 6]
1 => [7, 8]
2 => [9]
];
I want retrieve all product_id from pivot table that fits array filter.
Get all product_ids WHICH HAVE tag_id (5 OR 6) AND (7 OR 8) AND (9).
Level 4
I'm aware of all of linked functions. But I don't know what the first step should be.
I think I need for each product_id get list of all of its tag_ids (or somehow merge all product lines into a single list of tags). Should I somehow group that table. Or generate for each product_id array of its tags? And then filter?
Level 75
@andyandy What was difficult on that?
$productIds = DB::table('product_tag')
->where(function ($query) use ($selected) {
foreach ($selected as $tagIds) {
$query->whereIn('tag_id', $tagIds);
}
})->pluck('product_id');
Level 4
That won't work.
Because row looks like this:
id | product_id | tag_id
1 | 59 | 7
And you are doing
->whereIn('tag_id', [5, 6])->whereIn('tag_id', [7,8])->whereIn('tag_id', [9])
None of the rows can be tag_id = 5 and 8 and 9 at the same time.
I think you are missing the first step, where you aggregate data for each product, product_id 81: { tag_ids: 5,6,8,1,24} (and that's where I'm stuck?)
Level 75
@andyandy The query is exactly the same what you want before...
Get all product_ids WHICH HAVE tag_id (5 OR 6) AND (7 OR 8) AND (9).
Level 4
You have pivot table. And the task is Get all product_ids WHICH HAVE tag_id (5 OR 6) AND (7 OR 8) AND (9). If you just blindly turn that task into query it won't work.
Just look at the structure of table. And query you doing. It can't work like this.
Level 75
$productIds = Product::where(function ($query) use ($selected) {
foreach ($selected as $tagIds) {
$query->orWhereHas('tags', function ($query) use ($tagIds) {
$query->distinct()->whereIn('id', $tagIds);
}, '=', count($tagIds));
}
})->pluck('id');
This is
Get all product_ids WHICH HAVE tag_id (5 AND 6) OR (7 AND 8) OR (9).
Level 4
Level 75
$productIds = Product::where(function ($query) use ($selected) {
foreach ($selected as $tagIds) {
$query->whereHas('tags', function ($query) use ($tagIds) {
foreach ($tagIds as $tagId) {
$query->orWhere('id', $tagId);
}
});
}
})->pluck('id');
Level 4
OK, I put it together base on you example. Works as expected.
Thank you very much for your help. :)
Level 4
Yeah, that's exactly what I did. Thank you.
Please or to participate in this conversation.