Datatable Editor - Use "non" primary/auto-increment key of parent table for mjoin

Capamania

I using a mjoin. Instead of using the 'groups_access.id' to link the parent table 'groups_access' to the reference table 'users_access', I would like to use the value from another field 'groups_access.user_id' though ... which does not work yet. Is this even possible?

That's more or less my setup:

let editor = new Editor( db, 'groups_access', 'id' )
.fields(
  new Field( 'groups_access.id' ),
  new Field( 'groups_access.user_id' ),
  new Field( 'groups_access.account_id' ),
  new Field( 'groups_access.group_id' ),
  new Field( 'groups_access.group_limit' ),   
  new Field( 'groups_access.name' ),   
  new Field( 'users.id' ),
  new Field( 'users.email' ),
  new Field( 'users.username' ),   
  new Field( 'accounts.id' ),
  new Field( 'accounts.email' ),
  new Field( 'accounts.name' ),
  new Field( 'groups.id' ),
  new Field( 'groups.name' )
)
.leftJoin('users', 'users.id', '=', 'groups_access.user_id')
.leftJoin('accounts', 'accounts.id', '=', 'groups_access.account_id')
.leftJoin('groups', 'groups.id', '=', 'groups_access.group_id')
.join(
  new Mjoin('groups_limits')
    .link('groups_access.user_id', 'users_access.user_id')
    //.link('users.id', 'users_access.user_id')
    .link('groups_limits.id', 'users_access.limit_id')
    .fields(
      new Field('id')
        .options(new Options()
          .table('groups_limits')
          .value('id')
          .label(['group_id', 'limit'])
        ),
      new Field('group_id'),
      new Field('limit')
    )
);

editor_access = new $.fn.dataTable.Editor( {
    ajax: {
        url: "/admin/groups/access"
    },
    table: "#access_all",
    template: '#accessTemplate',
    fields: [ {
            label: "User:",
            name: "groups_access.user_id",
            className: 'block full'
        }, {
            ...
        }, {
            label: "User-Access (Limit):",
            name: "groups_limits[].id",
            type: "select2"
        }
    ]
} );    

More specific, what works fine is if I edit table 'users', mjoin it with table 'groups_limits' and link everything in reference table 'users_access'.

But if I want to give permission to a specific user for a specific limit out of the 'groups_access' table as describte above, It always uses 'groups_access.id' as 'dteditor_pkey' instead of 'groups_access.user_id' as desired.

Table to edit is groups_access:

table: groups_access
id, user_id, account_id, group_id, group_limit, name
1, 1, 1, 24, Group 24
2, 1, 1, 28, Group 28
3, 1, 1, 29, Group 29

... and right now I'm getting (after editing group_access records: 1, then 2 & then 3):

table: users_access
id, user_id, limit_id
1, 1, 1
2, 2, 1 
3, 2, 2
4, 3, 1
5, 3, 3
6, 3, 6

... if I edit all 3 records and select different values in each case for field 'groups_limits[].id'.

What I'm hoping to get though is this:

Edit groups_access record 1 (user_id: 1) (selected limit_id: 1):

table: users_access 
id, user_id, limit_id
1, 1, 1

Edit groups_access record 2 (user_id: 1) (selected limit_id: 1 & 2):

table: users_access 
id, user_id, limit_id
1, 1, 1
2, 1, 2 

Edit groups_access record 3 (user_id: 1) (selected limit_id: 1, 3 & 6):

table: users_access 
id, user_id, limit_id
1, 1, 1
2, 1, 3
3, 1, 6

I hoped that .link('groups_access.user_id', 'users_access.user_id') would tell Editor to use 'groups_access.user_id' as desired, but it ignores it.

Capamania

I'm using Editor 1.9.2

allan

Hi,

Let me get back to you on this one. This is the code where it is working out what field to use. I'll try to create a local case that replicates this.

Allan

allan

Could you try updating your Editor Node.js libraries to 2.0.8 please? They are compatible with Editor 1.9.x on the client-side and don't need a v2 license (the server-side libraries are open source - MIT license).

I've just tried debugging this locally and it is actually behaving as expected with the latest code.

Allan

Capamania

Hi Allan, unfortunatelly it is still not working on my side. I updated "datatables.net-editor-server" to "^2.0.8" and I have "DataTables Editor v1.9.1-dev" running in the frontend you provided me once.

