问题描述

我有两个表，比如users"和users_actions"，其中users_actions"与用户有 hasMany 关系:

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

用户

id | name | surname | email...

操作

id | id_action | id_user | log | created_at

模型用户.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

这当然是错误的.是否有可能在不使用 Eloquent 对每条记录执行查询的情况下获得此信息?我是否犯了一些我没有看到的明显错误?我刚开始使用 Eloquent，有时关系会困扰我.

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

但是这给了我所有有一个 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.

感谢@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

我需要获取 latest 操作为 1 的记录

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

推荐答案

如果您想轻松获得最新的 hasMany 相关模型，我由 @berbayk 链接的解决方案很酷.

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

但是，它无法解决您所要求的其他部分，因为使用 where 子句查询这种关系会导致您已经经历过的几乎相同的结果 - 所有行都会被返回，只有 latest 实际上不会是最新的(但最新的匹配 where 约束).

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.

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