Laravel groupBy Database query Builder not working

Question

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.

Controller function

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

Table to query

Error

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

Calling the model without select is returning all columns *, but you can only use columns that are inside sum() or group by. See my edit ... — Cristo
– Cristo, Commented May 31, 2016 at 15:10

Cristo · Accepted Answer · 2016-05-31 15:06:39Z

2

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 for your case should mean something like ->select("sum(po_total)")

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

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

edited May 31, 2016 at 15:06

answered May 30, 2016 at 13:51

Cristo

7101 gold badge9 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Eli Over a year ago

The error is gone but still it returns 4 records. It should have return 2 rows right?

Eli Over a year ago

$purchases = Purchase::select("purchase_order_id","purchases.id")->groupBy("purchases.id")->get(); return view('purchases/purchases_crud', ['allPurchases' => $purchases]);

Eli Over a year ago

Call to undefined method Illuminate\Database\Query\Builder::g‌et()

Cristo Over a year ago

Try DB::table('purchase')->select... instead of calling the model. laravel.com/docs/5.1/queries#ordering-grouping-limit-and-offset

Eli Over a year ago

SQLSTATE[42703]: Undefined column: 7 ERROR: column "sum(po_total)" does not exist LINE 1: select "purchase_order_id", "sum(po_total)" from "purchases"... ^ (SQL: select "purchase_order_id", "sum(po_total)" from "purchases" group by "purchase_order_id")

|

Collectives™ on Stack Overflow

Laravel groupBy Database query Builder not working

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related