Mysql(GroupBy) alternative for Postgresql?

Question

I am using laravel with mysql..

I have query something like this..

$mix = Ergebnisse::where(function($q) use($result) {               
    foreach ($result as $item) {
         $q->orWhere(function($q) use($item) {
             $q->where('temperatur', $item->temperatur)
               ->where('zeit', $item->zeit);
        });
    }
})->groupBy('katogorie_id')->get();

on my localhost it is working fine as i am using mysql there. but i deployed the project on Heroku with Postgresql.

I got an error..

SQLSTATE[42803]: Grouping error: 7 ERROR: column "ergebnisse.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select * from "ergebnisse" where (("temperatur" = $1 and "ze...
^ (SQL: select * from "ergebnisse" where (("temperatur" = 85 and "zeit" = 35) or ("temperatur" = 85 and "zeit" = 30)) group by "katogorie_id")

after further searching i came to know that GroupBy is not compatible with postgre. is there any other way of doing that? thanks

`GroupBy is not compatible with postgre` interesting why? because pure SQLs `GROUP BY` is nicely compatible with postgresql. — Oto Shavadze, Mar 30 '17 at 12:43
as @Lukasz Kaminski commented actually its bit different from that of Mysql.. — Hsn, Mar 30 '17 at 13:04
Which row do you want to get for a group of `katogorie_id`? (MySQL's answer: *err, you didn't specify that, so I'll just put random stuff there* -- PostgreSQL's answer: *you didn't specify that, so that's an error. correct your error and we can talk again*). — pozs, Mar 30 '17 at 13:22
Possible duplicate of [PostgreSQL GROUP BY different from MySQL?](http://stackoverflow.com/questions/1769361/postgresql-group-by-different-from-mysql) — pozs, Mar 30 '17 at 13:22
I dont want the duplicate katogories so thats y i m only putting katogorie:id in the groupby.Now i put the id of my table with the katogorie id like this.. ->GroupB('id','katogorie')..but it is showing multiple katogories — Hsn, Mar 30 '17 at 13:29
@pozs thanks for your comment... it is solved by ->distinct()->get(['katogorie_id']); — Hsn, Mar 30 '17 at 13:37
@HassanHaroon if `categorie_id` is enough for you than it's a perfectly valid solution. the linked answer contains the translation of your original query too. For more general solutions, browse the [tag:greatest-n-per-group] tag. — pozs, Mar 30 '17 at 13:43

score 1 · Answer 1 · answered Mar 30 '17 at 12:46

1

You SELECT * but GROUP BY katogorie_id. In short, you need to GROUP BY all columns that you are SELECTing. This is in contrast to MySQL old behavior accepting non-standard GROUP BY usage.

So either add more columns to your group by or select less columns (I doubt you need SELECT *).

answered Mar 30 '17 at 12:46

Łukasz Kamiński

5,630
1
19
32

hi thanks for your comment. actually i need to only group it by katogorie_id.. can you give me some other solution for that? – Hsn Mar 30 '17 at 13:03
I think that `SELECT DISTINCT ON (katogorie_id) *` will give the same-ish result as MySQL GROUP BY. I'm not too familiar with how MySQL old GROUP BY _exactly_ decides what rows to return. – Łukasz Kamiński Mar 30 '17 at 13:23
->distinct()->get(['katogorie_id']); this thing solved my problem..thanks – Hsn Mar 30 '17 at 13:35

score 0 · Accepted Answer · answered Mar 30 '17 at 13:37

Thankyou all of you who helped me.i solved it by this

 ->distinct()->get(['katogorie_id']);

full query-

$result=Ergebnisse::where('name_id', $nam)->where('geometrie_id', $geo)->orWhere('name_id', $nam2)->orWhere('geometrie_id', $geo2)->get();


          $mix = Ergebnisse::where(function($q) use($result) {               
        foreach ($result as $item) {
             $q->orWhere(function($q) use($item) {
                 $q->where('temperatur', $item->temperatur)
                   ->where('zeit', $item->zeit);
            });
        }
    })->distinct()->get(['katogorie_id']);

Mysql(GroupBy) alternative for Postgresql?

2 Answers2