問(wèn)題描述

我有兩個(gè)表，比如users"和users_actions"，其中users_actions"與用戶有 hasMany 關(guān)系:

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

現(xiàn)在，我想檢索所有用戶的列表以及他們的最后操作.

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

我看到在做 Users::with('action')->get();通過(guò)僅獲取關(guān)系的第一個(gè)結(jié)果，可以輕松地給我最后一個(gè)操作:

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

但我當(dāng)然想避免這種情況，只為每個(gè)用戶選擇最后一個(gè)操作.

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

但是這給了我一個(gè)錯(cuò)誤的結(jié)果，因?yàn)?Laravel 執(zhí)行這個(gè)查詢:

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

這當(dāng)然是錯(cuò)誤的.是否有可能在不使用 Eloquent 對(duì)每條記錄執(zhí)行查詢的情況下獲得此信息?我是否犯了一些我沒(méi)有看到的明顯錯(cuò)誤?我剛開(kāi)始使用 Eloquent，有時(shí)關(guān)系會(huì)困擾我.

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.

代表目的的一部分，我還需要此功能來(lái)在關(guān)系中搜索，例如我想搜索 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);
});

但是這給了我所有有一個(gè) action = 1 的用戶，因?yàn)樗且粋€(gè)日志，每個(gè)人都通過(guò)了這一步.

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

感謝@berkayk 看起來(lái)我解決了問(wèn)題的第一部分，但我仍然無(wú)法在關(guān)系中進(jìn)行搜索.

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

仍然沒(méi)有執(zhí)行正確的查詢，它會(huì)生成如下內(nèi)容:

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 相關(guān)模型，我由 @berbayk 鏈接的解決方案很酷.

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

但是，它無(wú)法解決您所要求的其他部分，因?yàn)槭褂?where 子句查詢這種關(guān)系會(huì)導(dǎo)致您已經(jīng)經(jīng)歷過(guò)的幾乎相同的結(jié)果 - 所有行都會(huì)被返回，只有 latest 實(shí)際上不會(huì)是最新的(但最新的匹配 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).

給你:

簡(jiǎn)單方法 - 獲取所有并過(guò)濾集合:

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

<小時(shí)>

或困難的方式 - 在 sql 中進(jìn)行(假設(shè)為 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();

通過(guò)比較性能選擇其中一個(gè)解決方案 - 第一個(gè)將返回所有行并過(guò)濾可能的大集合.

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

后者將使用嵌套子查詢 (where('id', function () {..}) 運(yùn)行子查詢 (whereHas)，所以兩種方式在大桌子上都可能會(huì)很慢.