CODEWITHSUNDEEP
mysqllaraveleloquent
Dexx
Dexx

Reputation: 163

Sum query doubles when joining three tables in Laravel

I'm sending data to chartist.js using Laravel controller. When I try to join three tables together to sum the values, the output is essentially doubled when I join the third table.

The following query gets the data I need however doesn't sum them accurately

$chart_stats = Transactions::join('customers', 'customers.id', '=', 'transactions.customer_id')
                        ->join('orders', 'orders.customer_id', '=', 'transactions.customer_id')
                        ->distinct()
                        ->select('customers.region', 'transactions.deposit', 'transactions.purchase_total', 'transactions.finance_amount_paid', 'orders.gov_funding_amount')
                        ->whereIn('customers.region', $selected_regions)
                        ->where('transactions.created_at', '>', $from)
                        ->where('transactions.created_at', '<', $to)
                        ->select(
                            DB::raw('customers.region as region' ),            
                            DB::raw('sum(transactions.deposit) as deposits'),
                            DB::raw('sum(transactions.purchase_total) as sums'),
                            DB::raw('sum(transactions.finance_amount_paid) as finance_paid'),
                            DB::raw('sum(transactions.deposit+transactions.finance_amount_paid) as total_paid'),
                            DB::raw('sum(orders.gov_funding_amount) as funding')
                        )
                        ->groupBy('customers.region')
                        ->orderBy('sums', 'asc')
                        ->get();

This will output the following in an array:

#original: array:6 [▼
    "region" => "Region 1"
    "deposits" => "5025.40"
    "sums" => "52875.00"
    "finance_amount_paid" => "0.00"
    "total_paid" => "5025.40"
    "funding" => "9228.00"

It should be:

    #original: array:6 [▼
    "region" => "Region 1"
    "deposits" => "1706.00"
    "sums" => "21271.00"
    "finance_amount_paid" => "0.00"
    "total_paid" => "1706.40"
    "funding" => "3396.00"

Upvotes: 0

Views: 479

Answers (1)

Dimitri Mostrey
Dimitri Mostrey

Reputation: 2355

I was going to give this as a comment but it would be better as a answer. It's a proposition and I could be wrong. There are 2 possible answers.

Change these lines:

->where('transactions.created_at', '>', $from)
->where('transactions.created_at', '<', $to)

To either:

->whereBetween('transaction.created_at, $from, $to)

Or edit: type, arrays into arrays, corrected:

->where([['transactions.created_at', '>', $from], ['transactions.created_at', '<', $to]])

Methink it is possible, if both conditions are true, the row is selected twice. The distinct() may not do what it is intended to do. You could also try a closure:

->where(function ($query) {
      $query->where('transactions.created_at', '>', $from)
            ->where('transactions.created_at', '<', $to)
            ->distinct();
       })

Upvotes: 1

Related Questions