To investigate I tried to enable await editor.debug(true); but I only get an empty debug: [] ... also did not find the created sql in console.log(editor) on the serverside, but I found an debug: true option on the knex config, which now gives me the created sql statements.

Looking at it I've found the the mjoin always uses the groups_access.id as dtkey also in the mjoin instead of the desired groups_access.user_id

new Mjoin('groups')
.link('groups_access.user_id', 'users_access.user_id')
.link('groups.id', 'users_access.group_id')

That's the users_access table I'm starting with:

##### - 1 (Table - Start)

 user_id    group_id
1   1
1   2
1   12
1   14  
2   1
2   4
3   1
3   4

If I edit the first record in the groups_access table which belongs to the user_id = 1

##### - 1 (Query - Console)

{
  action: 'edit',
  data: {
    row_1: {
      groups_access: [Object],
      groups: [Array],
      'groups-many-count': '5'
    }
  }
}

Then you can see the following:

{
  method: 'del',
  bindings: [ '1' ],
  sql: 'delete from `users_access` where (`user_id` = ?)'
}
{
  method: 'insert',
  bindings: [ '1', '1' ],
  sql: 'insert into `users_access` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '2', '1' ],
  sql: 'insert into `users_access` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '12', '1' ],
  sql: 'insert into `users_access` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '14', '1' ],
  sql: 'insert into `users_access` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '24', '1' ],
  sql: 'insert into `users_access` (`group_id`, `user_id`) values (?, ?)'
}

... and the updated users_access table:

##### - 1 (Table - End)

 user_id    group_id    
2   1
2   4
3   1
3   4
1   1
1   2
1   12
1   14
1   24

If I then edit the 2nd record of the groups_access table which also belongs to user_id = 1

##### - 2 (Query - Console)

{
  action: 'edit',
  data: {
    row_2: {
      groups_access: [Object],
      groups: [Array],
      'groups-many-count': '5',
    }
  }
}

the mjoin uses '2' as user_id instead of as hoped user_id = 1

{
  method: 'del',
  bindings: [ '2' ],
  sql: 'delete from `users_access` where (`user_id` = ?)'
}
{
  method: 'insert',
  bindings: [ '1', '2' ],
  sql: 'insert into `users_access` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '2', '2' ],
  sql: 'insert into `users_access` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '12', '2' ],
  sql: 'insert into `users_access` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '14', '2' ],
  sql: 'insert into `users_access` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '22', '2' ],
  sql: 'insert into `users_access` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '24', '2' ],
  sql: 'insert into `users_access` (`group_id`, `user_id`) values (?, ?)'
}


##### - 2 (Table - End)

 user_id    group_id    
3   1
3   4
1   1
1   2
1   12
1   14
1   24
2   1
2   2
2   12
2   14
2   22
2   24

And another example if I edit record 3 of groups_access

##### - 3 (Query - Console)

{
  action: 'edit',
  data: {
    row_3: {
      groups_access: [Object],
      groups: [Array],
      'groups-many-count': '7',
    }
  }
}

the mjoin uses '3' instead of user_id = 1

{
  method: 'del',
  bindings: [ '3' ],
  sql: 'delete from `users_access` where (`user_id` = ?)'
}
{
  method: 'insert',
  bindings: [ '1', '3' ],
  sql: 'insert into `users_access` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '2', '3' ],
  sql: 'insert into `users_access` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '12', '3' ],
  sql: 'insert into `users_access` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '14', '3' ],
  sql: 'insert into `users_access` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '22', '3' ],
  sql: 'insert into `users_access` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '24', '3' ],
  sql: 'insert into `users_access` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '28', '3' ],
  sql: 'insert into `users_access` (`group_id`, `user_id`) values (?, ?)'
}

##### - 3 (Table - End)

 user_id    group_id    
1   1
1   2
1   12
1   14
1   24
2   1
2   2
2   12
2   14
2   22
2   24
3   1
3   2
3   12
3   14
3   22
3   24
3   28

Do you have any idea what I should try next? :-) I'm open also to use e.g. your https://editor.datatables.net/examples/datatables/nested.html or the https://editor.datatables.net/examples/advanced/parentChild.html if this is more what I need.

Capamania

I've noticed I used the group_id example instead of the limit_id ... please use accordingly, sorry

allan

To investigate I tried to enable await editor.debug(true); but I only get an empty debug: []

