query joins only last row from relationship

Question

User can apply many times to single order. The last apply is the one I want. My code:

$users = User::whereHas('applies', function ($query) use ($order_id){ $query->where('order_id', $order_id); })->with(['applies' => function($query) use ($order_id){ $query->orderBy('id', 'desc')->where('order_id', $order_id)->first(); }])->paginate($request->perPage);

This limit the number of applies only in the last user, the other applies records are empty. Removing first() from with() shows all applies in all users. How is that? Please help.

I've tried limit(1), take(1)->get(), take(1), but it also fails.

The right method would be `limit(1)`. `first()` is used to run the query and return the result, same with `get()`. — Devon Bessemer, Aug 01 '18 at 12:43

score 1 · Accepted Answer · answered Aug 01 '18 at 12:51

There is no simple way to limit eager loading.

You can use a HasOne relationship:

public function latestApply() {
    return $this->hasOne(Apply::class)->orderBy('id', 'desc');
}

$users = User::whereHas('applies', function ($query) use ($order_id){
        $query->where('order_id', $order_id);
    })->with(['latestApply' => function($query) use ($order_id){
        $query->where('order_id', $order_id);
    }])->paginate($request->perPage);

This will still fetch all applies in the background, but only show the latest one for each user.

See also https://stackoverflow.com/a/50571322/4848587 for other possible solutions.

query joins only last row from relationship

1 Answers1