Combining MJoin and c# List to limit records shown to ones in List

Combining MJoin and c# List to limit records shown to ones in List

dynasoftdynasoft Posts: 422Questions: 67Answers: 3

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:
I have server and js code shown below. I wish to only show on the editor form the records that are stored in List MyViewModel. how can i combine this? i tried doing a check in the render part of the MJoin's Options but I simply get a check box with null against it in the client. I'm ok doing the check either in the server or client code. Many thanks.

  1. Server code:
public DtResponse CRUDGlobalP()
{
    MyModel DM = new MyModel();
    MyViewModel MyViewModel = DM.GetMyListViewModel();
    DM = null;

    HttpRequest formData = HttpContext.Current.Request;

    using (Database db = new Database(SetGetDbType2, SetGetDbConnection))
    {
        editor = new Editor(db, "GlobalSP", "GlobalSP.id").Model<SPDBModel.GlobalSP>("GlobalSP");
        editor.Field(new Field("GlobalSP.id")
            .Set(false)
        );
        editor.MJoin(new MJoin("DD")
        .Model<SPDBModel.DD>()
        .Name("GlobalPDD0")
        .Link("GlobalSP.id", "GlobalSPRelations.SpID")
        .Link("DD.id", "GlobalSPRelations.ParamID")
        .Where(q =>
            q.Where("GlobalSPRelations.ParamType", "4", "=")
        )
        .Order("DD.id ASC")
        .Field(new Field("GlobalSP.id"))
        .Field(new Field("GlobalSPRelations.id"))
        .Field(new Field("GlobalSPRelations.SpID"))
        .Field(new Field("GlobalSPRelations.ParamID"))
        .Field(new Field("id"))
        .Set(false)
        );
        editor.MJoin(new MJoin("DD")
            .Model<SPDBModel.DD>()
            .Name("GlobalPDD1")
            .Link("GlobalSP.id", "GlobalSPRelations.SpID")
            .Link("DD.id", "GlobalSPRelations.ParamID")
            .Where(q =>
                q.Where("GlobalSPRelations.ParamType", "4", "=")
            )
            .Order("DD.id ASC")
            .Field(new Field("GlobalSP.id"))
            .Field(new Field("GlobalSPRelations.id"))
            .Field(new Field("GlobalSPRelations.SpID"))
            .Field(new Field("GlobalSPRelations.ParamID"))
            .Field(new Field("id")
                .Options(new Options()
                    .Table("DD")
                    .Value("id")
                    .Label(new[] { "id", "CustomerID", "DDINumber", "Description", "Country" })
                    .Order("Country ASC").Order("id DESC")
                    .Render(row =>
                    {
                        Dictionary<string, object> dicPCEAValues = new Dictionary<string, object>();
                        dicPCEAValues = (Dictionary<string, object>)row;
                        if (dicPCEAValues.ContainsKey("DDINumber") && dicPCEAValues != null && DDIListViewModel != null)
                        {
                            if (DDIListViewModel?.FirstOrDefault(o => o.ID == CommonUtilities.ToLong(dicPCEAValues["id"])) != null)
                            {
                                strTp = "Some HTML"
                            }
                            else
                            {
                                dicPCEAValues = null;
                                strTp = null;
                            }
                        }
                        else
                        {
                            dicPCEAValues = null;
                            strTp = null;
                        }
                        return strTp;
                    })
                )
            )
        );
    }   
}
  1. Js code:
var editor = new $.fn.dataTable.Editor({

        destroy: true,
        ajax: {
            url: '/' + strAccountIdx1 + '/Admin/Packages/CRUDPackages/',
            data: function ( d ) {
                return $.extend({}, d, {
                    strEditorState: strAction,
                    intApplyChangesToCustomerP: intApplyChangesToCustomerP1,
                    strPreviousName: strPreviousName1
                });
            },
            type: 'POST',
            async: true,
            cache: false
        },
        table: '#tblDataTable',
        //template: $('#divEditorForm1').clone(),
        template: '#divEditorForm1',
        fields: [
            {
                label: '',
                name: 'GlobalSP.id',
                type: 'hidden'
            }, {
                label: 'GlobalPDD0[].id',
                name: 'GlobalPDD0[].id',    //ID
                data: function (row, type, val) {

                    //....
                }
            }, {
                label: '',
                name: 'GlobalPDD1[].id',
                type: 'checkbox',
                data: function (row, type, val) {

                    //...
                }
            }
        ]
    });

Answers

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    What does MyViewModel contain? Do you have the primary key for the records you want to show? If so, use a Where() condition similar to what you have on line 20. If you have a collection of id values, use WhereIn to filter to that list.

    Allan

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3

    Thanks. MyViewModel holds properties:

        public long ID { get; set; }
        public string MyNumber { get; set; }
        //....
    

    I do have the primary key, it is ID

    i thought about filering in the Where clause. Wasn't sure of the syntax but I'll check. Thanks again.

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3

    Shouldn't i place the Where clause after line 45 in the list of DD.id? If I place it in line 20 I still get all DD's. Thanks.

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3

    This would be more in line with what i need: https://datatables.net/forums/discussion/comment/95926/#Comment_95926

    What would be the syntax for c#? Thanks a lot.

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3

    Am trying this, I think it should work:

                                    .Where(q =>
                                        q.Where("id", MyListViewModel.Select(o => o.ID).ToList(), "=")
                                    )
    
  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3

    Not sure why but the none of the DD get returned when I see in debug there are items.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    Ah I see - you want the list of options to be limited. Do you want the options to charge for each host row though? If you, you need to make an Ajax call to get the list of options for each row as it is edited (e.g. using -e-api dependent()).

    If the list of options is static and you just want to limit them, then yes, what you have looks like it should work. If you add .Debug(true) just before the .Process(...) call (which interestingly I don't see in your controller above?), then it will dump the SQL that is being executed as part of the JSON response. If you post that, it might give us a clue as it what is going on.

    Regards,
    Allan

Sign In or Register to comment.