I get the error screen on the corresponding table " The request to your server either failed or returned invalid data.".
Expected behavior
I would like to see the table’s data.
Failure Logs
2024-07-02 13:32:20.707 UTC [202] ERROR: operator does not exist: "CropUsageType" = text at character 864
2024-07-02 13:32:20.707 UTC [202] HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
2024-07-02 13:32:20.707 UTC [202] STATEMENT: SELECT "CropUsage"."displayedName", "CropUsage"."id", "CropUsage"."weightUnit", "Crop"."name" AS "Crop.name", "CropUsage_AMG_through_cropAmgName"."id" AS "CropUsage_AMG_through_cropAmgName.id", "CropUsage_AMG_through_type"."id" AS "CropUsage_AMG_through_type.id", "CropUsage_GC_through_cropGcName"."id" AS "CropUsage_GC_through_cropGcName.id", "CropUsage_GC_through_type"."id" AS "CropUsage_GC_through_type.id", "Crop_AMG"."name" AS "Crop_AMG.name", "Crop_GC"."name" AS "Crop_GC.name" FROM "public"."CropUsage" AS "CropUsage" LEFT OUTER JOIN "public"."Crop" AS "Crop" ON "CropUsage"."cropName" = "Crop"."name" LEFT OUTER JOIN "public"."CropUsage_AMG" AS "CropUsage_AMG_through_cropAmgName" ON "CropUsage"."cropAmgName" = "CropUsage_AMG_through_cropAmgName"."cropName" LEFT OUTER JOIN "public"."CropUsage_AMG" AS "CropUsage_AMG_through_type" ON "CropUsage"."type" = "CropUsage_AMG_through_type"."cropName" LEFT OUTER JOIN "public"."CropUsage_GC" AS "CropUsage_GC_through_cropGcName" ON "CropUsage"."cropGcName" = "CropUsage_GC_through_cropGcName"."cropName" LEFT OUTER JOIN "public"."CropUsage_GC" AS "CropUsage_GC_through_type" ON "CropUsage"."type" = "CropUsage_GC_through_type"."cropName" LEFT OUTER JOIN "public"."Crop_AMG" AS "Crop_AMG" ON "CropUsage"."cropAmgName" = "Crop_AMG"."name" LEFT OUTER JOIN "public"."Crop_GC" AS "Crop_GC" ON "CropUsage"."cropGcName" = "Crop_GC"."name" ORDER BY "CropUsage"."id" DESC LIMIT 15 OFFSET 0;
Context
There is a composite primary key with an enum CropUsageType and a string on the table when selecting (see above), it compares the enum with the string thus fails.
Project name: carbon-monitoring-admin
Team name: ReSoil
Environment name: Development
Agent technology: nodejs
Agent (forest package) name & version: @forestadmin/agent (1.40.1)
I have tried to recreate the scenario that you are refering to but failed. While trying to create a similar postgre schema, I got the following error:
SQL Error [42804]: ERROR: foreign key constraint cannot be implemented Detail: Key columns are of incompatible types
Which I guess is expected. How did you manage to create such a postgre schema ? Could you please share a simple extract of your database schema highlighting the issue, with only the concerned columns and their types and keys (primary and foreign) declarations ?
One way that we could probably work around your issue is to replace the native relation by “emulating” it with one declared in code from within the agent:
I can probably give you a snippet example for how that would work if you are able to provide a reproducible pg schema
Hello,
Thanks for your quick reply.
I’m creating the tables and foreign/primary keys as follow :
DROP TYPE IF EXISTS "EnumType" CASCADE;
CREATE TYPE "EnumType" AS ENUM ('TYPEA', 'TYPEB', 'TYPEC');
DROP TABLE IF EXISTS "TableA";
DROP TABLE IF EXISTS "TableB";
CREATE TABLE IF NOT EXISTS "TableA" (
"type" "EnumType" NOT NULL,
"fieldA" TEXT NOT NULL,
"fieldB" TEXT NOT NULL,
"fieldC" TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS "TableB" (
"type" "EnumType" NOT NULL,
"fieldA" TEXT NOT NULL,
"fieldB" TEXT NOT NULL,
"fieldC" TEXT NOT NULL
);
ALTER TABLE "TableB" DROP CONSTRAINT IF EXISTS "B_pkey",
ADD CONSTRAINT "B_pkey" PRIMARY KEY ("type", "fieldA");
ALTER TABLE "TableA" DROP CONSTRAINT IF EXISTS "A_pkey", DROP CONSTRAINT IF EXISTS "A_fkey",
ADD CONSTRAINT "A_pkey" PRIMARY KEY ("type", "fieldA"),
ADD CONSTRAINT "A_fkey" FOREIGN KEY ("type", "fieldA") REFERENCES "TableB"("type", "fieldA");
where type is an ENUM and fieldA is a TEXT.
Can you reproduce the issue ?
Hello,
Thanks for this detailed example, I was able to reproduce the issue on my end !
At first sight it seems like a bug on the @forestadmin/agent package, but I will need some more time to investigate to be sure.
In the meantime, in order to unlock the table visibility you can can add a customization like that in your agent:
I have been making progress on this issue, the fix for the collection not displaying issue should be available from next week.
However, I do have to warn you that in fact the relationship will not be taken into account since it uses a composite foreign key, which is not supported in the @forestadmin/agent package.
This limitation stems from the sequelize dependency, that is used in the agent to support model relationships, but which does not support composite foreign keys (Composite foreign keys · Issue #311 · sequelize/sequelize · GitHub).
One suggested workaround (if you have some control over your postgres table definition) is to define a postgre generated column composed of a concatenation of the 2 fk columns and use this column for the relationship (either within postgre, or as a custom relation in the agent datasouce).
A fix for your issue has been released in the package @forestadmin/datasource-sql@1.14.11. Please upgrade your dependencies to the latest version to benefit from this fix, and let us know if it works for you
