using 'And Where' in Many to Many relation

Question

I have two models which have a many-to-many relationship.

class User extends Model
{
    function cars()
    {
        return $this->belongsToMany(Car::class);
    }
}

class Car extends Model
{
    function users()
    {
        return $this->belongsToMany(User::class);
    }
}

I want to get users who used a specific set of cars:

$car_selected = [1, 3, 6];

$users = User::when(count($car_selected) > 0, function ($q) use ($car_selected) {
    $q->whereIn('cars.id', $car_selected);
})
    ->get();

This gives too many results because of the 'whereIn' condition; what I want is 'whereAnd' something.

I tried this, but no luck.

$users = User::when(count($car_selected) > 0, function ($q) use ($car_selected) {
    foreach($car_selected as $xx) {
        $q->where( 'cars.id', $xx);
    }
})
    ->get();

How can I get all users which have a relationship to cars 1, 3, and 6?

Answer 1

Your code provided doesn't make a lot of sense, but based on your explanation, you want to find a user who has a relationship with cars 1 and 3 and 6. Using whereIn() gets you users with relationships with cars 1 or 3 or 6.

Your attempt with multiple where() filters wouldn't work, as this would be looking for a single row in the pivot table with multiple cars, which obviously wouldn't be possible. Instead, you need to nest multiple whereHas() relationship filters into a single where() group like this:

$users = User::where(function ($q) use ($car_selected) {
    foreach ($car_selected as $car) {
        $q->whereHas('cars', function ($query) use ($car) {
            $query->where('car_id', $car);
        });
    }
})
    ->with(['cars' => function ($q) use ($car_selected) {
        $q->whereIn('car_id', $car_selected);
    }])
    ->get();

This is all assuming you've correctly set up your relationships and tables per Laravel standards.

Demo code is here: https://implode.io/anjLGG