0

Let's say I have these tables: users, languages, language_user. The last one is a pivot table with an additional field rating that defines the level of competence of a User for that particular language (mother tongue, good, not good, ...).
I know how to get all users that can speak at least one of a particular set of languages:

$query->whereHas('languages', function($qry){                           
    $qry->whereIn("languages.id", [1, 8, 999]);                             
});

But what if I also want to filter based on rating for each language? So I want a User that can speak language 1 with a rating of 5, or language 8 with a rating of at least 3?

I tried with a loop inside the whereIn closure to iterate over all values and added a where subquery to match a particular value, but it doesn't work.

user852690
  • 111
  • 1
  • 11

1 Answers1

0

You have to wrap your inner query:

$query->whereHas('languages', function($query)
{    
    foreach ([1 => 5, 8 => 3] as $language => $rating)
    {
        $query->where(function($query) use ($language, $rating)
        {
            $query->where('languages.id', $language)
                  ->where('languages.rating', '>=' $rating);
        });
    }
});
Joseph Silber
  • 214,931
  • 59
  • 362
  • 292
  • Thank you. Almost there! The solution (almost identical to yours) has been posted in SO: [link](http://stackoverflow.com/questions/28160607/laravel-filter-many-to-many-on-multiple-values#answer-28376060) – user852690 Apr 27 '15 at 20:01