laravel - 使用预加载将原始 SQL 查询转换为 Laravel Eloquent

Question

我在尝试将以下查询转换为 Eloquent 时遇到了很大的困难。

SELECT Sum(t.amount) AS amount, 
       m.name 
FROM   transactionsv2 t 
       JOIN channels c 
         ON t.entityid = c.uuid 
       JOIN merchants m 
         ON c.sender = m.uuid 
WHERE  t.paymenttype = 'DB' 
       AND t.status = 1 
       AND t.processing_time >= '2019-01-01' 
       AND t.processing_time < '2019-01-21' 
GROUP  BY m.name; 

这就是我所拥有的，但结果集不正确......

public function getTransactionsVolumeReport()
{
    $report = Transaction::select(DB::raw('sum(amount) as amount, entityId'))
        ->where('paymentType', '=', 'DB')
        ->where('status', 1)
        ->where('processing_time', '>=', '2019-01-01 00:00:00')
        ->where('processing_time', '<=', '2019-01-21 23:59:59')
        ->with(['channel' => function ($q) {
            $q->select('uuid', 'name', 'sender');
        }])
        ->with(['channel.merchant' => function ($q) {
            $q->select('uuid', 'name')
                ->groupBy('name');
        }])
        ->get();

    echo $report;
}

这些是 Laravel 调试栏显示的查询......

以下是我的雄辩关系...

Transaction Model

protected $with = ['channel', 'statusPayment'];

public function channel() {

    return $this->hasOne(Channel::class, 'uuid', 'entityId');
}

-----

Channel Model

protected $with = ['merchant', 'attachedMerchantAccount'];

public function merchant() {

    return $this->hasOne('App\Merchant', 'uuid', 'sender');
}

标签： laravellaravel-5eloquentlaravel-query-builder