Laravel 5渴望加载极限 [英] Laravel 5 eager loading with limit

问题描述

我有两个表，说users和users_actions，其中users_actions与用户有一个hasMany关系：

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

users

id | name | surname | email...

动作

id | id_action | id_user | log | created_at

模型Users.php

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.

我看到，在 Users :: with（'action'） - > get（）;
可以通过简单地获取关系的第一个结果来轻松地给我最后一个动作：

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.

我尝试使用约束，如

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

但是，由于Laravel执行此查询，所以给我一个不正确的结果：

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.

编辑：

从代表目的的一部分，我还需要这个功能来在一个关系之间进行搜索，例如我想要搜索用户在哪里LAST ACTION ='something'

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'

我尝试使用

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

但这给了我所有已经有一个动作= 1的用户，而且由于它是一个日志通过了这一步。

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

感谢@berkayk看起来像我解决了我的问题的第一部分，但仍然无法搜索关系。

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

推荐答案

如果您想轻松获取最新的 hasMany 相关模型。

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).

所以这里你去：

简单的方法 - 所有和过滤器集合：

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

---

或硬的方式 - 在sql （假定MySQL）中执行此操作：

---

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.

后者将使用嵌套子查询运行子查询（ whereHas ）（ where （'id'，function（）{..} ），所以这两种方式在大桌上可能会很慢。

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

这篇关于Laravel 5渴望加载极限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！