Level 24
Use this:
$ids = [1, 2];
$orders = Order::has('tags', '=', count($ids))
->whereHas('tags', function($q) use($ids) {
$q->whereIn('tag_id', $ids);
}, '=', count($ids))
->get();
1 like
Mar 14, 2019
6
Level 2
Many to Many whereHas multiple variables
I have two models, Order and Tag, in a many to many relationship. A simplistic version of their tables are below.
Table orders
+-----------+------------+
| id | number |
+-----------+------------+
| 1 | 5000 |
| 2 | 5001 |
| 3 | 5002 |
+-----------+------------+
Table tags
+-----------+------------+
| id | label |
+-----------+------------+
| 1 | Red |
| 2 | Green |
| 3 | Blue |
+-----------+------------+
Table order_tag
+-----------+------------+
| order_id | tag_id |
+-----------+------------+
| 1 | 1 |
| 2 | 1 |
| 2 | 2 |
| 3 | 3 |
+-----------+------------+
In the tables above, the orders and their cooresponding tags are as follows:
5000: Red
5001: Red Green
5002: Blue
What I wish to accomplish is to find an order that exactly matches multiple tags. No more, no less. In the above example, I'm looking for an eloquent way (if possible) to select orders tagged with both Red AND Green, not Red OR Green.
Using this query builder:
$order = Order::whereHas('tags', function($q) {
$q->whereIn('tag_id', [1,2]);
})->get();
I get two results order 5000 and order 5001. As described in the tables, each are tagged with Red and/or Green, but only one is tagged with both. I am only looking to retrieve order 5001 which is tagged with both Red AND Green. The block of code is grabbing records that are tagged with either Red OR Green.
In essence, the goal I'm trying to achieve is to filter orders through a tagging system where an order is retrieved only if it matches all of the tags requested by a user.
Any suggestions on how to accomplish this are welcome. Thanks in advance!
Level 2
@STAUDENMEIR - That is perfect, thank you!
Level 2
@STAUDENMEIR - Can you briefly explain how this works? I think I understand, but the last part }, '=', count($ids) is a bit confusing to me.
Level 24
Have you looked at the executed query? Replace ->get() with ->toSql().
Level 2
I did. It's a lot to digest. I'm a novice when reading and writing sql.
Level 24
Does this equivalent query with a whereHas() clause for every tag help you understand it?
$ids = [1, 2];
$orders = \App\Order::has('tags', '=', count($ids))
->whereHas('tags', function($q) use($ids) {
$q->where('tag_id', $ids[0]);
})
->whereHas('tags', function($q) use($ids) {
$q->where('tag_id', $ids[1]);
})
->get();
1 like
Please or to participate in this conversation.