Unable to make relationships work on MariaDB

[sklaffa]

Hi All, sorry for the apparently noob question, but I've searched similar discussions and none of them seems to reproduce my seemingly trivial situation.
I've a "customers" table containing a list of customers, and a "projects" table which lists the projects started with those customers. A customer may have several projects associated, hence there is a "one-to-many" relationship between the "customers" and "projects" tables.
Here you can see how the database is structured in PHPMyAdmin:

And here is how the relationship is configured in Budibase:

What I observe is the following: if I simply try to manually populate the "projects" table within the "data" section of Budibase, the "customer" column created by Budibase does not work, if I try to add an existing customer the column blanks itself after selecting the desided customer. To create the association, I must actually manually fill the "customer_id" field by manually entering the "cust_id" key value. Doing so the "customer" column correctly displays the reference to the customer, but I actually can't operate on the "customer" column directly.

I assume I'm doing something wrong, but what? I'm clueless.

Best regards,
Filippo

[zedascouves]

So it seems I've just posted an issue related to this one: #10937. I'm having the same problem, but I think I've narrowed it down to a data format issue.

[sklaffa]

Hi @zedascouves and thanks for the feedback. The data type for the indexes is default one proposed by MariaDB, that is unsigned integer with 10 digits, as it can be seen from the database schema. I don’t know if I should try sonething else and, if yes, which MariaDB native type would do the trick…

[sklaffa]

OK so I've created a fictional, very simple database to replicate the issue. I created just two tables with a basic structure:

• Table1, with an INT field acting as primary key and a VARCHAR field just to put in some content;
• Table2, with an INT primary key, an INT field representing the foreign key for the relationship with table1, and a VARCHAR field to mimic the main content of the table.

I tried different integer types for the primary key (INT, SMALLINT, etc.) but whatever integer type I choose, then the relationship created within Budibase does not work:

1. if I insert a new row in table2 and try to use the relationship column generated by Budibase to add references from table2 to table1, the reference is not saved (but the new row is, even if the table view in Budibase does not show the new row);
2. If I try to update an existing row of table2 by adding a reference to a row of table1 using the relationship column generated by Budibase, I get an error message speaking about an "empty update call detected".

To further test I created a new test database, with the same structure as the previous one, but this time the primary key columns of both tables were set to CHAR(4). But again, once the relationship was created, it did not work with the same issues described above.

This is the DB structure:

And this is what happens when I try to use relationships within Budibase given the above structure:

Budibase.bug.mp4

Is this an actual Budibase bug, or am I doing something wrong?

Best regards,
Filippo

[melohagan]

Hey @sklaffa Sorry I'm only stumbling on this discussion thread today. Were any of the fields nullable, or were they all required NOT NULL fields?

[sklaffa]

Ok so I think I’ve found a workaround. What I found is this:

Setting up relationships using the Define Relationship dialog in Budibase DOES NOT work for me. If I setup relationships this way I get the issues I described in my opening post, and also in the DESIGN section the “relationship picker” object does NOT work. To verify whether the issues I’m facing are dependent on the specific backend, I tried to replicate the issue using PostgreSQL instead of MariaDB and I actually ran into the very same troubles.

At last, I managed to define working relationships by adding new columns to existing tables and setting the column type to RELATIONSHIP. Doing so I get other issues (for example, only the first relationship created in this way is shown in the list of defined relationships) but at least I have working relationship pickers for forms.

None of the issues I described here occur if I use the Budibase internal DB, but this is a non-relational database so I would be limited in other aspects. I need to have a relational backend. Unless I made some significant mistake installing or configuring Budibase (local hosting through the Budibase CLI and Docker), it looks like the relationships management is broken when using external relational databases as backends. Any suggestion would be welcome at this point.

Regards,
Filippo

[zedascouves]

Ok, I've managed to make it work, and I think it really is a (major) bug. I've ejected the form components until I get to the save action button. There, I've changed the actions a bit and I now transform the picker value like this, on the "Save row" action:

{{ toInt (remove (remove (remove (remove (stringify Details Form.Fields.VIPS_Country) '%5B') '%5D') '["') '"]') }}

So, my action looks like:

This way I can select my "foreign key" element on the picker, and it'll save the correct ID on the row. I'm duplicating this answer to my own post.

[cekaso-admin]

I have a similar issue because of the encoded id value (e.g. %5B1%5D -> [1]). For example, I can't set the default value on any of my form fields. It always ends up as null. This is very annoying. I also can't compare the id's. Even using the encoded values or removing the additional chars doesn't help.

[Reftek85]

I am facing the same issue, in my help thread #13623
I am trying to make @zedascouves workaround value work but I still get error