Wrong SQL query for sorting with importField and addManyToOneRelation

Help me!
1

Feature(s) impacted

importField, addManyToOneRelation and admin panel filters

Observed behavior

I have a database schema with “Company” that have many “Reference” that have many “Scenario”

I would like to show in Scenario list of admin panel the company of each scenario and filter and sort by this column. I customized the agent with these few lines :

 agent.customizeCollection("Scenario", (collection) => {
        collection.importField("companyId", { path: "reference:companyId", readonly: true });

        collection.addManyToOneRelation("company", "Company", {
            foreignKey: "companyId",
            foreignKeyTarget: "id",
            
        });
    });

The column is displayed in the admin panel :
image

The column is sortable, but when I click on it, I have this error : Unknown column 'Scenario.companyId' in 'order clause'

The SQL query from debug log is :

SELECT `Scenario`.`name`, `Scenario`.`id`, `reference`.`name` AS `reference.name`, `reference`.`companyId` AS `reference.companyId`, `reference`.`id` AS `reference.id`, `reference->company`.`name` AS `reference.company.name`, `reference->company`.`id` AS `reference.company.id` FROM `Scenario` AS `Scenario` LEFT OUTER JOIN `Reference` AS `reference` ON `Scenario`.`referenceId` = `reference`.`id` LEFT OUTER JOIN `Company` AS `reference->company` ON `reference`.`companyId` = `reference->company`.`id` ORDER BY `Scenario`.`companyId` DESC LIMIT 0, 15;

It seems to consider the imported field as a actual Scenario table field and not to use the joined field

Expected behavior

The sort should work. I guess that the order by clause should be something like ORDER BY reference.company.name

Context

  • Project name: waro-app
  • Environment name: development
  • Agent technology: nodejs
  • Agent (forest package) name & version:
    • “@forestadmin/agent”: “1.41.7”,
    • “@forestadmin/datasource-sql”: “1.14.11”,
  • Database type: MySQL
2

Hello,
I can reproduce, I will more investigate on Monday.
Thank you for the report :pray: I will come back very soon.
Best regard,
Alban

1 Like
3

Hello,
A high priority bug ticket is created :pray:
Thank you for you report, I will ping you when the fix will be merged.

If you want to make the sort available, you can add the relation directly from the sequelize model, but currently with the addManyToOne, it’s not possible :confused:

Have a nice week,
Alban

1 Like