php - Laravel: pivot table extra field in model

Let's assume I've got a User model and the Country model. User could have up to four countries simultaneously (home country and three countries he'd like to visit). Using Eloquent, I'd create a pivot able country_user with an extra field of type, that would correspond to the choice of the user, which country he'd put in the first place etc. So once again, there is:

    User
     --id

    Country
     --id

    country_user
     --id
     --user_id
     --country_id
     --type

I need to get all users who have, let's say, Canada as a country type 1, UK as a country type 2, France as a country type 3 and New Zealand as a country type 4. If I understand correctly then I can't use wherePivot to do this, only if I get a User::all() and iterate through this collection (which doesn't make much sense and puts unnecessary load on the DB). What could be the proper way to do this with a minimum possible amount of queries to DB? Or do I get it all wrong and there should be another approach to this task at all?

Solution:

You can still search on the pivot table using the relationship you have defined between a User and Country. If for the sake of this example we call that relationship countries and that you want to find people with the following $criteria (sticking to your example):

$criteria = [
    'canada' => 1,
    'uk' => 2,
    'france' => 3,
    'new zealand' => 4,
];

So the key in the above is the Country and the value is the type.

To find Users that have those exact requirements, you could do the following:

$users = User::whereHas('countries');

collect($criteria)->each(function ($type, $country) use ($users) {
    $users->whereHas('countries', function (Builder $query) use ($type, $country) {
        $query->where(['name' => $country, 'type' => $type]);
    })->get();
});

Then do something with your $users.

$users->get();

Solution:

You can the country relationship of HasMany

So, in users it would be this

public function countries() {
     return $this->hasMany('country_user_class', 'foreign_key_id_here')->latest()->take(4); // take 4 since the user can have 4 countries
}

The query would look something like this.

User::whereHas('countries', function($query) {
    $query->where('type', [1,2,3,4]);
})->get();

This would give you the result. This is how you can achieve it using Query. Such a problem can always be solved by Query. Let me know if you've any more question regarding this.