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

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

CapamaniaCapamania Posts: 223Questions: 79Answers: 5
edited July 25 in Editor

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.

Answers

  • CapamaniaCapamania Posts: 223Questions: 79Answers: 5

    I'm using Editor 1.9.2

  • allanallan Posts: 56,837Questions: 1Answers: 9,034 Site admin

    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

  • allanallan Posts: 56,837Questions: 1Answers: 9,034 Site admin

    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

Sign In or Register to comment.