Back

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


westphalen posted 2 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