Is it possible to LEFT JOIN to a table and conditionally UPDATE or INSERT when editing?

Rashid_QS

Hello,

I am currently using the .NET library for Editor and am having trouble with the following scenario. Here is a simplified version of my table structure.

Users table.
Columns: UserID, FirstName, LastName, Email

UserCatchPhrases table
Columns: UserCatchPhraseID, UserID, CatchPhrase

Not every user has a catch phrase entry so I use the LeftJoin() method. I also only want to allow each user to have at most one catch phrase. I manually, in SQL Management Studio, added a handful of catch phrases for a few users and I can see that data being rendered correctly on the data table. I can user the editor's built in pop up to modify the catch phrases that already exist successfully. The problem I have is trying to get the a new catch phrase row to be inserted for users that don't have a record yet.

I wanted to make sure that I was not spinning my wheels. Any thoughts, insight, or reference material would be greatly appreciated.

I have gone through some of the JOIN documentation but the main difference I see has been that these examples used Options and were linking to the ID on the record in the LEFT JOINed. Also the records already exist there.
https://editor.datatables.net/manual/php/joins#Left-Join

If any more context is needed I will happily provide it.

Thank you

allan

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

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

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

Got it! Thanks for the clarification, Allan.

Rashid_QS

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

Thank you rf1234,

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