Chart error : column must appear in the GROUP BY clause

Help me!
1

Feature(s) impacted

Dashboard Charts

Observed behavior

I created a chart on the dashboard,
I provided it a sql query,
And i get this error server side :

column "company.id" must appear in the GROUP BY clause or be used in an aggregate function

The “company” table is not used in my query so i don’t understand the cause of this error, i think it may be related to my tables relationship declaration.
An other chart that used to work fine in the past is not working either, it returns the same error, and it’s query is not related to the “company” table either.

Context

  • Project name: ma-domiciliation
  • Team name: Admin et dev
  • Environment name: Staging-dev
  • Agent (forest package) name & version: “forest-express-sequelize”: “~9.3.10”,
  • Database type: postgres
  • Recent changes made on your end if any: i think it’s not related to a recent change, the existing chart that is not working either had this problem already, but i had never investigated this one because we don’t really use it
2

Hello @kmcb

The first thing to check is from where does the “company” collection comes from:

  • Is there any configuration/code in the chart related to this collection ?
  • Does the chart use custom fields, or relationships ?
  • The chart may need you to explicitly include the related collections and associated GROUP BY clauses to your query. If you identified a relation between the collection “company” and your chart (even via a smart field or relationship), try to add this table to the “FROM” and “GROUP BY” clauses of your SQL query.

If the points above did not help, could you share the whole chart code and configuration, and maybe also the SQL query ?

3

Hello @Enki thanks for your answer,

Here is the configuration of the previous chart that has the same error, it may be better to debug with this one because it’s a simple query, that use no relations and no smart fields :

image
image1266×1094 61.9 KB

I think the cause is related to the fact that on the User (custom name : “Client”) model we use a beforeFind hook to add some included models to the base query, because on this collection we have many smart fields, and they use data from 8 related tables, so it’s the best way i found to have this data available for my smart fields and avoid doing tons of requests for each user displayed in the list.
Here is what we do in the beforeFind hook concerning the “company” table :

          if (!options.include.find(m => m.as === 'company')) {
            options.include.push({
              model: sequelize.models.Company,
              as: 'company',
              attributes: null,
            })
          }

This successfully gives me the data of this related table directly available in the get function of my smart fields.
I’m afraid the problem is related to this, do you know how i can fix this ?
If you know a better way to do this “pre-loading” it would be fine too.

4

Yes, your intuition is good : since you have a reference to the company table in your query, SQL expect it to be added to the GOUP BY clause.
The agent should handle simple charts automatically, maybe it is not the case because of the relation coming from a beforeFind hook. I will investigate on this subject.

As a workaround, you may manually define a SQL query.

5

Ok thanks for your answers, that seems to be true indeed concerning this chart
Concerning the other query which was already in SQL, in the end i had to reformulate it anyway because the WITH clause is not supported in the SQL queries for charts, and after reformulating it worked fine.