.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?

wadeparallon

UI wise, I think this is the closest to what I want...

https://editor.datatables.net/examples/datatables/parentChild.html

Basically working on this to have an entirely new editor/datatable for the Roles here..

Now I just want to avoid having to select a row and click edit.. I really want to just click the Edit button and it pop a MULTISELECT. Like I don't want to edit individual roles. I just want to add and remove existing Roles from the OrgUser.

allan

Like I don't want to edit individual roles. I just want to add and remove existing Roles from the OrgUser.

That sounds a lot like this? That example uses checkboxes, but it could readily use a select set for multiple values, or a datatable.

This would update only OrganizationUsers right? You can use Field->setValue(...) for the fields to set the values such as update time, etc. But the isActive is going to be difficult. There is no concept in the field of a row being present, but not active. Either it is checked, and therefore in the table, or it is not checked and therefore not in the link table.

Allan

wadeparallon

That is what I was trying originally. Think of my problem like this:

The query returning duplicate rows for each role.. Mjoin was going to blow away extra data in table (which I'm fine with resetting values if needed). That is why we landed on the Parent/Child/nested editor.

So instead we are trying to have:
Parent Editor 1
User -> UserOrg data
Child Editor 2
UserOrg -> UserOrgRoles (-> Roles is the multiselect data)

But Child Editor makes me select a role then click edit where I just want multiselect capabilities.

wadeparallon

This is what I'm saying. Basically I want the "Edit Entry" to be the child row. The UX of expanding a child row to select a single row in the child to then half to click Edit to multiselect ALL the roles, feels wonky.

rf1234

All you need is a single select field for "user's organization" and then a multi select field for "user's roles in the selected organization". Can the user really have multiple roles in one organization? Or is it actually only a single role per organization? If the latter is true you only need two single select fields.

The options for "user's organization" probably need to be read from the server because they are dynamic.

Are the "user's roles in the selected organization" also dynamic? If they are static, e.g. "VP, Director, Client Service Rep, etc." it is really easy: You just define those options locally client side. If these options are dynamic and deviate for each organization you will need to retrieve them from the server and update the options locally.

If the catalogue of roles deviates per organization but is not dynamic, you can also define those options locally client side and then update the options depending on user selection.

So what is your use case here?

wadeparallon

Users
Organzations
OrgUser (User can be part of many Orgs)
Within each Organization, they have specific roles.
Roles: Enum table of values. These are global roles across organizations (Business Owner, Product Owner.)

Roles are dynamic in a sense that we can add new ones via the admin at any time and Users drop and add roles yearly.

I had the form displaying (see above) but the issue was the returned data being duplicated per Role in the datatable.. so the suggestion was Parent/Child editor to move the Role editing to a Child editor.
I'm realizing that the Parent/Child editor makes the Role modification process strange.

Open to suggestions since I feel we've come full circle to the original problem again.

rf1234

Ok, then I assume the roles need to be fetched and updated dynamically:

Just use a single select field for "user's organization" and get the options from the server. Please note: if you are getting the options from the server using an options instance you don't need to specify the options again in the "select" field definition. The "select" field will use the options read from the server - if there are any.

And use a multi select field for "user's role in the selected organization"

Then using "dependent" make an ajax call to fetch the available user's roles for the multi select field and populate the options accordingly using the select field's "update" method.

https://editor.datatables.net/reference/field/select

https://editor.datatables.net/reference/api/dependent()

"dependent" could look something like this:

editor
   .dependent('selectedUserOrganziationId', function ( val, data, callback ) {
        $.ajax({
            type: "POST",
            url: 'getTheOptionsForMultiselect',
            data: {
                id: val
            },
            success: function (options) {  //label - value pairs
                editor.field("userRolesSelectedOrganization").update(options);
                callback({});
            }
        });
    });

wadeparallon

That is an interesting option. Right now the Org is already filtered by the Where clause in the Editor (where orgid = id). Can Dependent() be fired on a disabled field value (not on change) basically a read only field?

rf1234

Can Dependent() be fired on a disabled field value (not on change) basically a read only field?

No, I don't think so and I don't understand why you should want that.

You can also replace the above "dependent" statement like this and use different events if you don't like the "change" event.

$( editor.field('selectedUserOrganziationId').node() ).change(function() {
    ... do something
});
// trigger it initially just like dependent is triggered on opening the form
$( self.field('selectedUserOrganziationId').node() ).change();

allan

User organisation - if a user can be part of multiple organisations, then that one needs to be an Mjoin. Presumably then they have have multiple roles per organisation?

That's where I think the nested editing needs to come in. You need the child table to show the list of organisations. Then when you edit rows there, then that's where you need a second Mjoin to show the user roles.

I'm unfortunately very tight for time this week or I'd try to create an example, sorry.

Allan

rf1234

I think you don't even need nested editing for this use case:
You just select an organization with a simple single select fied.
"Dependent" on that selection you load the options for the multi select that is further down the form. I have quite a few use cases like this but I use "selectize" for this where I can't simply update the options. I "clear" the selectize fields and "add" them back in with the updated options.

Here is an example using "selectize":
I have different options for creation and editing. When editing the options depend on the contract id. I save the options in a global variable because I also need them for other purposes (e.g. column rendering in my data table)

This is just the "create" case but it is very similar for "edit"; the main difference being that the passed "ctr_id" is not 0 but the id of the record edited. For performance reasons I also update the options on "select" of the record to be edited in the data table; I don't wait for "initEdit".

var ctrGovdeptOptions = [];

editor
    .on ( 'initCreate', function ( e ) {
        $.ajax({
            type: "POST",
            url: 'actions.php?action=getCtrGovdeptOptions',
            data: {
    //create: we submit zero to avoid departments, categories etc. being kept from selected records
                ctr_id: 0
            },
            dataType: "json",
            success: function (data) {   
                ctrGovdeptOptions = data.options;
                subEditor.clear( "ctr_govdept[].id" ); 
                subEditor.add( {
                    label: lang === 'de' ? 'Abteilungsauswahl:' : 'Department selection:',
                    name: "ctr_govdept[].id", 
                    type: "selectize",
                    options: ctrGovdeptOptions,
                    opts: {
                        create: false,
                        maxItems: null,
                        openOnFocus: true,
                        allowEmptyOption: false,
                        placeholder: lang === 'de' ? 'Bitte wählen Sie eine oder mehrere Abteilungen' : 'Please select one or more departments',
                    }
                }, "sub.sub_partner_id" );
            }
        });

allan

You just select an organization with a simple single select fied.

If a user can be part of multiple organisations, that would mean you need to show one row for each user / organisation group. I'd been thinking a single row per user, and then the list of organisations for that user would be part of the form.

Either way could work nicely I think. Indeed, @rf1234's solution might be nicer when considering the isActive and other fields.

Allan

rf1234

Many roads lead to Rome

Good luck!

wadeparallon

I appreciate the brain storming around this. Just to summarize:

For the context of this editor the Users datatable is already filtered to only show users for that organization (see where clause). This is passed in by the organization id on the URL example: /organizations/2/users, so there will be no selection of organization on user edit. We will only be editing the OrgUser -> OrgUserRoles in this case (Parent User data is readonly/disabled on editor). The options for the multiselect comes from the Roles table.

I had the editor popping the multiselect but the issue was the Users datatable showed a row for each Role the user was in. I'm okay with wiping and re-adding extra field data in OrgUserRoles.

Users can be in multiple orgs, but in the context of this editor it is only editing this organization's user.

rf1234

so there will be no selection of organization on user edit.

Then it is very simple: Just a multi "select" field

but the issue was the Users datatable showed a row for each Role the user was in.

Yes, and that is caused by your left joins above. At least one of them has to go. See my 2nd comment above.

wadeparallon

At least one of them has to go. See my 2nd comment above.

I thought the discussion was how to achieve that, ie by doing a Parent/child editor I won't need the OrgUsersRole join, that'll be in the 2nd editor, etc.

I can't see how remove a join with killing the contents I need to show/edit atm.

I'll re-read this thread and go over the examples again. I may have gotten lost in the weeds.

rf1234

Sorry, I am lost. I'm afraid, I can't help you. Please accept my apologies.

allan

Let's go back a bit. The OrganizationUserRole is not a one-to-one link - it is one-to-many. Therefore it should not be included as a LeftJoin, but rather only as an Mjoin.

To do that drop:

 .Model<OrganizationUserRole>("OrganizationUserRoles")

and

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

Resulting in:

var editor = new Editor(db, "Users", "Id")
            .Model<User>("Users")
            .Model<OrganizationUser>("OrganizationUsers")
            .Field(new Field("Users.Id").Set(false))
 
....
 
            .LeftJoin("OrganizationUsers", "OrganizationUsers.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 suspect when you tried to remove the left joins before, you didn't remove the model, and that is what generated the error?

In the DataTable, do you have any columns that refer to OrganizationUserRole as if it were a left join? I see the Editor init code above, but not the DataTable.

Allan

wadeparallon

Here is my datatable:

const datatable = new DataTable("#UsersDatatable", {
        ajax: {
            url: '/Organizations/'+tableData.dataset.organizationid+'/Users/Editor',
            type: "GET",
            dataType: "JSON"
        },
        order: [2, "asc"],
        columns: [
            {
                data: null,
                orderable: false,
                searchable: false,
                render: DataTable.render.select(),
                width: '40px'
            },
            {
                data: "Users.Id",
                title: "Id",
                visible: false,
                searchable: false
            },
            {
                data: "Users.FullName",
                title: "Name",
            },
            {
                data: "Users.DomainId",
                title: "3-4"
            },
            {
                title: 'Role',
                data: 'Roles',
                render:  "[, ].Name"
            },
            {
                data: "OrganizationUsers.IsAdmin",
                title: "Organization Admin",
                searchable: false,
                render: function (data, type, row, meta) {
                    if (type === 'display') {
                        if (data === true) {
                            return '<i class="bi bi-eye-fill"></i>';
                        } else {
                            return '<i class="bi bi-eye-slash-fill"></i>';
                        }
                    }else{
                        return data;
                    }
                }
            },
            {
                data: "OrganizationUsers.IsActive",
                title: "Active",
                searchable: false,
                render: function (data, type, row, meta) {
                    if (type === 'display') {
                        if (data === true) {
                            return '<i class="bi bi-eye-fill"></i>';
                        } else {
                            return '<i class="bi bi-eye-slash-fill"></i>';
                        }
                    }else{
                        return data;
                    }
                }
            },
        ],
        "initComplete": function () {
            $(this).css("opacity", 1);
        },
        layout: {
            topStart: {
                buttons: [
                   { extend: 'edit', editor: editor }
                ]
            }
        },
        select: {
            style: 'single',
            selector: 'td:first-child'
        }
    });

It shows, the "Role1,Role2, Role3" fine but 3 rows for the user.

You're right I don't show anything for OrganizationUserRoles, but I needed the model for the Link on the Mjoin, if I remove the model, it throws an error for multipart OrganizationUserRoles not found.

wadeparallon

Actually.. this works!

.LeftJoin("OrganizationUsers", "OrganizationUsers.UserId", "=", "Users.Id")
            .MJoin(new MJoin("Roles")
                .Model<OrganizationUserRole>()
                .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 moved the OrganizationUserRole to the Mjoin (didn't realize that was possible for some reason).

Now datatables shows single user. Editor still shows all Roles in multiselect.

Now I just need to set all the values I need in the edit (user's can't be created here). Because mjoin is going to blow away those extra fields.

wadeparallon

That's probably going to be the rub here. If I don't do anything it throws that it can't be null on edit, but when I try to set it