Laravel 5 eager loading with limit

I think the solution you are asking for is explained here http://softonsofa.com/tweaking-eloquent-relations-how-to-get-latest-related-model/

Define this relation in User model,

public function latestAction()
{
  return $this->hasOne('Action')->latest();
}

And get the results with

User::with('latestAction')->get();

My solution linked by @berbayk is cool if you want to easily get latest hasMany related model.

However, it couldn't solve the other part of what you're asking for, since querying this relation with where clause would result in pretty much the same what you already experienced - all rows would be returned, only latest wouldn't be latest in fact (but latest matching the where constraint).

So here you go:

the easy way - get all and filter collection:

User::has('actions')->with('latestAction')->get()->filter(function ($user) {
   return $user->latestAction->id_action == 1;
});

---

or the hard way - do it in sql (assuming MySQL):

User::whereHas('actions', function ($q) { 

  // where id = (..subquery..)
  $q->where('id', function ($q) { 

    $q->from('actions as sub')
      ->selectRaw('max(id)')
      ->whereRaw('actions.user_id = sub.user_id');

  })->where('id_action', 1);

})->with('latestAction')->get();

Choose one of these solutions by comparing performance - the first will return all rows and filter possibly big collection.

The latter will run subquery (whereHas) with nested subquery (where('id', function () {..}), so both ways might be potentially slow on big table.

I created a package for this: https://github.com/staudenmeir/eloquent-eager-limit

Use the HasEagerLimit trait in both the parent and the related model.

class User extends Model {
    use \Staudenmeir\EloquentEagerLimit\HasEagerLimit;
}

class Action extends Model {
    use \Staudenmeir\EloquentEagerLimit\HasEagerLimit;
}

Then you can apply ->limit(1) to your relationship and you will get the latest action per user.