Multiple Aliased MJoins, only last positioned Mjoin in .NET api Controller works to insert data

Jorm

Hello,

Using: .Net; Editor 1.7.4; Datatables 1.10.18

I'm trying to implement multiple MJoins, aliased, so I can apply a different '.Where' filter in each instance. I'm doing this so I can display a long list of checkboxes (>120) separated into different collapsible frames on an .html page, based on a parent grouping.

I'm getting the correct filtered data from each MJoin statement (in my .html page, my collapsible frames in the Editor template modal show the correct checkboxes per grouping), but it's only interactions associated with data from the last MJoin statement that result in a successful database transaction (insert, delete). Clicking/unclicking checkboxes associated with any of the other MJoins does nothing.

I've been unable to find many examples in .Net implementing use of multiple aliased MJoins, so I feel like I'm kind of making things up, which I'm sure explains why it's not working!

In the below:

1) I'm intending to use information from tables 'Projects' and 'TradeTaskTypes' and insert into 'ProjectTaskLists'.
2) I'm getting checkbox data filtered appropriately for MJoins aliased 'Controls' and 'ServiceConstruction'
3) Only interactions with 'ServiceConstruction' (last in sequence of MJoins...) data result in a successful insert/delete on table
'ProjectTaskList'
4) If I were to delete the second MJoin, insert/deletes would work for 'Controls' data
5) If I were to add another MJoin, named 'Engineering' for e.g., insert/deletes would only work for 'Engineering' (last in
sequence of MJoins), not 'Controls' or 'ServiceConstruction'.

I think I might be able to implement use of database views to get this to work, if the MJoins won't (I hope I hope!).

Here is relevant code:

  using (var db = new Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, "Projects", "PrjDBid")
                    .Debug(true)
                    .Model<Projects>("Projects")

                .MJoin(new MJoin("TradeTaskTypes")
                        .Name("Controls")
                        .Link("Projects.PrjDBid", "ProjectTaskLists.PtlPrjID")
                        .Link("TradeTaskTypes.TttDBid", "ProjectTaskLists.PtlTttID")
                        .Model<TradeTaskTypes>()
                        .Field(new Field("TttDBid")
                            .Options(new Options()
                            .Table("TradeTaskTypes")
                            .Value("TttDBid")
                            .Label("TttName")
                            .Where(q => q.Where("TttTrdID", 1))
                            )))

                .MJoin(new MJoin("TradeTaskTypes")
                    .Name("ServiceConstruction")
                    .Link("Projects.PrjDBid", "ProjectTaskLists.PtlPrjID")
                    .Link("TradeTaskTypes.TttDBid", "ProjectTaskLists.PtlTttID")
                    .Model<TradeTaskTypes>()
                    .Field(new Field("TttDBid")                     
                        .Options(new Options()
                        .Table("TradeTaskTypes")
                        .Value("TttDBid")
                        .Label("TttName")
                        .Where(q => q.Where("TttTrdID", 2))
                        )))

Here's relevant stuff from the .html page:

 var editor = new $.fn.dataTable.Editor({
                
                    ajax: '/api/projecttasklist',
                    table: '#ProjectTasks',
                    template: "#customForm",
                    fields: [
                        {
                            label: "Project Number:",
                            name: "Projects.PrjNumber",        
                        }
                        , {
                            "label": "Project Name:",
                            "name": "Projects.PrjName",
                        }                       
                        , {
                            label: "" //"Controls Tasks"
                            , name: "Controls[].TttDBid"
                            , type: "checkbox"
                        }
                        , {
                            label: "" //"Service Construction Tasks"
                            , name: "ServiceConstruction[].TttDBid"
                            , type: "checkbox"
                        }]
                });


 $('#ProjectTasks').DataTable({
                    dom: "Bfrtip",
                    ajax: {
                        url: "/api/projecttasklist",
                        type: 'POST'
                    },
                    columns: [
                        { data: "Projects.PrjNumber" }
                        , { data: "Projects.PrjName" }                  
                       ],
                    select: true,
                    buttons: [                       
                        { extend: "edit", editor: editor }                      
                    ]
                });

                              

---------EDITOR TEMPLATE:

     <editor-field name="Controls[].TttDBid">                 
     <editor-field name="ServiceConstruction[].TttDBid">

allan

The problem here is that Editor's libraries aren't applying the WHERE condition on the DELETE statement for the link table. So it will just "nuke" all existing links it has between the parent and the child, including those that it has just created. So the links do exist for a moment and then are deleted by the second Mjoin update.

The only way around this at the moment is to use a second link table (i.e. one for each set of joins).

I did have a look at how to resolve this recently, but it was going to take a significant amount of work, so I've shunted it down a release or two (not yet certain) I'm afraid.

This is something I'd like to resolve though and its in our bug tracker, but for the moment the workaround of two link tables would be needed.

Allan

Jorm

Thank you Allan! That was an easy work around and it's working great....

For those that may be new to this like me, here's the change I needed to make it work - I am managing 'ProjectTaskLists' info in tables specific to the '.Where' categories I'm implementing (i.e. ProjectTaskLists_Controls, ProjectTaskLists_ServiceConstruction...):

 .MJoin(new MJoin("TradeTaskTypes")
       .Name("Controls")
       .Link("Projects.PrjDBid", "ProjectTaskLists_Controls.PtlPrjID")
       .Link("TradeTaskTypes.TttDBid", "ProjectTaskLists_Controls.PtlTttID")
       .Model<TradeTaskTypes>()
       .Field(new Field("TttDBid")
            .Options(new Options()
            .Table("TradeTaskTypes")
            .Value("TttDBid")
            .Label("TttName")
            .Where(q => q.Where("TttTrdID", 1))
        )))

 .MJoin(new MJoin("TradeTaskTypes")
        .Name("ServiceConstruction")
        .Link("Projects.PrjDBid", "ProjectTaskLists_ServiceConstruction.PtlPrjID")
        .Link("TradeTaskTypes.TttDBid", "ProjectTaskLists_ServiceConstruction.PtlTttID")
        .Model<TradeTaskTypes>()
        .Field(new Field("TttDBid")                     
             .Options(new Options()
             .Table("TradeTaskTypes")
             .Value("TttDBid")
             .Label("TttName")
             .Where(q => q.Where("TttTrdID", 2))
         )))