[bug?] inelegant handling of one-to-one joins SOLVED

[batonac]

Referencing my original question on joins:

Firstly, are one-to-one joins compatible with Fabrik? I'm thinking two tables such as 'school' and 'school_mission_vision' that share the same primary key (entries from the second table are identified by the auto-incrementing primary key in the first table). I see this layout as beneficial for storing additional data for a table that does not need linked to every list where that table is used. Will Fabrik be able to understand/enforce this structure?

And Rob's answer:

One to one joins should work. Create the list pointing at the school db table, then edit it to add the join and ensure that the newly created join's group is not set to be repeatable.

I've tried both sharing the same primary key, with primary table id auto-incremental, _extradata not (I believe this is SQL preferred):

Also unique auto-incremental primary keys, with an extra foreign key column in the _extradata table:

I haven't been able to make the first setup work in Fabrik at all. Fabrik likes the second setup better, and allows you to join & edit the two tables together in a single form, as long as new data is not being entered into both forms at the same time.

In other words, in the above example, fabrik will correctly save/associate data in both table2 and table2_extradata together as long as we are editing an entry of table2 that already exists, not adding a new one.

Is there any way around these limitations? Also, do devs view this as a new feature request, or a bug (what's the fixing priority)?

Thanks

 

[rob]

Yeah the first is not how fabrik sees things - so we can forget about that one

The second one really should work, I'm just wondering how you are setting the tables up - are you letting fabrik do everything or are you trying to model them outside of fabrik and then creating lists from them? Note we don't follow/allow for INNODB foreign keys - perhaps you are using those?

Also can you show your db joins setting as a screen shot - or let us know which site/list you are testing with?

 

[batonac]

I am modeling tables outside of fabrik, and I am using foreign keys. With further testing I've found that the second setup does work if I allow Fabrik to create the tables. Could I kindly ask what makes the difference? I knew that fabrik doesn't enforce innodb restraints, tripping database errors if user violates them, but I didn't know that they tampered with Fabrik's core functions.

 

[rob]

Not sure why it makes a difference, Hugh is more of the one to one join guy (I can't think that I've ever used them for a personal project). Could you send a dump of your database structure and I'll have a go at replicating the issue locally and seeing what is causing the issue?

 

[cheesegrits]

In the second case, is the FK on the parent or the child? I'm pretty sure your picture shows it as being on the child (the table you are joining to in the main List). So you have child.parent_id (FK) pointing to parent.id (PK)?

I really have no clue what might be preventing us from saving, if you are building Lists on pre-existing InnoDB tables. That's why one of our pre-requisites is MyISAM, and why we create MyISAM tables.

As Rob said, we'd need an SQL dump so we can test it. But I don't think we can guarantee to fix whatever the issue is.

-- hugh

 

[batonac]

Someone must have fixed something! On the latest github code I can't replicate the issue! Thanks guys!