Get pivot data from another model as query scope.

Hi everyone,

I'm struggling with a bit of an odd query. my models are as follows:

class Register extends Model
{
    public function users()
    {
        return $this->belongsToMany(User::class)
            ->withPivot([
                'credit',
            ]);
    }
}

class User extends Model
{
    public function registers()
    {
        return $this->belongsToMany(Register::class)
            ->withPivot([
                'credit',
            ]);
    }
}


class Accreditation extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function register()
    {
        return $this->belongsTo(Register::class);
    }
}

So i can access both ids from the Accreditation model.

I'm trying to make a query scope where only records with credit = 1 will show up. So far i managed to get it to work by making an extra method and filtering by that with Collections, but i'd really like to chain this with other query methods.

I would appreciate any suggestions.

tykus

8 years ago

Level 104

is Accreditation a model representing the pivot table?

J_shelfwood

8 years ago

Level 13

No that is a model that has an id of both other models. I don't have a model representing the pivot table. My pivot table is register_users and it has column called credit.

Now let's say it has a record like register_id = 1 user_id = 2 credit = 0

And i have a record in my accreditations table where register_id = 1 user_id = 2

I want to scope my query by the credit value of the pivot table.

J_shelfwood

8 years ago

Level 13

This method on the Accreditation model is the closest i've come to what i want:

public function credit()
{
    return $this->user->registers
        ->where('id', $this->register_id)
        ->first()['pivot']['credit'];
}

But this can't be used as a query scope, only to filter an existing collection. This would work fine but it doesn't allow for pagination, unfortunately.

J_shelfwood

8 years ago

Best Answer

Level 13

Ok i figured it out. Here's the solution i found:

I added these relations

class User 
{
    public function registers_with_credit()
    {
        return $this->belongsToMany(Register::class)
            ->withPivot('credit')
            ->withTimestamps();
            ->wherePivot('credit', 1);
    }

class Register 
{
    public function users_with_credit()
    {
        return $this->belongsToMany(User::class)
            ->withPivot('credit')
            ->withTimestamps();
            ->wherePivot('credit', 1);
    }

And then the query scope:

class Accreditation
{
    public function scopeWithCredit($query)
    {
        return $query
            ->has('user.registers_with_credit')
            ->has('register.users_with_credit');
    }

It's weird and very specific but maybe this will save someone a headache at some point.

Please or to participate in this conversation.