Laravel 5.4雄辩的SUM和ORDER BY数据透视表列 [英] Laravel 5.4 Eloquent SUM and ORDER BY pivot table column

问题描述

我需要一个雄辩的查询来检索数据库中购买最多的产品.这些表是产品， products_purchases ， purchase .产品和购买都具有多对多关系.

I need an Eloquent query that retrieves the most purchased products in my database. The tables are products, products_purchases, purchases. Products and purchases both have a many-to-many relationship.

数据透视表 products_purchases 有一个名为 quantity 的数据透视表字段，其中包含购买时购买的产品总数.

The pivot table products_purchases has a pivot field called quantity, which has the total quantity of a product bought on a purchase.

我试图获取这样的记录:

I tried to get the records like this:

$data['most_purchased'] = Products::whereHas('purchases', function($query){
        $query->selectRaw('SUM(products_purchases.quantity) AS qty')->orderBy('qty', 'desc')->groupBy('product_id');
})->get();

但是它没有给出准确的值，正确的方法是什么?

But it doesn't give accurate values, what is the right way to do this?

推荐答案

此代码有效:

Products::join('products_purchases', 'product.id','=','products_purchases.product_id')
           ->selectRaw('product.*, SUM(products_purchases.quantity) AS qty')
           ->groupBy('product.id')
           ->orderBy('qty', 'desc')
           ->get();

但是，还有另一种更清洁"的方法可以在不使用 join 的情况下做到这一点吗?

But, is there another "cleaner" way to do this without using join?

这篇关于Laravel 5.4雄辩的SUM和ORDER BY数据透视表列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！