Answers

• allan Posts: 63,145Questions: 1Answers: 10,403 Site admin

  October 2023

  Is the separation of the tables necessary? I'm wondering if the simplest solution would actually to just be to have another text field on the Users table called CatchPhrase? Then it is a plain regular text field to edit, always limited to one and easy to validate.

  Another option, if you are willing to change the schema, would be to add a CatchPhraseID column to users and then let users pick from a drop down of catch phrases. If needed you could do nested editing or parent child editing if you didn't want the catch phrases to be visible to other users.

  Editor's editing doesn't really lend itself to left join table editing without the nesting approach for exactly the reason you have found - there is no existing link to update.

  If you didn't simplify the tables for this post, then I think having another text field is your simplest option. If you did simplify it for the sake of brevity here, perhaps you could detail a little more what you are looking to do?

  Allan
• rf1234 Posts: 2,936Questions: 87Answers: 415

  October 2023 edited October 2023 Answer ✓

  Editor's editing doesn't really lend itself to left join table editing without the nesting approach for exactly the reason you have found - there is no existing link to update.

  But why is this so? You could simply INSERT the link, I guess.

  Since this doesn't work I am doing this manually on "writeCreate" and on "writeEdit" of the parent table.

  In this case the solution would be to use ->set (false ) for the left joined table's field.

  On "writeCreate" I would do a manual INSERT into the child table - regardless of whether the catch phrase was filled or not. On "writeEdit" I would do an UPDATE of the child table regardless of whether or not the catch phrase was changed.

  In PHP this could look like this:

  ->on( 'writeCreate', function ( $editor, $id, $values ) use ( $db ) {
      $res = $db->insert( 'UserCatchPhrases ', array (
          'user_id'        => $id,
          'catch_phrase'   => $values["UserCatchPhrases"]["catch_phrase"]
      ) );
  } )
  ->on( 'writeEdit', function ( $editor, $id, $values ) use ( $db ) {
      $res = $db->update( 'UserCatchPhrases', array( 
          'catch_phrase'  => $values["UserCatchPhrases"]["catch_phrase"]
      ), array( 'user_id' => $id ) );
  } )
  

  You don't need to take any provisions for DELETE in case you have referential integrity properly implemented in your data model (DELETE CASCADE).
• allan Posts: 63,145Questions: 1Answers: 10,403 Site admin

  October 2023 edited October 2023

  I'm simplifying a little - it actually does attempt to do an insert to a left join if it is required.

  Part of the issue is that the left joins happen after the main row. That actually works in this case, but in many cases the left join inserts need to be done first.

  The main complexity though is the primary key of the left joined table. Normally an update is done with the primary key, but that isn't always present for the left joined column (it might need to be mandated), or even worse the user selects a different left joined row and submits a changed value.

  There are a lot of footguns with it and I prefer to try and steer folks to a different solution that I find it conceptually easier to explain. In the case described here, an extra text column is by far the easiest option, but only if the description is the full issue and data set - there might be other things at play that I'm not aware of.

  What I need to do sometime is plot all of this out and document it in detail. Until then, nested editing is the way forward with the DataTable control .

  Allan
• rf1234 Posts: 2,936Questions: 87Answers: 415

  October 2023

  Got it! Thanks for the clarification, Allan.
• Rashid_QS Posts: 5Questions: 2Answers: 0

  October 2023

  Good morning and thank you for your responses.

  Allan, what you say is true with regards to modifying my table structure for the example that I provided. That would be the easiest move. However, I am currently working on a proof-of-concept web application that will need to have dynamic column support once it is deployed. There would be tables which define the columns that should be displayed and editable for different organizations. Each organization would provide data from their own system to be imported prior to their first use. Much of their data will look similar but some organizations will have extra data points they would like tracked and edited. The site admin on our end would see that there were, let's say, three extra fields that we need imported. Two might be text fields and the last one might be a date. The admin would define these on our dashboard and add them as columns for the organization. Data import would then be run and soon after the members from the organization would log in and be able to manipulate that data. So a row in the data table would be…

  A record from a base table which is a parent of sorts. And X records from another table that stores the dynamic column data. So if it held column data for “CompanyReferenceNumberMisc” then it might look like:

  ColDataID -> Primary Key, Identity
  FieldTypeID -> ID of the field/column’s definition so to speak.
  FieldData -> The stored data.. “1002”, “123SomeData”, “Etc”

  I currently have code written that can dynamically generate the columns for the data table and editor. It also runs the editor’s LeftJoin() methods and the likes correctly. It’s just the issue in my original question that I’ve been wrestling with.

  I know that one possible solution to my problem would just be to generate a stub record in the LEFT JOINed table with a NULL FieldData value whenever I add a new column but I was seeing if there was a more elegant way.

  One more final note in relation to why I gave the example that I did. A user may pull up a record that was edited a month ago. Since their last edit there may have been a new field added that may need to be populated and/or editable.

  I apologize if I seem all over the place but am trying give enough context without being overly verbose.

  Thanks again.
• Rashid_QS Posts: 5Questions: 2Answers: 0

  October 2023

  Thank you rf1234,

  I will look into the writeCreate and writeEdit events now and get back to you.