PHP/Laravel:SELECT和JOIN两个表

Question

我有三个表，如下所示，并试图加入客户的费用和津贴表。我试过使用下面的代码，但它在费用和津贴方面返回null。我也尝试了内部JOIN，但没有成功。

我的表格如下：

customers(id, user_id, full_name)
expenses(id, customer_id, name, amount)
allowances(id, customer_id, name, quantity)

我尝试使用下面的代码，但没有成功。

$logs = Customer::query()
->select([
"customers.id",
"customers.full_name",
"expenses.name as expenses_name",
"expenses.amount as expenses_amount",
"allowances.name as allowance_name",
"allowances.quantity as allowance_quantity"
])
->join('expenses', function (JoinClause $join) {
$join->type = 'left outer';
$join->on('expenses.customer_id', 'customers.id');
})
->join('allowances', function (JoinClause $join) {
$join->type = 'left outer';
$join->on('allowances.customer_id', 'customers.id');
});
return $logs->toJson();

我希望输出为：

{
id:'',
full_name:'',
expenses:{{expenses_name, expenses_amount},{expenses_name, expenses_amount}},
allowances:{{allowance_name, allowance_quantity},{allowance_name, allowance_quantity}}
}

Answer 1

使用以下代码：

$logs = Customer::select([
"customers.id",
"customers.full_name",
"expenses.name as expenses_name",
"expenses.amount as expenses_amount",
"allowances.name as allowance_name",
"allowances.quantity as allowance_quantity"
])
->leftjoin('expenses', 'customers.id','=','expenses.customer_id')
->leftjoin( 'allowances','customers.id','=','allowances.customer_id')
->get();

Answer 2

您还可以使用雄辩的关系。

在客户模型中定义关系

public function expenses()
{
return $this->hasMany(Expense::class);
}
public function allowances()
{
return $this->hasMany(Allowance::class);
}

获得具有关系的客户

$logs = Customer::with(['expenses','allowances'])->get();