Laravel使用查询生成器作为对象进行连接

Question

在我的控制器中，我得到了一个带有连接的列表，如下所示

[
{
// transaction table
"id": 1,
"remark": "User Creation",
"amount": 500000,
"transaction_id": 158936526598058974,
"user_id": 14,
"role_id": 2,
"beneficiary_id": 1,
"parent_id": 0,
"created_by": 1,
"type": 1,
// user table ( join with beneficiary_id)
"username": "super_admin",
"name": "test1",
},
{
//Same
}
]

我想让它像雄辩的hasOne()

[
{
// transaction table
"id": 1,
"remark": "User Creation",
"amount": 500000,
"transaction_id": 158936526598058974,
"user": {  // join users table using user_id
"username": "admin",
"name": "test2",
},
"role_id": 2,
"beneficiary": {   //join with users table using be beneficiary_id
"username": "super_admin",
"name": "test 1",
},
"parent_id": 0,
"created_by": 1,
"type": 1,
},
{
//Same
}
]

问题是我知道如何雄辩地做到这一点。而且我不知道这是否可能，并且想使用查询构建器来做到这一点，因为我没有模型。

并且连接将位于beneficiary_id和user_id的相同(用户(表中。我想把它做成对象

我的控制器代码在这里：

$user = User::where('id', $id)->first();
$tablename = Roles::where('id', $user->role)->first();
$name = str_replace(' ', '_', trim($tablename->name));
$transaction = DB::table($name.'_user_transactions')
->where('user_id', $id)
->join('users', 'users.id' , '=' , 'beneficiary_id')
->get();

需要帮助 谢谢！

Answer 1

您需要在Transaction模型中定义 2 个关系并急切加载它们：

class Transaction extends Model
{
protected $with = ['user', 'beneficiary'];
public function user()
{
return $this->hasOne(User::class);
}
public function beneficiary()
{
return $this->hasOne(User::class, 'beneficiary_id');
}
}

然后，您需要调整序列化时可见的字段。添加到类Transaction：

$visible = ['id', 'remark', ..., 'user', 'beneficiary'];

对于User，您只需使 2 个字段可见username和name.您可以通过User模型中定义$visible来实现。或者在序列化事务之前动态：

$transactions = Transaction::all(); //<- here should be your logic for fetching transactions
$transactions->each(function(Transaction $transaction) {
$transaction->user->setVisible(['username', 'name']);
$transaction->beneficiary->setVisible(['username', 'name']);
});

然后，您只需要序列化事务：

$transactions->toArray();

或

$transactions->toJson();