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

?

Does valid_before field belong to user table? If yes, you don't need pivot here. It will be like select * from users where valid_before < some_date — Tarasovych
– Tarasovych, Commented Sep 24, 2019 at 14:50

Lonare · Accepted Answer · 2021-09-22 12:42:00Z

1

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();

answered Sep 22, 2021 at 12:42

Lonare

4,8012 gold badges56 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

IlGala · Accepted Answer · 2019-09-24 15:10:37Z

0

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.

answered Sep 24, 2019 at 15:10

IlGala

3,4494 gold badges42 silver badges53 bronze badges

2 Comments

Lonare Over a year ago

Query Scopes are not available in Laraval 8.x.x

IlGala Over a year ago

@HarshalLonare yes they are. Have a look at the docs

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related