php - Laravel 5 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

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