Mysql provider filter with _id issue. "Unknown column _id in where clause"

[gmentzik]

Hi all,

1. I am using Mysql as datasource and my tables have a PK column named id. Could this be conflicting in any way with the auto generated id column of Budibase?
2. I have a custom page including an edit form with provider selecting the row to be edit with the Mysql id (url/:id <- MySQL id).
   However if I use filtering with _id (Budi autogenerated id) it say no such column exists!
   If I auto generate the edit page _id works!

So before I start getting crazy what could be wrong here?
And finally is there any advantate using the _id (Budibase id) vs Mysql(Datasourse) Id?

[melohagan]

Hey @gmentzik

The Budibase _id will simply map to the PK of external datasources, so in this case _id would map to id.

I'm surprised that Budibase cannot find _id when filtering. Could you provide some screenshots and/or the app export please?

[melohagan]

I'm not able to do much with the app export because I can't connect to the DB, I should have thought of that before I asked for the app export!

So if you were to add a Data Provider -> Repeater -> Paragraph and print out _id and smt_id do they look the same, or is there a difference?

[gmentzik]

I'm not able to do much with the app export because I can't connect to the DB, I should have thought of that before I asked for the app export!

So if you were to add a Data Provider -> Repeater -> Paragraph and print out _id and smt_id do they look the same, or is there a difference?

1st provider is the auto generated. Haven't touched filter. It works both passing smt_id and _id value at url. (Didn't know that)
2nd provider is the user added, set to filter with smt_id.

Regarding mysql db table is nothing special. A simple table with : smt_id (int) pk AI column and a value (varchar(45) column.

[melohagan]

Ah okay I see, when using _id its applying encoding to the id. Hmm, looks related to: #6194

[gmentzik]

Ah okay I see, when using _id its applying encoding to the id. Hmm, looks related to: #6194
Here is with filtering on _id

I don't think encoding is the issue. Something different there is between auto generated provider and user added provider.

[gmentzik]

@melohagan Any news? I feel unsafe with the solution to create an auto generated page to copy provider so that _id works. What if it breaks down in future?
Also even if I use the auto created provider, if i change or delete the filter with _id and re introduce it generates again same error. It tries to find a column with that name instead of using the magic Budibase id.