How to insert into many-to-many table

georgemcfly

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:
I have 3 tables: Users, Roles and UserRoles.
UserRoles contains UserId and RoleId to allow a user to have multiple roles.
When adding a new user, the editor debuginfo shows an Update statement for UserRoles rather than an Insert statement.
I'm using C#. How do I define my editor fields to make this work? I'm using LeftJoin. Do I have to user MJoin to make this work as expected?

var editor = new Editor(db, "Users", "Id")
.Field(new DataTables.Field("Users.Id", "Id"))
.Field(new DataTables.Field("Users.UserName", "UserName"))
.Field(new DataTables.Field("OvercastUser.Roles.RoleName", "Role"))
.Field(new DataTables.Field("OvercastUser.OvercastUserRoles.RoleId", "RoleId")
.Options(new Options()
.Table("Roles")
.Value("Id")
.Label("RoleName")))
.LeftJoin("UserRoles",
"UserRoles.UserId", "=", "Users.Id")
.LeftJoin("Roles",
"Roles.Id", "=", "UserRoles.RoleId")

guidols

Hi,
this is my code in C# (every project can have multiple operators):

var response = new Editor(db, "Projects", "Projects.Id")
    .Model<EditProject>("Projects")
    .Field(new Field("Id").Set(false))
    .MJoin(new MJoin("Operators")
        .Link("Projects.Id", "ProjectOperators.Project_Id")
        .Link("Operators.Id", "ProjectOperators.Operator_Id")
        .Model<JoinedOperator>()
        .Order("Operators.UserName")
        .Field(new Field("Id")
            .Options(new Options()
                .Table("Operators")
                .Value("Id")
                .Label("UserName")
            )
        )
    )
    .Process(request)
    .Data();

allan

Hi,

As @guidols's code suggests, you need to use the Mjoin class if you are using a one-to-many linking (junction) table. Documentation for that with .NET is available here.

With that you should expect to see a DELETE operation on the UserRoles followed by an INSERT whenever you perform a change to the roles.

Regards,
Allan

georgemcfly

So what would the javascript side of this look like? How do I display this in an editor window? I need to be able to select multiple roles per user.

georgemcfly

@guidols: It would help to see your model classes. There are several references to "Id" but it is not clear which class the Id belongs to.
Can we see the definitions for EditProject and JoinedOperator?

georgemcfly

@guidols, @allan: Finally got it! Thanks for your help!

allan

Super - thanks for the update!

Allan