問題描述

我正在嘗試將一些原始 SQL 遷移到模型上的 Eloquent(或查詢生成器)范圍.我的零件歷史記錄表如下所示:

I am trying to migrate some Raw SQL to an Eloquent (or Query Builder) scope on my model. My Parts history table looks like this:

+----+---------+--------+------------+
| id | part_id | status | created_at |
+----+---------+--------+------------+
|  1 |       1 |      1 | ...        |
|  2 |       1 |      2 | ...        |
|  3 |       2 |      1 | ...        |
|  4 |       1 |      2 | ...        |
|  5 |       2 |      2 | ...        |
|  6 |       1 |      3 | ...        |

請注意，同一個 part_id 可以有多個狀態相同的條目.

Notice the same part_id can have multiple entries where the status is the same.

目前我使用以下選擇最新狀態:

At the moment I use the following to select the latest status:

$part = Part::leftjoin( DB::raw("
 (SELECT t1.part_id, ph.status, t1.part_status_at 
  FROM (
    SELECT part_id, max(created_at) part_status_at
    FROM part_histories
    GROUP BY part_id) t1 
  JOIN part_histories ph ON ph.part_id = t1.part_id AND t1.part_status_at = ph.created_at) as t2
  )", 't2.part_id', '=', 'parts.id')->where( ... )

我正在嘗試在零件模型上制作一個范圍，到目前為止我有這個:

I am trying to make a scope on the parts model out of this, so far I have this:

public function scopeWithLatestStatus($query)
{
    return $query->join(DB::raw('part_histories ph'), function ($join) {
         $join->on('ph.part_id', '=', 't1.id')->on('t1.part_status_at', '=', 'ph.created_at');
      })
      ->from(DB::raw('(select part_id as id, max(created_at) part_status_at from part_histories GROUP BY part_id) t1'))
      ->select('t1.id', 'ph.part_status', 't1.part_status_at');
}

這是其中的一部分(但仍然使用一些原始 SQL)，我就是想不通其余的

which is part way there (but still using some raw SQL), I just can't figure out the rest

推薦答案

您可以將查詢重寫為左連接以獲得相同的結果

You could rewrite your query as left join to get the same results

select a.* 
from part_histories a
left join part_histories b on a.part_id = b.part_id 
                            and a.created_at < b.created_at
where b.part_id is null

我猜你可以在你的范圍內輕松轉換上面的查詢，比如

and I guess you can transform easily above query in your scope something like

public function scopeWithLatestStatus($query)
{
    return $query->leftJoin('part_histories as b', function ($join) {
                $join->on('a.part_id', '=', 'b.part_id')
                     ->where('a.created_at', '<', 'b.created_at');
            })
        ->whereNull('b.part_id')
        ->from('part_histories as a')
        ->select('a.*');
}

Laravel Eloquent 選擇具有最大值的所有行created_at

Laravel - 獲取最后一個條目每種 UID 類型

Laravel Eloquent 按最近記錄分組

使用上述查詢作為 has 關系進行編輯，要獲取每個部分的最新歷史記錄，您可以定義一個 hasOne 關系，如

Edit using above query as has relation,To get the latest history for each part you can define a hasOne relation like

namespace AppModels;

use IlluminateDatabaseEloquentModel;
use IlluminateSupportFacadesDB;
class Part extends Model
{
    public function latest_history()
    {
        return $this->hasOne(AppModelsPartHistory::class, 'part_id')
            ->leftJoin('part_histories as p1', function ($join) {
                $join->on('part_histories.part_id', '=', 'p1.part_id')
                    ->whereRaw(DB::raw('part_histories.created_at < p1.created_at'));
            })->whereNull('p1.part_id')
            ->select('part_histories.*');
    }
}

然后要加載具有最新歷史記錄的零件，您可以將上面定義的映射加載為

And then to load parts with their latest history you could eager load above defined mapping as

$parts = Part::with('latest_history')->get();

您將擁有一份零件清單以及最新的歷史記錄

You will have a list of parts along with latest history as

Array
(
    [0] => Array
        (
            [id] => 1
            [title] => P1
            [latest_history] => Array
                (
                    [id] => 6
                    [created_at] => 2018-06-16 08:25:10
                    [status] =>  1
                    [part_id] => 1
                )

        )
....
)

這篇關于Laravel 獲取每個組的最新記錄的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！