Laravel GroupBy数据库查询生成器不起作用 [英] Laravel groupBy Database query Builder not working

问题描述

我在使用laravel groupBy的查询中遇到此问题，它只是返回了groupBy错误.我已经阅读了有关此文档，但无法真正解决.我也读到了同样的问题，指出是由于postgreSQL，我需要在grouBy子句中包括所有列.我试过了，但仍然没有返回不同的值.请帮我解决一下这个.下面是我的代码.非常感谢.

I have this problem in a query using laravel groupBy, it simply return a groupBy error. I have read the documentation about this but can't really figure it out. I also read the same problem pointing that it is because of postgreSQL that I need to include all the columns in grouBy clause. I tried it but still it doesn't return the distinct values. Please help me with this. Below is my code. Thanks a lot.

控制器功能

 public function index(){
    $purchases = Purchase::groupBy('purchase_order_id')->get();
    return view('purchases/purchases_crud', ['allPurchases' => $purchases]);
}

要查询的表

错误

QueryException in Connection.php line 680:
SQLSTATE[42803]: Grouping error: 7 ERROR: column "purchases.id" must appear
in the GROUP BY clause or be used in an aggregate function
LINE 1: select * from "purchases" group by "purchase_order_id"
^ (SQL: select * from "purchases" group by "purchase_order_id")

推荐答案

在那里，您可以按"purchases.id"添加组，或者将选择范围限制为仅需要的操作.

There you have it add group by "purchases.id" or restrict the select to only the operates that are needed.

->select("purchase_order_id","purchases.id")

->groupBy("purchases.id") // add if possible

针对您的案例的Agreggates应该表示类似->select("sum(po_total)")

Agreggates for your case should mean something like ->select("sum(po_total)")

如果我们按ID分组，由于ID是唯一的，我们会得到所有结果，这是我的错.你想要这样的东西

If we group by id, we get all results as id is unique, my mistake. You want something like this

DB::table("purchases")->select("purchase_order_id", DB:raw("sum(po_total)"))->groupBy("purchase_order_id")->g‌​et();

经验法则是您使用Sum()选择一个字段，还是将其放在分组依据上

Rule of thumb is you either select a field with Sum() or have it on the group by

这篇关于Laravel GroupBy数据库查询生成器不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！