Eloquent, relationships and foreach loops

You should be able to use joins instead, something like:

DB::table('companies')
    ->where('companies.id', user()->companies()->first()->id)
    ->leftJoin('payments', 'companies.id', '=', 'payments.landlord_id')
    ->leftJoin('rents', function ($join) {
        $join->on('companies.id', '=', 'rents.company_id')->where('status', '!=', 'unpaid')
    })->get();

You would need to probably alias columns to line up column names from rents with and payments tables, but otherwise it will work.

Have a look at the Sub joins part of https://laravel.com/docs/5.8/queries#joins to see how you might select the specific columns from the joined tables - you could selectRaw() to alias the columns that need aliasing

That query is showing:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: select * from `companies` left join `payments` on `companies`.`id` = `payments`.`landlord_id` left join `rents` on `companies`.`id` = `rents`.`company_id` and `status` != unpaid where `id` = 59)

Updated above:

//
->where('companies.id', user()->companies()->first()->id)
//

Of course, my bad!!

No worries!

That query is simply an example jumping off point for you; I expect you will need the subJoin approach to alias your payments.amount differently to the rent.amount since they map to income and expenditure columns on your HTML table.

Yeah, I can see that now and I think I should be able to figure the rest out, thanks buddy. Failing that I may be back later :)

There's 8 records showing and they are duplicates of each rent item, so each showing twice, there should be 6 items in total (should be 7 but we exclude the unpaid one) I guess i need a groupBy() of some sort just not sure as I'm a little new to this join/sub query stuff.

Also this is kinda like a Balance sheet, so income should be rents and payments will be expenditure

Anyone else willing to chip in?

Can you show us you current Eloquent/Query Builder query?

$incomes = DB::table('companies')
                        ->where('companies.id', user()->companies()->first()->id)
                        ->leftJoin('payments', 'companies.id', '=', 'payments.company_id')
                        ->leftJoin('rents', function ($join) {
                            $join->on('companies.id', '=', 'rents.landlord_id')->where('status', '!=', 'unpaid');
                        })
                        ->leftJoin('properties', 'rents.property_id', '=', 'properties.id')
                        ->get();

Are you wanting to aggregate all income and expenditure for a property/landlord on a single row?

Sorry, I'm wanting to show all income/rents that are paid so there should be 5 rows there, then for expenditure/payments, there's 2 rows so in total on the html table/view I should see 7 rows.

@tykus - presuming you saw my reply, I'm still looking to get this sorted with a solution of sort, your help greatly appreciated.