Laravel Eloquent How Can I Select Using Condition "where" for pivot table

Question

I have three database tables called user(id,name), group(id,name) and user_group(user_id, group_id,valid_before) with relations many to many.

class User extends Model
{
    protected $table = 'user';

    public function groups()
    {
        return $this->belongsToMany(Group::class, 'user_group')
               ->withPivot('valid_before');
    } 
}

class Group extends Model
{
    protected $table = 'group';

    public $timestamps = false;

public function user()
{
    return $this->belongsToMany(User::class, 'user_group');
}
}

How can I select all users (using Eloquent) who have

valid_before < $some_date

?

Answer 1

There are many ways to achieve this goal. I'll show you an example using query scopes.

In your User class you have to make a little update:

class User extends Model
{
    protected $table = 'user';

    public function groups()
    {
        return $this->belongsToMany(Group::class, 'user_group')
               //->withPivot('valid_before'); <-- Remove this
    }
}

and create a scope in your Group model:

class Group extends Model
{
    protected $table = 'group';

    public $timestamps = false;

    public function user()
    {
        return $this->belongsToMany(User::class, 'user_group');
    }    

    /**
     * This scope gets as input the date you want to query and returns the users collection
     *
     * @param  \Illuminate\Database\Eloquent\Builder $query
     * @param  string $date
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeUsersValidBefore($query, $date)
    {
        return $query->users()->wherePivot('valid_before', '<', $date);
    }
}

Now, I imagine you have a GroupController that somewhere creates a query to retrieve the valid before users. Something like:

// [...]

$users = Group::usersValidBefore($yourDate)->get();

// [...]

If you want to create the query from the other side, I mean you want to use the User model and list all the Users that has a pivot relation with valid_before populated, than the right approach is creating a UserGroup intermediate model that can be easily used to create a query.

Answer 2

If you are using Laravel 8.x.x

It's much easier with Inline Relationship Existence Queries

If you would like to query for a relationship's existence with a single, simple where condition attached to the relationship query, you may find it more convenient to use the whereRelation and whereMorphRelation methods. For example, we may query for all posts that have unapproved comments:

use App\Models\Post;

$posts = Post::whereRelation('comments', 'is_approved', false)->get();

Of course, like calls to the query builder's where method, you may also specify an operator:

$posts = Post::whereRelation(
    'comments', 'created_at', '>=', now()->subHour()
)->get();