CODEWITHSUNDEEP
laraveleloquent
Ulrira
Ulrira

Reputation: 219

Laravel - Get an user who has specific relationships

I have a user with countries relationship. (hasMany)

user_id 1
country_id 1

user_id 1
country_id 2

...

What I want is to get those users who have both countries (country 1 and country 2) How can I do this? I'm reading http://laravel.com/docs/eloquent#querying-relations but I'm not very sure what to do.

edit: almost the solution

$users = \User::whereHas('countries', function($query) {
    $countries_ids = [1, 2, 4];
    $query->whereIn('users_countries.country_id', $countries);
})->get();

Upvotes: 0

Views: 1155

Answers (2)

Dimitrios Kontoulis
Dimitrios Kontoulis

Reputation: 420

You can do it the other way. Make the query on the country model. Add a function in the country model

 public function user(){
  return $this->belongsTo('user');//or if there is no user_id column in the countries table, use the ('model','local_key','foreign_key') pattern.
 }

Then create an array with the country_ids, and que the country model.

 $country_ids=array(4,67);
 $countries=Country::where('id',$country_ids[0])->where('id',$country_ids[1])->get();

In order to get the users you should loop through like this

foreach($countries->user as $user)

Upvotes: 0

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81147

This will work as long as there are no duplicates in the pivot table (unique index for pair of fk keys for example):

$countries = [1,5,9];

User::whereHas('countries', function ($q) use ($countries) {
  $q->whereIn('users_countries.country_id', $countries);
}, '=', count($countries) )->get();

In case there are duplicates, like:

user_id | country_id
   1    |     1
   1    |     2
   1    |     1

Then you would need raw count(distinct ...) so you couldn't use whereHas, so I suggest this, which will be much faster:

$countries = [1,5,9];

User::join('users_countries as uc','uc.user_id', '=', 'users.id')
  ->whereIn('uc.country_id', $countries)
  ->having(DB::raw('count(distinct uc.country_id)'), '=', count($countries))
  ->groupBy('users.id')
  ->get(['users.*']);

Upvotes: 3

Related Questions