Yup, as I noted at the start, the way Mjoin works is going to cause a problem with any fields which are not are of the link. The way it works is to delete all rows that make the link, and then add new ones for the options selected. It does it this way as a diff between what was and is, could be very difficult, and other fields might be required, which the client-side has no way of supplying for a select list.
Your only option in such as case is to use a nested editor. Have the user create a row per role (or delete, etc).
Yeah I'll have to go that route. I was aware of it deleting everything, but I was hoping to be able to manual supply the data on preedit to overcome it. Doesn't look like I can without the join, and the join messes the display (duplicate rows).
Okay so I'm back at it. Based on suggestions, I've attempted to implement a solution in many different ways nested, joinArray, parent/child, etc. All don't quite work with my table structure/needs.
I'm taking a step back and perhaps come at it from a different angle. Maybe I should have an entirely different Datatable of Roles, and I edit those and assign Users to the Role.
Isn't that what you already have? The issue isn't that - it is the additional data such as IsActive in the Mjoin. The only way I can see to resolve it is to use the OrganizationUser table as the main table for editing and then left join to users.
Apologies I haven't had a chance to type and create an example of this. Its been bonkers busy recently.
The only way I can see to resolve it is to use the OrganizationUser table as the main table for editing and then left join to users.
I had exactly the same situation - and did it the same way as Allan suggests. My solution:
- Custom button on top of the user table "User roles" (active on select of a user)
- on button click: Modal containing a data table for the user organization roles is shown
- Then use the modal Editor to add / change / delete user roles for the respective organization
Looks like this:
This could also be done using field type "datatable", I guess. But that wasn't around when I built this
Yup - this would probably make a good example for the datatable input. The top level table is the user, the nested table (editable) being the OrganizationUser - that would allow the existing structure and for IsActive and friends.
This is perhaps the closest example I have to that at the moment.
I'd be tempted to start by building simple tables that edit just Users, and then another page that edits just OrganizationUser. Then the two can be merged.
@rf1234 That is basically the direction I was going to try to go in. @allan
This is perhaps the closest example I have to that at the moment.
This is the circle I keep going in. In the process of implementing that single selection, I realized I needed multiple selection, the issue with the multiple selection example is that removing a "Role" (Permission in example) removes that for everyone not the user being edited in. The rest of the examples all bump up against Mjoin issue and Mjoin not supporting compound key, etc.
I'm actually going to have a discussion this morning about the business value of having all that extra junk in the join table. If I can purge that it would be easy peazy.
I'm actually going to have a discussion this morning about the business value of having all that extra junk in the join table. If I can purge that it would be easy peazy.
Honestly I was in a similar situation when I got started with field type "datatables". For me this was all about parent - child editing. And I was trying to make that work. Took me a while, but eventually it worked.
This Editor is a bit extreme: It has three fields of type "datatable" and hardly anything else. What does it do?
You select a "contract" (Maßnahme) and then take note of "funds calls" (Mittelabrufe), "proofs of usage" (Nachweise) and other stuff called "Zweckbindungsfristen". This is just 3 times parent child editing with many attributes.
It is a bit of code - and I don't use .NET server side but I'd be willing to share.
What do you need for this?
- The parent Editor containing the the 3 fields of type "datatable"
- Three child Editors for each field of type "datatable"
- Some rendering if you want to show the results of all of this in the parent datatable
Okay, so I've killed the extra columns. I'm now looking at:
I've literally copy/pasted the example from joinArray (permissions). And changed values to match my tables.
.LeftJoin("OrganizationUsers", "OrganizationUsers.UserId", "=", "Users.Id")
.MJoin(new MJoin("Roles")
.Link("OrganizationUsers.Id", "OrganizationUserRoles.OrganizationUserId")
.Link("Roles.Id", "OrganizationUserRoles.RoleId")
.Model<OrganizationUserRolesViewModel>()
.Order("Roles.Name")
.Validator("Roles[].Id", Validation.MjoinMaxCount(4, "No more than four selections please"))
.Field(new Field("Id")
.Options(new Options()
.Table("Roles")
.Value("Id")
.Label("Name")
)
))
The OrganizationUserRolesViewModel is a replication of JoinAccessModel from the joinArray example.
However, I get
System.NullReferenceException: Object reference not set to an instance of an object.
at DataTables.MJoin.Data(Editor editor, DtResponse response) in /home/vagrant/DataTablesSrc/extensions/Editor-NET/DataTables-Editor-Server/MJoin.cs:line 407
at DataTables.Editor._Get(Object id, DtRequest http) in /home/vagrant/DataTablesSrc/extensions/Editor-NET/DataTables-Editor-Server/Editor.cs:line 1514
at DataTables.Editor._Process(DtRequest data) in /home/vagrant/DataTablesSrc/extensions/Editor-NET/DataTables-Editor-Server/Editor.cs:line 1219
at DataTables.Editor.Process(DtRequest data) in /home/vagrant/DataTablesSrc/extensions/Editor-NET/DataTables-Editor-Server/Editor.cs:line 890
at DataTables.Editor.Process(HttpRequest request, String culture) in /home/vagrant/DataTablesSrc/extensions/Editor-NET/DataTables-Editor-Server/Editor.cs:line 968
I've looked up various questions on the forum and I didn't find anything helpful, I'm hoping I'm over looking something simple now, but I've been trying various examples for the last hour and it keeps throwing that error.
EDIT: I'd like to add the rest of the Editor is the same as pasted before, and if I comment out the Mjoin, it loads the datatable/editor just fine.
I fear you've run straight into a limitation in the Mjoin here. This is the condition that is causing the error - specifically the Mjoin class is assuming that the parent table will be the table being used by the Editor instance (presumably you have that setup to be Users in this case). Because it doesn't find a match _hostField is never set and thus the error you are seeing.
I probably need to change the code there to consider all tables that are registered at the top level (i.e. add in the left joined tables). There are a few other things that need to be updated as well and I'm wondering if there might be edge cases whereby there might be a name clash. I don't immediately thing so, but I'll ponder it a bit. I'll look at how this might be implemented.
I've gone ahead an prototyped this and built the dll for it. My data set isn't set up to test this at the moment (the top level example works as it should with the new code), but if you'd like to give this a go, I'll drop you a private message with an address for the new dll. I'd certainly welcome feedback on this new feature if you are willing to give it a spin, and hopefully we can get it in for Editor 2.4.
Answers
Yup, as I noted at the start, the way Mjoin works is going to cause a problem with any fields which are not are of the link. The way it works is to delete all rows that make the link, and then add new ones for the options selected. It does it this way as a diff between what was and is, could be very difficult, and other fields might be required, which the client-side has no way of supplying for a select list.
Your only option in such as case is to use a nested editor. Have the user create a row per role (or delete, etc).
Allan
Yeah I'll have to go that route. I was aware of it deleting everything, but I was hoping to be able to manual supply the data on preedit to overcome it. Doesn't look like I can without the join, and the join messes the display (duplicate rows).
Thanks for all the help.
Okay so I'm back at it. Based on suggestions, I've attempted to implement a solution in many different ways nested, joinArray, parent/child, etc. All don't quite work with my table structure/needs.
I'm taking a step back and perhaps come at it from a different angle. Maybe I should have an entirely different Datatable of Roles, and I edit those and assign Users to the Role.
Isn't that what you already have? The issue isn't that - it is the additional data such as
IsActivein the Mjoin. The only way I can see to resolve it is to use theOrganizationUsertable as the main table for editing and then left join to users.Apologies I haven't had a chance to type and create an example of this. Its been bonkers busy recently.
Allan
I had exactly the same situation - and did it the same way as Allan suggests. My solution:
- Custom button on top of the user table "User roles" (active on select of a user)
- on button click: Modal containing a data table for the user organization roles is shown
- Then use the modal Editor to add / change / delete user roles for the respective organization
Looks like this:
This could also be done using field type "datatable", I guess. But that wasn't around when I built this
Yup - this would probably make a good example for the
datatableinput. The top level table is the user, the nested table (editable) being theOrganizationUser- that would allow the existing structure and forIsActiveand friends.This is perhaps the closest example I have to that at the moment.
I'd be tempted to start by building simple tables that edit just
Users, and then another page that edits justOrganizationUser. Then the two can be merged.Allan
@rf1234 That is basically the direction I was going to try to go in.
@allan
This is the circle I keep going in. In the process of implementing that single selection, I realized I needed multiple selection, the issue with the multiple selection example is that removing a "Role" (Permission in example) removes that for everyone not the user being edited in. The rest of the examples all bump up against Mjoin issue and Mjoin not supporting compound key, etc.
I'm actually going to have a discussion this morning about the business value of having all that extra junk in the join table. If I can purge that it would be easy peazy.
Honestly I was in a similar situation when I got started with field type "datatables". For me this was all about parent - child editing. And I was trying to make that work. Took me a while, but eventually it worked.
This Editor is a bit extreme: It has three fields of type "datatable" and hardly anything else. What does it do?
You select a "contract" (Maßnahme) and then take note of "funds calls" (Mittelabrufe), "proofs of usage" (Nachweise) and other stuff called "Zweckbindungsfristen". This is just 3 times parent child editing with many attributes.
It is a bit of code - and I don't use .NET server side but I'd be willing to share.
What do you need for this?
- The parent Editor containing the the 3 fields of type "datatable"
- Three child Editors for each field of type "datatable"
- Some rendering if you want to show the results of all of this in the parent datatable
Okay, so I've killed the extra columns. I'm now looking at:
I've literally copy/pasted the example from
joinArray(permissions). And changed values to match my tables.The
OrganizationUserRolesViewModelis a replication ofJoinAccessModelfrom thejoinArrayexample.However, I get
I've looked up various questions on the forum and I didn't find anything helpful, I'm hoping I'm over looking something simple now, but I've been trying various examples for the last hour and it keeps throwing that error.
EDIT: I'd like to add the rest of the Editor is the same as pasted before, and if I comment out the Mjoin, it loads the datatable/editor just fine.
I fear you've run straight into a limitation in the Mjoin here. This is the condition that is causing the error - specifically the
Mjoinclass is assuming that the parent table will be the table being used by the Editor instance (presumably you have that setup to beUsersin this case). Because it doesn't find a match_hostFieldis never set and thus the error you are seeing.I probably need to change the code there to consider all tables that are registered at the top level (i.e. add in the left joined tables). There are a few other things that need to be updated as well and I'm wondering if there might be edge cases whereby there might be a name clash. I don't immediately thing so, but I'll ponder it a bit. I'll look at how this might be implemented.
Allan
I've gone ahead an prototyped this and built the dll for it. My data set isn't set up to test this at the moment (the top level example works as it should with the new code), but if you'd like to give this a go, I'll drop you a private message with an address for the new dll. I'd certainly welcome feedback on this new feature if you are willing to give it a spin, and hopefully we can get it in for Editor 2.4.
Allan