One block; three tables; losing foreign key sync when saving
Permalink
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
TL;DR: when a single block has two additional tables, one of which has a primary key that is used as a foreign key in the other, how do you tell the c5 core that when it changes the three primary keys during updates, that it also needs to update the foreign key in the third table?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Hi. I am developing a single block based on the c5 Google Maps block, but with the ability to create marker types (e.g. with a blue 'binoculars' icon and a user-specified label) and then create markers that use those marker-types, along with user-entered info-boxes. Everything works fine up to the point of saving a block update.
When creating a new map marker, the user selects the required marker-type, which catches the marker-type ID for saving with the marker record. Great, but here's the problem: At the point of the save, a new map record is created with a new ID, along with new marker and marker-type records, all with new IDs. But the marker record is now referencing the marker-type ID prior to the save and consequent ID update.
How should I deal with this please? The ID-updates are happening auto-magically so I don't understand how to maintain sync of the foreign-keys as they all increment outside of my control.
TL;DR: when a single block has two additional tables, one of which has a primary key that is used as a foreign key in the other, how do you tell the c5 core that when it changes the three primary keys during updates, that it also needs to update the foreign key in the third table?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Hi. I am developing a single block based on the c5 Google Maps block, but with the ability to create marker types (e.g. with a blue 'binoculars' icon and a user-specified label) and then create markers that use those marker-types, along with user-entered info-boxes. Everything works fine up to the point of saving a block update.
When creating a new map marker, the user selects the required marker-type, which catches the marker-type ID for saving with the marker record. Great, but here's the problem: At the point of the save, a new map record is created with a new ID, along with new marker and marker-type records, all with new IDs. But the marker record is now referencing the marker-type ID prior to the save and consequent ID update.
How should I deal with this please? The ID-updates are happening auto-magically so I don't understand how to maintain sync of the foreign-keys as they all increment outside of my control.
<?xml version="1.0" encoding="UTF-8"?> <schema xmlns="http://www.concrete5.org/doctrine-xml/0.5" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.concrete5.org/doctrine-xml/0.5http://concrete5.github.io/doctrine-xml/doctrine-xml-0.5.xsd"&... <table name="btPoiMap"> <field name="bID" type="integer"><unsigned/><key/></field> <field name="title" type="string" size="255" /> <field name="overlay" type="string" size="255" /> <field name="location" type="string" size="255" /> <field name="latitude" type="float" /> <field name="longitude" type="float" /> <field name="zoom" type="smallint" /> <field name="fitBounds" type="boolean"><default value="1" /><notnull/></field> <field name="width" type="string" size="8"><default value="100%" /></field> <field name="height" type="string" size="8"><default value="400px" /></field> <field name="scrollwheel" type="boolean"><default value="1" /><notnull/></field> </table>
Viewing 15 lines of 37 lines. View entire code block.
Thanks mnakalay but the core Image Slider has already been my primary reference point for creating my new block, along with the core Google Maps block. My issue is specific to running three tables whereby the primary key of the secondary table has a foreign key in the ternary table.
In the end I gave up trying to handle it in whatever the 'proper' way is for c5, as I simply couldn't work out what the 'proper' way might be. I wrote my own code-based solution for the creation and tracking of a foreign key that links the secondary and ternary table items together; one that does not change when a block instance is updated/duplicated.
In the end I gave up trying to handle it in whatever the 'proper' way is for c5, as I simply couldn't work out what the 'proper' way might be. I wrote my own code-based solution for the creation and tracking of a foreign key that links the secondary and ternary table items together; one that does not change when a block instance is updated/duplicated.
In many cases the use of a secondary table for blocks with variable lists is misdirection driven by data purism. As long as secondary data does not need to be accessed outside of the block, it can be simpler and functionally sufficient to serialise the secondary data as json and save it in the block table.
Thanks for your thoughts John, and I do understand what you are suggesting, but in this case there is a clear, logical case for treating the data records as 'one-to-many' in each case, both one map instance to many markers, and one marker-type to many markers. Not only is this clean and tidy from a data storage and access perspective (call it purism if you like), but once we introduce the need to filter the map display by marker-type (which is what I've done in this case), trying to do so with serialised ternary data would be too messy for any self-respecting developer to even consider.
So as I've said, I coded my out of this particular problem with a custom FK mechanism, but for future reference I'd still like to know if there is a 'correct' way to approach the problem with regards to built-in c5 core functionality. Have I missed some basic schema config mark-up that would give me the automatic FK updates that I need(ed) when updating an instance of the block?
So as I've said, I coded my out of this particular problem with a custom FK mechanism, but for future reference I'd still like to know if there is a 'correct' way to approach the problem with regards to built-in c5 core functionality. Have I missed some basic schema config mark-up that would give me the automatic FK updates that I need(ed) when updating an instance of the block?
Is that a case for the map points to be completely independent of the block?
(as per the various dealer-locator addons in the marketplace)
When I have built systems as you describe, it has usually been built out from one of these addons.
(as per the various dealer-locator addons in the marketplace)
When I have built systems as you describe, it has usually been built out from one of these addons.
I don't see that as necessary or desirable, John, as I like the portability of everything being integral to one block. But thanks for the suggestion.
Something I don't understand is you're saying that IDs for all 3 tables are changing. But as far as I know, Concrete5 automatically creates a new record with new ID only for the main table, referenced as such in the block's controller. It is then your responsibility to get that new ID and update the other 2 tables.
I don't understand how, in your case, all 3 tables get new records with new IDs?
Could you maybe post your controller here so we can have a look?
I don't understand how, in your case, all 3 tables get new records with new IDs?
Could you maybe post your controller here so we can have a look?
If you look at the schema you will see that the marker ID (mID) and the marker-type ID (mtID) are both set as incrementing primary keys. This is intentional, as every record in a replicated table (which these are) needs a primary key, and all tables should have one regardless. Thus when the table records are duplicated following an update, the primary keys all increment, which is what I would expect and desire to happen.
If only the primary block table was to duplicate (and thus change its bID) following an update, how could one associate the secondary (or ternary) records with the primary table if the secondary and ternary bID fields don't also change to maintain that association? The controller reflects this within its duplicate() method:
If only the primary block table was to duplicate (and thus change its bID) following an update, how could one associate the secondary (or ternary) records with the primary table if the secondary and ternary bID fields don't also change to maintain that association? The controller reflects this within its duplicate() method:
public function duplicate($newBID) { parent::duplicate($newBID); $db = Database::get(); $v = [$this->bID]; // duplicate the marker types $q = 'select * from btPoiMapMarkerTypes where bID = ?'; $r = $db->query($q, $v); while ($row = $r->FetchRow()) { $db->execute('INSERT INTO btPoiMapMarkerTypes (bID, mtNumber, icon, label, circle, filter, mtSortOrder) values(?,?,?,?,?,?,?)', [ $newBID, $row['mtNumber'], $row['icon'], $row['label'],
Viewing 15 lines of 40 lines. View entire code block.
I wonder if you don't have a structural problem in how you manage the data.
My understanding is that marker types are not block-specific. The same type can be used by many blocks.
If I am correct then I have 2 questions:
First, why do you need to have the bID in the market types table
Second, why do you need to create new marker type records on update?
As I see it, markers are block-specific and they reference marker types which are NOT block-specific. So really you should only use an INSERT for markers and you shouldn't need to have the bID or use an INSERT for marker types.
If for some reason you need the bID in the marker type table than you can but again, I don't see why you would need an INSERT for marker types, a simple UPDATE would do.
Last but not least, I noticed that in your code, when inserting the new markers, you don't actually have the required mtID. That probably also needs to be corrected.
My understanding is that marker types are not block-specific. The same type can be used by many blocks.
If I am correct then I have 2 questions:
First, why do you need to have the bID in the market types table
Second, why do you need to create new marker type records on update?
As I see it, markers are block-specific and they reference marker types which are NOT block-specific. So really you should only use an INSERT for markers and you shouldn't need to have the bID or use an INSERT for marker types.
If for some reason you need the bID in the marker type table than you can but again, I don't see why you would need an INSERT for marker types, a simple UPDATE would do.
Last but not least, I noticed that in your code, when inserting the new markers, you don't actually have the required mtID. That probably also needs to be corrected.
Thanks for trying to help, but in my block, the marker-types are most definitely specific to the block. The operator can select/add the custom-svg icon, the label, the colour, the background shape, and whether or not to include the marker-type in the selectable filter whereby the end-user can filter out marker-types that they are not interested in. All these options are most definitely block-specific.
Similarly, the operator can choose where to place a marker, what marker-type it is, if it is to have any infoContent, and if it is to have a 'more info' button etc. Again, these are all block-specific features. You may see marker types as not being block-specific, but with respect, it's my block and I say that they are block-specific.
With regards to the mID field, again thanks for trying to assist me, but this was the original Foreign Key relationship that caused the problem. I have gotten round the issue by leaving the marker-type primary key (mtID) alone to satisfy Percona XtraDB Cluster's strict table requirements of every table needing a PK, and created 'mtNumber' as a pseudo-PK for the purposes of having an ID that does not change when the table is duplicated. The pseudo-FK of 'mtNumber' is 'mtNum', and once created, this relationship never gets touched. When creating a new marker-type, I populate 'mtNumber' as follows:
Similarly, the operator can choose where to place a marker, what marker-type it is, if it is to have any infoContent, and if it is to have a 'more info' button etc. Again, these are all block-specific features. You may see marker types as not being block-specific, but with respect, it's my block and I say that they are block-specific.
With regards to the mID field, again thanks for trying to assist me, but this was the original Foreign Key relationship that caused the problem. I have gotten round the issue by leaving the marker-type primary key (mtID) alone to satisfy Percona XtraDB Cluster's strict table requirements of every table needing a PK, and created 'mtNumber' as a pseudo-PK for the purposes of having an ID that does not change when the table is duplicated. The pseudo-FK of 'mtNumber' is 'mtNum', and once created, this relationship never gets touched. When creating a new marker-type, I populate 'mtNumber' as follows:
$row_max = $db->fetchAssoc('SELECT max(mtNumber) from btPoiMapMarkerTypes WHERE bID = ?', [$this->bID]); $args['mtNumber'][$i] = (int)$row_max['max(mtNumber)']+1;
You should look at the core Image Slider block which uses 2 tables to see how they do it.
Pay particular attention to the save() and duplicate() functions.
The comment for the duplicate() function says: "Automatically run when a block is duplicated. This most likely happens when a block is edited: a block is first duplicated, and then presented to the user to make changes."