Laravel eager loading with limit

Question

I have two tables, say "users" and "users_actions", where "users_actions" has an hasMany relation with users:

users

id | name | surname | email...

actions

id | id_action | id_user | log | created_at

Model Users.php

class Users {
    public function action()
    { 
       return $this->hasMany('Action', 'user_id')->orderBy('created_at', 'desc');
    }
}

Now, I want to retrieve a list of all users with their LAST action.

I saw that doing Users::with('action')->get(); can easily give me the last action by simply fetching only the first result of the relation:

foreach ($users as $user) {
   echo $user->action[0]->description;
}

but I wanted to avoid this of course, and just pick ONLY THE LAST action for EACH user.

I tried using a constraint, like

Users::with(['action' => function ($query) {
    $query->orderBy('created_at', 'desc')
          ->limit(1);
    }])
->get();

but that gives me an incorrect result since Laravel executes this query:

SELECT * FROM users_actions WHERE user_id IN (1,2,3,4,5)
ORDER BY created_at
LIMIT 1

which is of course wrong. Is there any possibility to get this without executing a query for each record using Eloquent? Am I making some obvious mistake I'm not seeing? I'm quite new to using Eloquent and sometimes relationship troubles me.

Edit:

A part from the representational purpose, I also need this feature for searching inside a relation, say for example I want to search users where LAST ACTION = 'something'

I tried using

$actions->whereHas('action', function($query) {
    $query->where('id_action', 1);
});

but this gives me ALL the users which had had an action = 1, and since it's a log everyone passed that step.

Edit 2:

Thanks to @berkayk looks like I solved the first part of my problem, but still I can't search within the relation.

Actions::whereHas('latestAction', function($query) {
    $query->where('id_action', 1);
});

still doesn't perform the right query, it generates something like:

select * from `users` where 
 (select count(*) 
   from `users_action` 
   where `users_action`.`user_id` = `users`.`id` 
   and `id_action` in ('1')
  ) >= 1 
order by `created_at` desc

I need to get the record where the latest action is 1

Answer 1

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.

Answer 2

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();