Yes - with our Node.js libraries you need to add debug: true to the Knex config as you have done, and it will dump SQL information to the console.

In the Editor Node libraries for Mjoin.js you will find:

            this._join.parent = [ f1[1], f2[1] ];
            this._join.child = [ f3[1], f4[1] ];

in the _prepare function. Could you:

console.log( JSON.stringify(this._join) );

for me and show me the output from that when you do an insert command?

Thanks,
Allan

Capamania

I placed it at the very bottom of the function in mjoin.js

Mjoin.prototype._prepare = function (editor) {

    ...
    
        this._join.parent = [f1[1], f2[1]];
        this._join.child = [f3[1], f4[1]];
    }
    
    console.log( JSON.stringify(this._join) );
};

If I then edit record '2' of groups_access which belongs to user_id = 1 ... I'm gettting:

{"child":["id","group_id"],"parent":["user_id","user_id"],"table":"users_groups"}
{
  method: 'del',
  bindings: [ '2' ],
  sql: 'delete from `users_groups` where (`user_id` = ?)'
}
{"child":["id","group_id"],"parent":["user_id","user_id"],"table":"users_groups"}
{
  method: 'insert',
  bindings: [ '1', '2' ],
  sql: 'insert into `users_groups` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '2', '2' ],
  sql: 'insert into `users_groups` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '12', '2' ],
  sql: 'insert into `users_groups` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '14', '2' ],
  sql: 'insert into `users_groups` (`group_id`, `user_id`) values (?, ?)'
}
{
  method: 'insert',
  bindings: [ '22', '2' ],
  sql: 'insert into `users_groups` (`group_id`, `user_id`) values (?, ?)'
}

And resulting table:

 user_id    group_id    
1   1
1   2
1   12
1   14
3   1
3   4
2   1
2   2
2   12
2   14
2   22

Capamania

To recap the situation, the mjoin works fine if I edit the "users_access" out of the "users" table:

let editor = new Editor( db, 'users', 'id' )
.fields(      
    new Field( 'users.id' )
        ...
)
.join(
  new Mjoin('groups')
    .link('users.id', 'users_access.user_id')
    .link('groups.id', 'users_access.group_id')

What I'm trying to do here is to edit the "users_access" table out of the separate "groups_access" table where 'user_id' is a column value and could be part of multiple 'groups_access.id':

let editor = new Editor( db, 'groups_access', 'id' )
.fields(
      new Field( 'groups_access.id' ),
      new Field( 'users.id' )
    ...
)
.leftJoin('users', 'users.id', '=', 'groups_access.user_id')
.join(
   new Mjoin('groups')
    .link('groups_access.user_id', 'users_access.user_id')
    .link('groups.id', 'users_groups.access_id')

What I did in meantime and works is to call the table "users" edit function out of the "groups_access" table by passing in the the user_id to get the relevant row:

// Edit access permission
var editor_access_permission = $('#groups_access_all').on('click', 'a.edit_access_permission', function (e) {
    e.preventDefault();
    var hostRow = $(this).closest('tr');
    var editRow = hostRow.hasClass('child') ?
        hostRow.prev() :
        hostRow;
    var rowData = table_access.row( editRow ).data();
    editor_users.edit( table_users.rows( '#row_'+rowData.users.id ).indexes(), {
        title: 'Edit',
        buttons: [ ... ]
    });
});

I'm fine with the two steps above but would be sweet if the original attempt in one step also works!

Capamania

The mjoin in the "groups_access" table also provides the values from the 'users_access' table multiple times in the fe response:

{
    "groups": [
        {
            "id": 1,
            "name": "Default"
        },
        {
            "id": 1,
            "name": "Default"
        },
        {
            "id": 2,
            "name": "Admin"
        },
        {
            "id": 2,
            "name": "Admin"
        },
        ...
    ]
}

Capamania

I've found a typo again ... it should be:

.link('groups.id', 'users_access.access_id') instead of .link('groups.id', 'users_groups.access_id')

let editor = new Editor( db, 'groups_access', 'id' )
.fields(
      new Field( 'groups_access.id' ),
      new Field( 'users.id' )
    ...
)
.leftJoin('users', 'users.id', '=', 'groups_access.user_id')
.join(
   new Mjoin('groups')
    .link('groups_access.user_id', 'users_access.user_id')
    .link('groups.id', 'users_access.access_id')