从关系中的外键检索最新数据

Question

我想问一下，我可以检索关系中的最后一个外键数据吗？

在本例中，我想检索上次的用户事务数据。

例如，在 3 个月前到现在之间的交易表中，有一些交易数据如下。

{
"current_page": 1,
"data": [
{
"id": 8,
"user_id": 1,
"type": "sell",
"created_at": "2019-12-11 11:55:31",
"updated_at": "2019-12-11 11:55:31",
"tax": null,
"invoice_number": null,
"name": "Fredy"
},
{
"id": 7,
"user_id": 1,
"type": "buy",
"created_at": "2019-11-20 17:14:55",
"updated_at": "2019-11-20 17:14:55",
"tax": null,
"tax_percent": "0.90",
"invoice_number": null,
"name": "Fredy"
},
{
"id": 6,
"user_id": 8,
"type": "buy",
"created_at": "2019-11-20 16:28:33",
"updated_at": "2019-11-20 16:28:33",
"tax": null,
"name": "Tommy"
},
{
"id": 5,
"user_id": 2,
"type": "buy",
"created_at": "2019-11-20 15:47:57",
"updated_at": "2019-11-20 15:47:57",
"tax": null,
"name": "Sarah"
},
{
"id": 4,
"user_id": 3,
"type": "buy",
"created_at": "2019-11-20 15:47:31",
"updated_at": "2019-11-20 15:47:31",
"tax": null,
"name": "John Doe"
},
],
}

这是我制作的示例代码

public function index()
{
$start = Carbon::now()->subMonths(3)->format('Y-m-d') . ' 00:00:00';
$end = Carbon::now()->format('Y-m-d') . ' 23:59:59';
return $query = DB::table('transaction')
->join('users', 'users.id', '=', 'gold_transaction.user_id')
->select('transaction.id', 'user_id', 'users.*')
->whereBetween('transaction.created_at', [$start, $end])
->whereIn('transaction.id', [DB::raw("SELECT MAX(transaction.id) FROM transaction GROUP BY user_id")])->get();
}

如果我只想为所有用户显示自上次交易起 7 天未进行交易的用户，该怎么办？

谢谢。

Answer 1

然后你可以像下面这样做，

获取用户的交易

$transaction = User::with(['transactions' => function ($q) use($start, $end){
$q->whereBetween('transactions.created_at', [$start, $end]);
])->find($user_id);

获取所有用户的交易

$transaction = User::with(['transactions' => function ($q) use($start, $end){
$q->whereBetween('transactions.created_at', [$start, $end]);
])->get();

假设您的用户模型中有事务关系，如下所示

public function transactions() {
return $this->hasMany('AppTransaction', 'user_id');
}