Laravel WhereIn as AND instead of OR

Question

Let's say I got an array: [2,3,4]

(array length / amount of values in array changes so I cannot hardcode it with bunch of where's)

And I want to get only the items which include all of the values in array while querying a relationship.

I am aware of WhereIn and using it like so:

 ->whereHas('interests', function($query) use ($arrayIds) {
     $query->whereIn('interest_id', $arrayIds);
 })

But this is basically

orWhere()->orWhere->get();

How can I achieve behaviour of:

->where()->where()->get();

So that all values in array would have to match instead while using an array of id's?

When you say get the items which include all of the values in array what do you mean exactly? — atymic
– atymic, Commented Jul 20, 2019 at 1:12
Do you just want to get only the items with ids, 2, 3 and 4? — atymic
– atymic, Commented Jul 20, 2019 at 1:13
Yes, precisely. Only items with id's 2,3 and 4. Sorry about the bit bad explanation in question. — EmJeiEn
– EmJeiEn, Commented Jul 20, 2019 at 1:14
But how would a row have an id of 2 AND and id of 3 AND an id of 4? Is this an array in a json column? — newUserName02
– newUserName02, Commented Jul 20, 2019 at 1:26

newUserName02 · Accepted Answer · 2019-07-20 01:49:55Z

1

I'm going to answer this with users and clubs. If a user can belong to many clubs, this will give you users that belong to club 2 AND club 3 AND club 4:

$clubIds = [2,3,4];     // club IDs
$users = User::query(); // initialize the query

// loop over club IDs, and give a whereHas clause for each one
foreach($clubIds as $clubId) {
    $users = $users->whereHas('clubs', function($clubQuery) use ($clubId) {
        // specify "clubs.id" to avoid ambiguity
        $clubQuery->where('clubs.id', $clubId); 
    });
}

$users = $users->get(); // execute the query

answered Jul 20, 2019 at 1:49

newUserName02

1,70814 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

EmJeiEn Over a year ago

Thank you! That indeed did the trick. Had no idea at all that we can "split" the query like that. Extremely useful information here for the future.

newUserName02 Over a year ago

@EmJeiEn No problem! But please update your question to say that you wanted to query a related model, and put the name of the relation in your whereHas instead of the foreign key. It will be easier for people seeking similar answers in the future.

Murwa · Accepted Answer · 2019-07-20 01:46:38Z

0

I'm not sure why you need an AND on ids, but you can loop over the ids like so:

 ->whereHas('interest_id', function($query) use ($arrayIds) {
    foreach($arrayIds as $id){
        $query->where('id', $id);
    }
 })

edited Jul 20, 2019 at 1:46

answered Jul 20, 2019 at 1:40

Murwa

2,2881 gold badge21 silver badges22 bronze badges

5 Comments

EmJeiEn Over a year ago

Thanks for the reply. Tried that actually earlier myself but it it always returned empty enven though I know 100% that there's matches. Though I think the right format would be foreach ($arrayIds as $arrayId) for the loop? :)

Murwa Over a year ago

Hahaha. True, wrote some js in there. Could you check the query being executed here?

EmJeiEn Over a year ago

Yeah, happens to me often as well :). But I just retried it, unfortunately it does return empty results. First my first thought to try as well in the beginning.

Murwa Over a year ago

So, check this out. stackoverflow.com/questions/53849011/…

EmJeiEn Over a year ago

thanks, got it solved with @newUserName02's answer though :).

Collectives™ on Stack Overflow

Laravel WhereIn as AND instead of OR

2 Answers 2

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related