The sum amount from join tables is incorrect

I need to get the sum of the admin added into transactions. The transactions is splitted into multiple tables, and now i want to get a total from both tables separately. End up the results i’m getting is ridiculous high. I not sure is it my query multiplied the value somewhere.

User::select('user.name as name',
                DB::raw('sum(CASE WHEN current.amount > 0 THEN current.amount END) as current_positive'),
                DB::raw('sum(CASE WHEN current.amount < 0 THEN current.amount END) as current_negative'),
                DB::raw('sum(CASE WHEN cash.amount > 0 THEN cash.amount END) as cash_positive'),
                DB::raw('sum(CASE WHEN cash.amount < 0 THEN cash.amount END) as cash_negative')
                )->leftjoin('current_transaction as current', 'current.created_by', '=', 'user.id')
                ->leftjoin('cash_transaction as cash', 'cash.created_by', '=', 'user.id')
                ->whereBetween('current.created_at', [$start_date->format('Y-m-d'), $end_date->format('Y-m-d')])
                ->whereBetween('cash.created_at', [$start_date->format('Y-m-d'), $end_date->format('Y-m-d')])
                ->where('user.type', 3)
                ->groupBy('user.name')
                ->get();

Update

I’m trying with the solution from M Khalid, and the following is the error message:

  1. ->mergeBindings($CUT)

SymfonyComponentDebugExceptionFatalThrowableError: Type error: Argument 1 passed to IlluminateDatabaseQueryBuilder::mergeBindings() must be an instance of IlluminateDatabaseQueryBuilder

  1. ->mergeBindings($CUT->getBindings())

SymfonyComponentDebugExceptionFatalThrowableError: Type error: Argument 1 passed to IlluminateDatabaseQueryBuilder::mergeBindings() must be an instance of IlluminateDatabaseQueryBuilder, array given

  1. ->addBinding($CUT)

ErrorException: Object of class IlluminateDatabaseEloquentBuilder could not be converted to string

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You are getting wrong sum amount because of the left joins and each joined table may have more than one rows per user which makes the sum amount quite higher than the original one, to sort out this issue you need to calculation these sum amounts in indvidual sub clauses and then join these with main somewhat like

select users.name as name,cu.*,ca.*
from users
left join (
    select created_by,
    sum(CASE WHEN amount > 0 THEN amount END) as current_positive,
    sum(CASE WHEN amount < 0 THEN amount END) as current_negative
    from current_transaction
    where created_at between :start_date and :end_date
    group by created_by
) cu on users.id = cu.created_by
left join (
    select created_by,
    sum(CASE WHEN amount > 0 THEN amount END) as cash_positive,
    sum(CASE WHEN amount < 0 THEN amount END) as cash_negative
    from cash_transaction
    where created_at between :start_date and :end_date
    group by created_by
) ca on users.id = ca.created_by
where users.type = 3

To do above in laravel is quite complex like for each sub clause get the sql and query builder object and then use them in main query somewhat like below
// For current_transaction
$CUT = CurrentTransaction::query()
    ->select('created_by',
              DB::raw('sum(CASE WHEN amount > 0 THEN amount END) as current_positive'),
              DB::raw('sum(CASE WHEN amount < 0 THEN amount END) as current_negative')
            )
    ->from('current_transaction')
    ->whereBetween('created_at', [$start_date->format('Y-m-d'), $end_date->format('Y-m-d')])
    ->groupBy('created_by');
$CUTSql = $CUT->toSql();

// For cash_transaction
$CAT = CashTransaction::query()
    ->select('created_by',
              DB::raw('sum(CASE WHEN amount > 0 THEN amount END) as cash_positive'),
              DB::raw('sum(CASE WHEN amount < 0 THEN amount END) as cash_negative')
            )
    ->from('cash_transaction')
    ->whereBetween('created_at', [$start_date->format('Y-m-d'), $end_date->format('Y-m-d')])
    ->groupBy('created_by');
$CATSql = $CAT->toSql();

// Main query
User::select('user.name as name','cu.*','ca.*')
    ->leftjoin(DB::raw('(' . $CUTSql. ') AS cu'),function($join) use ($CUT) {
            $join->on('user.id', '=', 'cu.created_by');
            /* ->addBinding($CUT->getBindings());*/
    })
    ->leftjoin(DB::raw('(' . $CATSql. ') AS ca'),function($join) use ($CAT) {
            $join->on('user.id', '=', 'ca.created_by');
            /* ->addBinding($CAT->getBindings());  */
    })
    ->where('user.type', 3)
    ->mergeBindings($CUT) /* or try with ->mergeBindings($CUT->getBindings()) */
    ->mergeBindings($CAT) /* or try with ->mergeBindings($CAT->getBindings()) */
    ->get();


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x