在Laravel中使用whereHas时从子查询中选择SUM [英] Select SUM from subquery while using whereHas in Laravel

问题描述

我有2个表，分别是 customers 和 customer_invoices ，我想让所有具有发票条件的客户，并选择特定的列(customers.id，customer.last_name，以及每个客户发票的 total_price 之和)，我得到以下查询:

I have 2 tables, customers and customer_invoices, and I want to get all the customers with a condition on their invoices, and select specific columns (customers.id, customers.last_name, and the sum of the total_price of the invoices for each customer), I have this query :

$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)作为总和')，否则我会得到这个当然是错误的:

I want to use subqueries instead of joins, so here I can't select this \DB::raw('SUM(customer_invoices.total_price) as sum'), or else I get this error of course :

"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))"

如何在不使用联接的情况下实现这一目标?

How can I achieve this without using joins ?

推荐答案

您可以使用

You can use withCount() to get sum from related model as

$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()关系，例如

Another approach to get sum, you can define a hasOne() relation in your Customer model like

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

根据雄辩:withCount()会覆盖get()上的$列问题首先放置 select()方法，然后使用 with()函数

As per Eloquent : withCount() overrides the $columns on get() issue first put select() mehtod and then use with() function

这篇关于在Laravel中使用whereHas时从子查询中选择SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！