0

I have a Laravel query that works when our DB is using our MySQL DB, but when I switch to our new PostgreSQL DB it stops working.

I am using Laravel's group by function as below:

$this->crud->groupBy('leads.id')

The whole query translated to mysql is below:

select count(*) as aggregate 
from (select leads.* from leads 
where leads.club_id in ('TR71') and 
leads.deleted_at is null GROUP BY leads.id) as aggregate_table;

The error that is now giving is the following:

"SQLSTATE[42803]: Grouping error: 7 ERROR:  column \"leads.name\" must appear in the GROUP BY clause or be used in an aggregate function (SQL: select count(*) as aggregate from (select \"leads\".* from \"leads\" where \"leads\".\"club_id\" in (TR71) and \"leads\".\"deleted_at\" is null group by \"leads\".\"id\") as \"aggregate_table\")"

It pretty much is asking me to list each column from the leads.* part of the query into the group by clause. So far I have tried listing each column but it is not efficient or good practice as we could always end up in the situation where we add more columns to the table

SJW525
  • 51
  • 1
  • 9
  • 3
    The fact that the query "works when our DB is using our MySQL DB" doesa not mean that the query is correct. It works only due to SQL language extension in MySQL (see ONLY_FULL_GROUP_BY). PS. Looking more deep one can easily see that the query is illogical (illogically complicated and complexed). – Akina Apr 18 '21 at 19:17
  • https://stackoverflow.com/questions/19601948/must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function check out this it may help Remember ,MySQL is running on top of SQL with new features if query is running in MySQL doesn't meant it will work in postgrs sql – dvijparekh Apr 18 '21 at 19:19
  • @dvijparekh what would I change groupBy to? – SJW525 Apr 18 '21 at 21:33
  • @Akina It is what Laravel translates it to. I did not write it to be that complex – SJW525 Apr 18 '21 at 21:34
  • Can you add the database structures for the leads table? – Tobias P. Apr 22 '21 at 06:12

1 Answers1

1

MySQL have an inconsisten behaviour with aggregate calculus.

The SQL standard require that all columns that are in the SELECT clause of a SELECT statement and are not involved in an aggregate function must be specified in the GROUP BY clause. This is probably the case of your subquery that limits the GROUP BY to only the leads.id column.

Excepts MySQL in which it is clearly a bug and returns false data, there is no other RDBMS that accepts such an inconsistent query syntax.... In this case, MySQL returns randomized data that you believe to be thruth !

SQLpro
  • 3,994
  • 1
  • 6
  • 14
  • of course. No other RDBMS will accept it: PostGreSQL, SQL Server, Oracle, Sybase ASA/ASE, IBM DB2... MySQL is the most bugged DBMS ! – SQLpro Apr 20 '21 at 06:37