How to insert into many-to-many table

How to insert into many-to-many table

georgemcflygeorgemcfly Posts: 18Questions: 4Answers: 0

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")

Answers

  • guidolsguidols Posts: 38Questions: 14Answers: 1

    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();
    
  • allanallan Posts: 63,498Questions: 1Answers: 10,470 Site admin

    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

  • georgemcflygeorgemcfly Posts: 18Questions: 4Answers: 0

    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.

  • georgemcflygeorgemcfly Posts: 18Questions: 4Answers: 0

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

  • georgemcflygeorgemcfly Posts: 18Questions: 4Answers: 0

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

  • allanallan Posts: 63,498Questions: 1Answers: 10,470 Site admin

    Super - thanks for the update!

    Allan

Sign In or Register to comment.