在laravel中使用wherehas时从子查询中选择sum

6ss1mwsb 于 2021-06-19 发布在 Mysql

关注(0)|答案(1)|浏览(443)

我有两张table， customers 以及 customer_invoices ，我想获得所有客户的发票上都有一个条件，并选择特定的列（customers.id、customers.last\u name和 total_price 对于每个客户的发票），我有以下查询：

$result = Customer::whereHas('customerInvoices', function(Builder $q) {
            $q->where('customer_invoices.status', 1);
        })->select([
            'customers.id',
            'customers.last_name',
            \DB::raw('SUM(customer_invoices.total_price) as sum')
        ])->get();
``` `customerInvoices` 关系是：

public function customerInvoices() {

return $this->hasMany(CustomerInvoice::class);

}

我想使用子查询而不是联接，所以在这里我不能选择这个 `\DB::raw('SUM(customer_invoices.total_price) as sum')` ，否则我会得到这个错误：

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'customer_invoices.total_price' in 'field list' (SQL: select customers.id, customers.last_name, SUM(customer_invoices.total_price) as sum from customers where exists (select * from customer_invoices where customers.id = customer_invoices.customer_id and customer_invoices.status = 1))"

如果不使用连接，如何实现这一点？

mysql subquery laravel laravel-5.4

来源：https://stackoverflow.com/questions/52944636/select-sum-from-subquery-while-using-wherehas-in-laravel

1条答案

按热度按时间

xzv2uavs1#

你可以用 withCount() 从相关模型中求和

$result = Customer::select([
            'customers.id',
            'customers.last_name'
        ])->withCount([
            'customerInvoices as invoice_sum' => function($query) {
                $query->select(DB::raw('SUM(total_price)'));
            }
        ])->whereHas('customerInvoices', function(Builder $q) {
            $q->where('customer_invoices.status', 1);
        })->get();

另一种求和的方法，可以定义 hasOne() 客户模型中的关系

public function invoice_sum()
{
    return $this->hasOne(CustomerInvoice::class)
        ->select('customer_id',
            DB::raw('sum(total_price)')
        )->groupBy('customer_id');
}

在查询生成器中

$result = Customer::select([
            'customers.id',
            'customers.last_name',
        ])->with('invoice_sum')
          ->whereHas('customerInvoices', function(Builder $q) {
            $q->where('customer_invoices.status', 1);
        })->get();

根据eloquent:withcount（）重写get（）上的$columns select() 方法然后使用 with() 功能

赞(0）回复(0）举报 2021-06-20

我来回答

在laravel中使用wherehas时从子查询中选择sum

1条答案

相关问题

热门标签

最新问答