PHP/Laravel: SELECT and JOIN two tables-CodePudding

I have three tables as shown below and trying to join the expenses and allowance table on customers. I have tried using the code below but it returns null on expenses and allowances. I tried inner JOIN as well but didn't work.

I have tables as below:

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

And i tried using the code below but doesn't work.

$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();

I want output as:

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

CodePudding user response：

You can also use eloquent relationship.

define relations in Customer Model

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

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

get Customers with relationship

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

CodePudding user response：

Use the following Code:

$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();