問題描述

我有兩個表，比如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)，所以兩種方式在大桌子上都可能會很慢.