Getting Multi Reference query error with Big Increment IDs

[that_cheeseguy]

Wappler Version : 4.8.2
Operating System : MacOS Big Sur - M1
Server Model: NodeJS
Database Type: Postgres
Hosting Type: Own Server

Expected behavior

What do you think should happen?

When performing a query and returning multi reference data, I expect the query to return the related data as shown here.

Actual behavior

What actually happens?

I have an app_user table that has multi-referenced role_assignment under it. The id field on the app_user table is of big increment type.

The role_assignment also has data in it, so it is not empty.

When I query the database to return a record, I get the following error when I make a request:
TypeError: results is not iterable\n at App._processSubQueries (.../lib/modules/dbconnector.js:279:38)

---

To confirm that it wasn’t just an issue with that app_user table alone. I made a new temporary user table, this time with a regular increment id (not big increment). I then added a role_assignment multi reference to the table, filled in the data and made a request, and it worked. I got the nested results.

I assume this has something to do with the way big increment IDs are parsed?

[patrick]

Are the fields in both tables of the same type, so is the foreign key field also of the bigint type?

[that_cheeseguy]

The id field in the app_user table is bigIncrement and the role_assignment.app_user_id field is a reference type, which is what Wappler defaults to when linking foreign keys.

Changing the type of the role_assignment.app_user_id from a Reference Field to a BigIncrement field removes the relational features such as OnUpdate and OnDelete.

The big increment id field being used as a reference key doesn’t cause issues when performing regular joins. The problems only happen when performing nested queries.

[patrick]

Thx for the feedback. It seems that the Big Increment becomes a BigInt type and the Reference is a normal Int type. For the database this is not a problem, the normal Int can hold less data than the BigInt. Problem is with NodeJS, the database driver converts the BigInt to a string and the Int to a number, this is because JavaScript doesn’t support BigInt (It supports them in the latest version, but it still has issues there). Because of this it doesn’t match the ids correctly and generates the error, we will look for a solution. Ideal the Reference type should be the same as the Identifier.

[George]

Will see if we can make, in the Database Manager, the Reference fields automatically of the same type as the the field they are pointing to.

[that_cheeseguy]

Ohh I see, thanks for getting back to me. It seems the only options I have at the moment are:

1. Pause any backend work that involves the database until the reference field issue gets fixed. And then migrate existing reference fields to match the original field type.

2. Temporarily convert existing database tables that use Big Increment to a normal Increment so I can still do backend work with the database. When the reference field issue gets fixed, migrate the fields back to big Increment.

3. Manually edit knex migration files to force the reference fields to match the id type from the other table.

If there’s another alternative that I’m missing, please let me know.

[George]

You can just convert your reference field to be of type big integer as well, you can use pgadmin for that.

[that_cheeseguy]

Thanks, that works too!

For anyone else in my situation, I’ve also manually updated the migration files that reference the bigIncrement ids, so that I can sync the changes across targets. That means updating the reference field migrations like table.integer('field_name').unsigned() with table.bigInteger('field_name).unsigned() for bigIncrement ids.

[Teodor]

This has been fixed in Wappler 4.8.3