Limiting retrieved columns when using withPivot on belonsToMany relationship

I have a model called Shifts with a belongsToMany relationship to a shift_employee table that acts as a pivot table to record applications for employees to shifts. I also have a scope so that I can return applications with shift objects. Here is part my Shift model:

class Shift extends Model
{
    //
    use SoftDeletes;
    use AppHttpTraitsUsesUuid;

    protected $guarded = [];

    public function applications()
    {
        return $this->belongsToMany(Employee::class, 'shift_employee')->as('application')->withTimestamps()->withPivot('shortlisted');
    }

...
    public function scopeWithApplications($query)
    {
        $query->with('applications');
    }
...
}

My shift_employee pivot table is pretty simple and the structure is shown below. I have one extra field to determine if an application has been shortlisted:

        Schema::create('shift_employee', function (Blueprint $table) {

        $table->primary(['employee_id', 'shift_id']);
        $table->uuid('employee_id');
        $table->uuid('shift_id');
        $table->boolean('shortlisted')->default(false);
        $table->timestamps();

        $table->foreign('employee_id')
            ->references('id')
            ->on('employees');

        $table->foreign('shift_id')
            ->references('id')
            ->on('shifts')
            ->onDelete('cascade');
        });

Below is my API show function for retrieving shift info:

public function show($id)
{
    $shift = Shift::where('id', $id)
        ->with...()
        ->withApplications()
        ->with...()
        ->first();

    return response([
        'shift' => $shift,
    ]);
}

This is the response that I’m getting:

"shift": {
    "id": "2b91f55b-c0ff-4bdb-abc4-02604ba6a161",
    "some_field": "some_value",
    ...
    "applications": [
        {
            some_field: "some_value",
            ...
            application: {
                shift_id: "2b91f55b-c0ff-4bdb-abc4-02604ba6a161",
                employee_id: "some_uuid",
                created_at: ...,
                updated_at: ...,
                shortlisted: 0
            }
        },
        {
        ...
        }
    ]
...
}

What I want to do, is to replace the whole “application” inner object with only the field “shortlisted” from the pivot table so that it looks like this:

"shift": {
    "id": "2b91f55b-c0ff-4bdb-abc4-02604ba6a161",
    "some_field": "some_value",
    ...
    "applications": [
        {
            some_field: "some_value",
            ...
            shortlisted: 0
            }
        },
        {
        ...
        }
    ]
...
}

How can I do that? Ideally an eloquent call to something like withPivot but that excludes other fields and does not return an object. I couldn’t find it in the docs, but does something like that exist?

Answer

i think that the most straightforward way is to make independent relation based on the pivot table using pivot model:

class ShiftEmployee extends Pivot
{
    protected $table='shift_employee';
}

now the new relation in Shift Model:

class Shift extends Model
{
    public function shortlistedApplications()
    {
        return $this->hasMany(ShiftEmployee::class,'shift_id');
    }
 public function scopeWithShortlistedApplications($query)
    {
        $query->with('shortlistedApplications:shift_id,shortlisted');
    }
}

now this new scope would bring the data you want

Advertisement

Answer