In an attempt to solve my latest fabrik issue (in bold below), I came across this today in the Professional Support area - and it has me asking if this issue is being addressed?
Copy record (via form copy) will "unlink" all joined data from original record
IMO, this is the best example of the horror that comes out of using ?repeat? tables to store data rather than Json encoding the data and storing it all in the same table.
As far as I can tell, copying (or deleting) any rows from a table that has any ?repeat? tables associated with it is never being done properly.
When a row is deleted ? are any/all elements in a row that contains an element that uses a repeat table being checked for? ? and are the rows in the repeat table(s) being deleted also?
When a row is cloned/copied ? are any elements in that row that uses a repeat table being checked for? and are the rows in the repeat table(s) being created/copied also?
I'm pretty sure the answer to all those questions is NO. So of course ? copying a row from a table that contains an element that uses a repeat table isn?t really copying all of that data.
The last I tested it (just last week) ? what is happening is, when you copy a repeat group in a form via the form copy plugin, the new row id of the newly created row is used to replace the ?parent_id? in any/all of the repeat tables. This is not what you want to do - and explains how you ?lose? the joined data.
What really needs to be done in such a case (for copying rows) is to initiate a function that will?
And this, IMO, is the can of worms that these ?repeat tables? have created. ( I have one table that uses 18 of these ?repeat? tables ? and to make matters worse I?m sure, that table is used as part of a repeat group.)
I have already written a function to ?clean up? and handle orphaned repeat tables and/or repeat table rows that reference a non-existent ?parent_id? ? but that is not something that should really be needed, if delete and copy/clone functions were being done right to begin with. I have also already created the functions to handle copying or deleting a row with 'repeat' tables associated with it (per my outlines above). But it only handles this one form in this one instance.
I?d offer up my code but it is being done very old school ? i.e. using php string functions to identify the repeat tables and field names that any repeat table is using. Maybe the better way would be to use the fabrik ?element? table to identify those repeat tables. Though, I dunno ? since both would accomplish the same thing.
My awakening to all of this came about because I now have an instance where I want to copy ALL of the values from a row in a repeat group into a new ?Form Templates? (?form_templates?) table ? to allow the user to use those settings, as saved, as a ?template? for a later-added repeat group. Not only would that require this new ?form_templates? table to hold cloned values of the elements in the form from which a template is being created ? but it would require creating all of the associated ?repeat? tables ? e.g. a new ?form_templates_repeat___facility_types? table to hold the values from the database join element ?facility_types?. But, besides physically creating the new empty table structures for the tables (copied/renamed via mySql), then adding the new ?form_templates? table to fabrik - wouldn?t I have to add the ?repeat? tables to the #__fabrik_elements or #__fabrik-joins tables also? Or would they automatically get created when the new ?form_templates? parent table is added as a new fabrik table?
Copy record (via form copy) will "unlink" all joined data from original record
IMO, this is the best example of the horror that comes out of using ?repeat? tables to store data rather than Json encoding the data and storing it all in the same table.
As far as I can tell, copying (or deleting) any rows from a table that has any ?repeat? tables associated with it is never being done properly.
When a row is deleted ? are any/all elements in a row that contains an element that uses a repeat table being checked for? ? and are the rows in the repeat table(s) being deleted also?
When a row is cloned/copied ? are any elements in that row that uses a repeat table being checked for? and are the rows in the repeat table(s) being created/copied also?
I'm pretty sure the answer to all those questions is NO. So of course ? copying a row from a table that contains an element that uses a repeat table isn?t really copying all of that data.
The last I tested it (just last week) ? what is happening is, when you copy a repeat group in a form via the form copy plugin, the new row id of the newly created row is used to replace the ?parent_id? in any/all of the repeat tables. This is not what you want to do - and explains how you ?lose? the joined data.
What really needs to be done in such a case (for copying rows) is to initiate a function that will?
- Store the id of the original row being copied to a variable (e.g. $original_id).
- Create the new parent table row/record and import the copied row data into it.
- Save and store that new row id to a variable (e.g. as $new_id ? which will become the new ?parent_id? in the newly created repeat table rows done in #6 below).
- Using a query on the INFORMATION _ SCHEMA . TABLES, get a list of all the ?repeat? tables used by the table from which a row is being copied.
- Loop through that list ? and, using the $original_id as the WHERE condition ( e.g. ?WHERE parent_id=?.$original_id ), query each of the ?repeat? tables to produce an array of all the rows from the repeat table that contain the data for the copied row (that has that parent_id).
- Then INSERT those query results into that repeat table ? using the $new_id for the ?parent_id?.
- Repeat #5 until all the ?repeat? tables have been updated with the copied data for the element that repeat table represents.
- Store the id of the original row being deleted to a variable (e.g. $delete_id).
- Delete that row from the table.
- Using a query on the INFORMATION _ SCHEMA . TABLES, get a list of all the ?repeat? tables used by the table from which a row is being copied.
- Loop through that list of repeat tables ? and, using the $delete_id as the WHERE condition ( e.g. ?WHERE parent_id=?.$delete_id ), delete all rows from each of those ?repeat? tables that contain that ?parent_id? value.
- But to really complicate matters - If the list has linked/joined tables and/or repeat groups ? you would have repeat this process/function on the joined table row(s) and/or repeat groups.
And this, IMO, is the can of worms that these ?repeat tables? have created. ( I have one table that uses 18 of these ?repeat? tables ? and to make matters worse I?m sure, that table is used as part of a repeat group.)
I have already written a function to ?clean up? and handle orphaned repeat tables and/or repeat table rows that reference a non-existent ?parent_id? ? but that is not something that should really be needed, if delete and copy/clone functions were being done right to begin with. I have also already created the functions to handle copying or deleting a row with 'repeat' tables associated with it (per my outlines above). But it only handles this one form in this one instance.
I?d offer up my code but it is being done very old school ? i.e. using php string functions to identify the repeat tables and field names that any repeat table is using. Maybe the better way would be to use the fabrik ?element? table to identify those repeat tables. Though, I dunno ? since both would accomplish the same thing.
My awakening to all of this came about because I now have an instance where I want to copy ALL of the values from a row in a repeat group into a new ?Form Templates? (?form_templates?) table ? to allow the user to use those settings, as saved, as a ?template? for a later-added repeat group. Not only would that require this new ?form_templates? table to hold cloned values of the elements in the form from which a template is being created ? but it would require creating all of the associated ?repeat? tables ? e.g. a new ?form_templates_repeat___facility_types? table to hold the values from the database join element ?facility_types?. But, besides physically creating the new empty table structures for the tables (copied/renamed via mySql), then adding the new ?form_templates? table to fabrik - wouldn?t I have to add the ?repeat? tables to the #__fabrik_elements or #__fabrik-joins tables also? Or would they automatically get created when the new ?form_templates? parent table is added as a new fabrik table?