左外连接，来自表的 laravel 组

Question

如何左联接表已经分组，在我的例子中，表 A 有数据帐号，表 B 是事务，我必须在加入表 A 之前计算并按帐号分组，如果在 sql natife 中如下所示

select name.account_no

,amount

from

ci_account_name name left join (

    select account_no,currency_id,sum(amount) as amount from 

    ci_account_transaction

    where status <> 'X' and store_id = 62242

    group by account_no,currency_id

) as trans

on name.account_no = trans.account_no

该编码有效，但是如何在 laravel 雄辩地实现我已经尝试了下面的代码但是有错误

public function reportShowZerro($data){

        $return = $this->accountNameModel->select (['ci_account_name.*'

                            ,'amount'

                        ])

                        ->leftJoin("

                            $this->model->select(['account_no','currency_id',\DB::raw('SUM(amount) amount')

                            ])

                            ->where('store_id',62242)

                            ->where('status','<>','X')

                            ->where('year',$data['year'])

                            ->where('month',$data['month'])

                            ->groupBy('account_no','currency_id')

                            ) as trans",'ci_account_name.account_no','=','trans.account_no'

                        ->whereIn('ci_account_name.store_id',[0,62242)

                        ->get();

        return $return;             

    }

Answer 1

public function reportWithModel($data){&nbsp; &nbsp; &nbsp; &nbsp; return $this->accountNameModel->select([&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'ci_account_name.*',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'trans.store_id as trans_store_id','currency_id','amount'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ])->leftJoin(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; \DB::raw('&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (select account_no,currency_id,store_id,sum(amount) as amount from&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ci_account_transaction&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; where status <> "X" and store_id = '.store()->id.'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; and year = '.$data["year"].' and month = '.$data["month"].'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; group by account_no,currency_id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) as trans&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'trans.account_no',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '=',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'ci_account_name.account_no'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->whereIn('ci_account_name.store_id',[0,store()->id])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->orderBy('ci_account_name.account_no')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->get();&nbsp; &nbsp; }