.NET Mjoin, LeftJoin, and Multiselect

wadeparallon

Description of problem:

I have basically what I'd describe as a One to Many to Many. Users -> UserOrg -> UserOrgRoles -> Roles. Users can be in Many Organizations and Many Roles inside those Organizations.

I have the Editor essentially setup like this (cut down for brevity):

var editor = new Editor(db, "Users", "Id")
            .Model<User>("Users")
            .Model<OrganizationUser>("OrganizationUsers")
            .Model<OrganizationUserRole>("OrganizationUserRoles")
            .Field(new Field("Users.Id").Set(false))

....

            .LeftJoin("OrganizationUsers", "OrganizationUsers.UserId", "=", "Users.Id")
            .LeftJoin("OrganizationUserRoles", "OrganizationUserRoles.UserId", "=", "Users.Id")
            .MJoin(new MJoin("Roles")
                .Link("Users.Id", "OrganizationUserRoles.UserId")
                .Link("Roles.Id", "OrganizationUserRoles.RoleId")
                .Model<Role>()
                .Order("Roles.Name")
                .Field(new Field("Id")
                    .Options(new Options()
                        .Table("Roles")
                        .Value("Id")
                        .Label("Name")
                    )
                )
            ).Where("OrganizationUsers.OrganizationId", id);

