Laravel Group By 关系列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48563912/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel Group By relationship column
提问by CairoCoder
I have Invoice_Detailmodel which handles all products and it's quantities, this model table invoice_detailshas item_idand qtycolumns.
我有Invoice_Detail型号处理所有的产品，它的数量，这种模式表invoice_details具有item_id和qty列。
The Invoice_Detailhas a relation to Itemsmodel which holds all item's data there in its itemstable, which has item_id, name, category_id.
该Invoice_Detail有一个关系Items模型，有拥有其所有项目的数据items表，其中有item_id，name，category_id。
The Itemmodel also has a relation to Categorymodel which has all categories data in its categoriestable.
该Item模型还与Category模型有关系，该模型在其categories表中包含所有类别数据。
Question: I want to select top five categories from Invoice_Detail, how?
问：我想从 中选择前五个类别Invoice_Detail，如何？
Here's what I did:
这是我所做的：
$topCategories = InvoiceDetail::selectRaw('SUM(qty) as qty')
->with(['item.category' => function($query){
$query->groupBy('id');
}])
->orderBy('qty', 'DESC')
->take(5)->get();
But didn't work !!
但是没有用！！
[{"qty":"11043","item":null}]
回答by Yury
Category::select('categories.*',\DB::raw('sum("invoice_details"."qty") as "qty"'))
->leftJoin('items', 'items.category_id', '=', 'categories.id')
->leftJoin('invoice_details', 'invoice_details.item_id', '=', 'items.id')
->groupBy('categories.id')
->orderBy('qty','DESC')
->limit(5)
->get();
This will return you collection of top categories. Tested on laravel 5.5 and PostgreSQL.
这将返回顶级类别的集合。在 laravel 5.5 和 PostgreSQL 上测试。
UPD:To solve this without joins you can add to Categories model this:
UPD：要在没有连接的情况下解决此问题，您可以将以下内容添加到类别模型中：
public function invoiceDetails()
{
return $this->hasManyThrough(Invoice_Detail::class, Item::class);
}
And to select top 5 categories:
并选择前 5 个类别：
$top = Category::select()->with('invoiceDetails')
->get()->sortByDesc(function($item){
$item->invoiceDetails->sum('qty');
})->top(5);
But first solution with joins will work faster.
但是第一个带有连接的解决方案会更快地工作。
