Are there any options to not delete on an MJoin "edit"?

jacob.steinberger

https://datatables.net/forums/discussion/24159/inline-editing-with-joined-tables-is-deleting-data
https://datatables.net/forums/discussion/69978/mjoin-providing-list-of-linked-records-to-delete

Seems like this has come up a few times, and was discussed for release in Editor 2.1, but I haven't found any references in the release notes or Mjoin documentation.

In short, I have an Mjoin, and that relationship has its own "options" that can be set. If I have an existing record with 2 relationships, and add a 3rd, the first two are deleted before being recreated. This means all the previously set options associated with record #1 and #2 are deleted.

Was this feature/problem addressed?

allan

Not directly. I presume that the relationship has what I would call a link table (I believe it is also commonly called a "junction" table). That is the table which has extra properties on it does it?

Unfortunately no, there is no option to keep that extra data - it is not clear to me where it would come from (perhaps it would have to be defined programmatically at the server-side and that enforced in the API). What is the extra data - a user id or something like that?

There are a lot of complications with trying to maintain the data - for example the user deselecting all options and saving that, then coming back and selecting an option. Data that would have been set externally for those links, would now be gone. If it was set programmatically (i.e. not by the user), then perhaps there would be a way to do it by allowing that in the API, but I suspect it might get confusing.

Allan

jacob.steinberger

I am using a link table, and the link table itself is another instance of DT/Editor to further define.

If someone deleted all the Mjoin for a record, and came back later to re-add it, I'd expect it to be reset and have to start over. But deleting all the Mjoin records when Mjoin is being added to, or a non-Mjoin related column is changing, is the painful part.

I think I've found the relevant mjoin code that does this, and will see if I can dream up a checker so it doesn't add or delete a record if it already exists and is still in the populated list.

allan

If the link table had its own primary key column, then we could probably over come this. For example, if you had two records selected and the user changed both, how would you know which one to update the values of? I suppose it could submit all of the meta data as well for a comparison, but an id column would resolve that conflict. That might be the best way to resolve this issue in fact.

Allan

jacob.steinberger

Linking table does have its own primary key (id, autoincrement).

allan

Perfect - I was thinking about it over night and I think this will be the way to resolve it.

I'm currently focused on the development work for DataTables 2, so I can't say for certain when this will be added to Editor, but I have put it in my tracker with a 2.3 release target.

Allan

jacob.steinberger

I was looking at it a bit more loosely, I think. Since the update function has the incoming data, I was just going to check to see if the values already/still exist and if so, do nothing on both the remove and creation function.

But that means learning a bit more about the inner-workings of Editor than I originally estimated!

Still trying to see if I can figure out a workaround.

allan

This is the relevant part of the PHP libraries. What will need to happen is that an extra pkey option be added for the join and that information retained by the client-side, and then processed here.

I don't think this is going to be a trivial change at all I'm afraid.

Allan

jacob.steinberger

I was looking at this guy.

And I would agree, it's not easy, but I get the opportunity to learn something new, so that's something

jacob.steinberger

I was able to solve this by adding a new class (started as a copy of Mjoin) that I called Jjoin. From there, I added a select function that:
* Pulls current records from DB based on this_name and join.table

Updated delete function to
* Delete records from DB based via whereNotIn, based on data[this._name].flatMap

Pass records to the existing create function based on being in data, but absent from records list.

Probably a more elegant way to do this (like what you said, with pkey), but I haven't messed with code this low-level for managing promises, so it was quite the learning curve for me. Also works with linking tables with no primary.

paravis

Hello @jacob.steinberger, would you mind sharing this code that you created? I, too, have actually quite a few situations where I really need to keep those cross-references (that also have an id primary key column) in order to then add attributes to that specific link.

I thought about using triggers, but even then, the deleting of the links will make that approach a disaster.

Or, @allan, do you have any further updates on the "official" approach that you might take?

Thanks for all of your help, gents.

allan

No change to the libraries I distribute yet I'm afraid. I suspect what will be needed when I do this is that the link table will need a unique primary key, which many link tables don't have, but that's the only reliable solution I've got atm.

Sorry I don't yet have better news.

Allan

jacob.steinberger

It took a few months, but I did get approval from my employer to post it.

I could toss it up as a branch, but that may be overkill if the desired results arent desired. @paravis , I'm working in NodeJS, so my changes are in javascript, but I assume they could be easily altered for PHP or other platforms.

@allan , I hope this is ok to post.

Again, the changes revolve around the update function, and leaving as much alone as possible. The biggest change is adding the SelectRemove function, which basically is a flashy delete statement to only delete the rows that aren't in the submit from the client data (thus, leaving the pre-existing children alone).

diff mjoin.ts ../../node_modules/datatables.net-editor-server/src/mjoin.ts
610,614c610
<               if ( data[this._name + '-many-count'] === '0' ) {
<                       await this.remove( editor, [parentId] );
<               } else {
<                       await this.selectRemove( editor, parentId, data );
<               }
---
>               await this.remove( editor, [parentId] );
619,640d614
<     * Selectively delete records based on their absence from
<     * the data from the client
<       */
<       public async selectRemove( editor: Editor, parentId: string, data: object ): Promise<void> {
<               if ( ! this._set ) {
<                       return;
<               }
< 
<               this._prepare( editor );
<               let db = editor.db();
<               let join = this._join;
<               let children = data[this._name].flatMap(x=>x.id);
<               let query = db
<                       .del()
<                       .from(join.table)
<                       .where(join.parent[1],parentId)
<                       .whereNotIn(join.child[1],children);
< 
<               await query;
<       }
< 
<       /**
736,737c710
<                               .from( join.table )
<                               .onConflict().ignore();
---
>                               .from( join.table );

The last bit, .onConflict().ignore();, is to tell knex to not generate errors on add/inserts, if the record already exists. You could absolutely update the insert functions instead of taking the "lazy" route.

As a side note, this solution does not require a unique column ID type field (as discussed previously in this thread). Since the delete is based on the parent and the child values, we're examining the actual join table values, and not a third ID column.

jacob.steinberger

And my last comment about not requiring an ID field could be wrong, now that I look at the final implementation and use of flatMap:

<               let children = data[this._name].flatMap(x=>x.id);

Mileage may vary!

allan

Delighted - thank you for sharing this! It is much appreciated!

Allan

jacob.steinberger

    private async _insert( db: Knex, parentId: string, data: object ): Promise<void> {
        let join = this._join;
        let fields = this.fields();

        if ( join.table ) {
            // Insert keys into the join table
            //console.log('1',join.parent[1],'2', parentId, '3', join.child[1], '4', data[join.child[0]]);
            await db
                .select()
                .from( join.table )
                .where( join.parent[1], parentId )
                .andWhere( join.child[1], data[ join.child[0] ] )
                .then(async function(rows) {
                    if ( rows.length===0) {
                        // no matching records found
                        await db
                            .insert( {
                                [join.parent[1]]: parentId,
                                [join.child[1]]: data[ join.child[0] ]
                            } )
                            .from( join.table );
                    }
                });
        }

I updated the join.table section of _insert, to retain error messages. What I did previously wasn't a great practice, but was to make a minimal amount of changes to achieve the goal. The above change is basically doing a select from the join table first, to see if the record is already there. If it's not, it will then perform the insert.

This should retain errors, if one is encountered, and prevent duplicate inserts if you aren't using database unique keys.