I got everything working (I haven't tried create/edit data) except for the fact that the Datatable shows duplicate rows, basically a row for every Role the Users are in:

This person being in 4 Roles for the Organizaiton

Clicking Edit on the record selects all the Roles appropriately

Do I need to do a 2nd MJoin? I noticed someone else asked about GroupBy in the forum and it was said it wasn't supported, but has this changed since then?

rf1234

I noticed someone else asked about GroupBy in the forum and it was said it wasn't supported, but has this changed since then?

Probably not, because you can't really edit this anyway. But you can create and use a (non-updatable) view that uses "group by". No problem.

allan

Exactly that. GROUP BY makes the editing aspect a lot more difficult.

It sounds like you are doing something like this.

I'm trying to work out the database structure here - do you need the left join to OrganizationUserRoles if that is your link table for the Mjoin? Or indeed OrganizationUsers? Could you show me the full controller code and ideally if you have an image showing the database structure and references, that would be useful.

Allan

rf1234

The redundance is caused by the left joins

.LeftJoin("OrganizationUsers", "OrganizationUsers.UserId", "=", "Users.Id")
.LeftJoin("OrganizationUserRoles", "OrganizationUserRoles.UserId", "=", "Users.Id")

This means that one row is retrieved for every organization the user is assigned to AND every role the user has for the respective organization.

If you want just one row for each user you need to get rid of both left joins and retrieve the details with an Mjoin as suggested in Allan's example (see Permissions column).
If a user can only be assigned to one organization, you can keep the first left join.

In a way a data table with an Mjoin works similar to "group by": The grouping parameters are the fields of the data table, and the group content is what you return from the Mjoin.

wadeparallon

^ relooking at that I might be able to skip the OrgUser join and go directly to the OrgUserRole join.

@allan that is the example I was looking at in the .NET package. Its exactly what I am needing but instead of permissions its Roles... except its deeper in the DB structure.

I'm trying to edit User data which Roles are set PER Organization the User is apart of.

I setup the left joins thinking I needed to let Editor know about them so it could set data fields appropriately on edit/create.

Good points on the Group By being non-editable.

I'll see about removing some left joins.

wadeparallon

Here is the full Editor.. I tried removing the Left Joins but get error messages that I can't remove them because it needs to know about them.

1. I'm setting values on OrgUser and OrgUserRole and User.

var editor = new Editor(db, "Users", "Id")
            .Model<User>("Users")
            .Model<OrganizationUser>("OrganizationUsers")
            .Model<OrganizationUserRole>("OrganizationUserRoles")
            .Field(new Field("Users.Id").Set(false))
            .Field(new Field("Users.FullName")
                .Validator(Validation.NotEmpty()))
            .Field(new Field("Users.DomainId").Set(false)
                .GetFormatter( Format.IfEmpty( null ))
            ).Field(new Field("OrganizationUsers.IsAdmin")
                .Validator(Validation.Boolean())
            ).Field(new Field("OrganizationUsers.IsActive")
                .Validator(Validation.Boolean())
            ).Field(new Field("Users.PreferredName")
                .Validator(Validation.MaxLen(50, new ValidationOpts(){ Message = "Must be less than 50 characters."}))
                .SetFormatter( Format.IfEmpty( null ))
                .GetFormatter( Format.IfEmpty( null ))
            ).Field(new Field("Users.CreatedById")
                .Get(false)
                .Set(Field.SetType.Create)
            ).Field(new Field("Users.CreateDate")
                .Get(false)
                .Set(Field.SetType.Create)
            ).Field(new Field("Users.ModifiedById")
                .Get(false)
                .Set(Field.SetType.Both)
            ).Field(new Field("Users.ModifyDate")
                .Get(false)
                .Set(Field.SetType.Both)
            )
            .LeftJoin("OrganizationUsers", "OrganizationUsers.UserId", "=", "Users.Id")
            .LeftJoin("OrganizationUserRoles", "OrganizationUserRoles.UserId", "=", "Users.Id")
            .MJoin(new MJoin("Roles")
                .Link("Users.Id", "OrganizationUserRoles.UserId")
                .Link("Roles.Id", "OrganizationUserRoles.RoleId")
                .Model<Role>()
                .Order("Roles.Name")
                .Field(new Field("Id")
                    .Options(new Options()
                        .Table("Roles")
                        .Value("Id")
                        .Label("Name")
                    )
                )
            ).Where("OrganizationUsers.OrganizationId", id);

allan

Are those the only three fields that you are showing in the form? I presume you are showing others in the DataTable? Which ones?

The problem you are going to run into here is that your junction (link) table has more information in it than just the junction of users and roles. The example I linked to has a user_permission table which is simply two columns.

The way Mjoin works in Editor is that it will delete the records in the junction table and then add them again. This is fine for simple links, but if you have extra data, such as OrganizationUserRoles.isActive, it simply doesn't work since that data would be lost.

The way to handle such a case is with a nested editor. You could still use Mjoin to show the multiple rows for a user (and perhaps we should focus on that first), using ->set(false) to make sure it doesn't attempt to write to the child table. Then have a child editor for OrganizationuserRoles (i.e. as if it were a main table, doing that it might be best to add its own primary key). A lot of the values will be set by the API (userId and CreatedById for example) but I think that should be possible. I don't have an example of that I can just link to though unfortunately.

Allan

wadeparallon

Yeah I'm showing mostly User data, but those 3 are the ones being edited.

I have a PreCreate/PreEdit that sets the CreatedBy/ModifiedBy.

I'll look into a nest editor?.. by that do you mean like this? https://editor.datatables.net/examples/datatables/mJoin.html

Using a datatable in the editor?

Speaking of focusing on data output first.... I had a thought of modifying the response.data and de-duping the values to return... but that seems hacky.

wadeparallon

Here is my Editor.js

const editor = new DataTable.Editor({
        ajax: '/Organizations/'+tableData.dataset.organizationid+'/Users/Editor',
        idSrc:  'Users.Id',
        fields: [
            {
                label: 'Name',
                name: 'Users.FullName',
                type: 'readonly',
                attr: {
                    disabled: 'disabled'
                }
            },
            {
                label: '3-4',
                name: 'Users.DomainId',
                type: 'readonly',
                attr: {
                   disabled: 'disabled'
                }
            },
            {
                label: 'Email',
                name: 'Users.Email',
                type: 'readonly',
                attr: {
                    disabled: 'disabled'
                }
            },
            {
                label: 'Display Name',
                name: 'Users.PreferredName',

            },
            {
                label: 'Role',
                name: 'Roles[].Id',
                type: 'select',
                multiple: true

            },
            {
                label: 'Active',
                name: 'OrganizationUsers.IsActive',
                type: 'checkbox',
                options: [{ label: "", value: 1}],
                separator: "",
                unselectedValue: 0

            }
        ],
        table: '#UsersDatatable'
    });

wadeparallon

Actually I think you were referring to this:
https://datatables.net/blog/2019/parent-child-editing-in-child-rows

So I can have a user parent table, the child datatable/row can just be a datatable of all the roles, the editor would just pop up to a single multiselect?