连接表的总金额不正确 [英] 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();

---

更新

我正在尝试使用Khalid的解决方案，以下是错误消息:

---

Update

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

1. -> mergeBindings($ CUT)

Symfony \ Component \ Debug \ Exception \ FatalThrowableError:类型错误:传递给Illuminate \ Database \ Query \ Builder :: mergeBindings()的参数1必须是Illuminate \ Database \ Query \ Builder的实例

Symfony\Component\Debug\Exception\FatalThrowableError: Type error: Argument 1 passed to Illuminate\Database\Query\Builder::mergeBindings() must be an instance of Illuminate\Database\Query\Builder

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

Symfony \ Component \ Debug \ Exception \ FatalThrowableError:类型错误:传递给Illuminate \ Database \ Query \ Builder :: mergeBindings()的参数1必须是Illuminate \ Database \ Query \ Builder的实例，给定数组

Symfony\Component\Debug\Exception\FatalThrowableError: Type error: Argument 1 passed to Illuminate\Database\Query\Builder::mergeBindings() must be an instance of Illuminate\Database\Query\Builder, array given

1. -> addBinding($ CUT)

ErrorException:Illuminate \ Database \ Eloquent \ Builder类的对象无法转换为字符串

ErrorException: Object of class Illuminate\Database\Eloquent\Builder could not be converted to string

推荐答案

由于左联接，您得到的总金额错误，每个用户的连接表可能有多于一行，这使得总金额大大高于原始行首先，要解决此问题，您需要在单个子句中计算这些总金额，然后将它们与main进行合并，例如

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

在laravel中执行上述操作非常复杂，例如为每个子子句获取sql和query builder对象，然后在主查询中使用它们，如下所示

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

这篇关于连接表的总金额不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！