Back

Shouldn't we make `newPivotQuery` a public method?


westphalen posted 11 months ago

The InteractsWithPivotTable trait has public methods such as newPivotStatement and newPivotStatementId, but to actually get the query builder for the pivot table that would be used by $model->related()->get() you would need access to the protected method newPivotQuery.

Take for example:

class User extends Model
{
    public function devices()
    {
        return $this->belongsToMany(Device::class)
            ->withTimestamps()
            ->withPivot('whatever');
    }
 }

Now I might want to trim the User's Devices to have only the 3 most recently attached.

$this->user->devices()->newPivotQuery()
    ->whereNotIn('id', $this->user->devices()
        ->newPivotQuery()
        ->orderBy('created_at', 'DESC')
         ->limit(3))
    ->delete();

If the newPivotQuery function was accessible, the following SQL should be generated:

DELETE FROM `device_user` WHERE `user_id` = ? AND `id` NOT IN (SELECT `id` FROM `device_user` WHERE `user_id` = ? ORDER BY `created_at` DESC LIMIT 3);

I believe this SQL is the ideal way to delete with an offset.

What do you think? Is there a better way to achieve this, or have no one ever needed to perform advanced queries on pivot tables directly?

My redundant work-around is currently $user->device->newPivotStatement()->where('user_id', $user->id).


Sign in to participate in this thread!


We'd like to thank these amazing companies for supporting us