How to populate dropdownlist on individual column searching on server side processing

Mundaring

Hi Allan,

I have used the serverSide on true working perfectly fine, also I have used individual column searching with dropdownlist, but when I activate the serverSide on true the dropdownlist is just being populated with the information presented on the screen not with all the information from the table or query I just executed. This make sense because it is working with partial data. Is there any chance to load the dropdownlist with all the possible values on the total of the information?

Thanks,
Wilson

Mundaring

Attaching images.

Image 5 is on serverSide: true (displying partial list)
Image 6 is on serverSide: false (displaying the total of options in the query)

allan

Hi,

What you would need to do is query the database to get the full list of options on the first draw (draw = 1) and have that information sent back to the client-side. You could then populate the select lists in the initComplete method, which will pass in the JSON returned from the server as the second parameter.

Allan

Mundaring

Hi Allan,

I am not sure where to start when you say "query the database to get the full list of options". I cannot run the full query because it is a lot of information, do you mean a separate query?. I am using initComplete to populate the current drop down I have created, but as I said it just contains some option on the list according to the information on the draw 1...n. I am attaching my code maybe you could point me where can I start applying your suggestion.

I would like the drop down list filter the information in a similar way the search does it, even working on server side true still looks for information on the total of information not just on the partial information load on the draw.

I am attaching an image of the screen, so you can have an idea.

Controller

//DataTables
        [AcceptVerbs(HttpVerbs.Get | HttpVerbs.Post)]
        public ActionResult DTAsset()
        {
            var request = HttpContext.Request.Form;

            using (var dtDB = new DataTables.Database("sqlserver", conn))
            {
                var response = new Editor(dtDB, "ams.Asset", "Asset.Id")
                    .Model<VMAsset>()
                    .Field(new Field("Asset.AssetModuleId")
                        .Options("ams.AssetModule", "Id", "Name")
                    )
                    .Field(new Field("Asset.Code"))
                    .Field(new Field("Asset.Name"))
                    .Field(new Field("Asset.SuburbName"))
                    .Field(new Field("Asset.RoadName"))
                    .Field(new Field("Asset.TypeOfAssetId")
                        .Options("ams.TypeOfAsset", "Id", "DisplayName")
                    )
                    .Field(new Field("Asset.PurchaseValue"))
                    .LeftJoin("ams.TypeOfAsset", "Asset.TypeOfAssetId", "=", "TypeOfAsset.Id")
                    .LeftJoin("ams.AssetModule", "Asset.AssetModuleId", "=", "AssetModule.Id")
                    .Process(request)
                    .Data();

                return Json(response, JsonRequestBehavior.AllowGet);
            }
        }

Model

public class VMAsset: EditorModel
    {

        public class Asset : EditorModel
        {
            public int Id { get; set; }
            public int AssetModuleId { get; set; }
            public string Code { get; set; }
            public string Name { get; set; }
            public string SuburbName { get; set; }
            public string RoadName { get; set; }
            public decimal PurchaseValue { get; set; }
            public int TypeOfAssetId { get; set; }
        }

        public class TypeOfAsset : EditorModel
        {
            public string DisplayName { get; set; }
        }

        public class AssetModule : EditorModel
        {
            public string Name { get; set; }
        }
    }

View - HTML

<table id="tblList" class="table table-striped table-hover as-datatable" cellspacing="0">
    <thead>
        <tr>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
        </tr>
        <tr>
            <th></th>
            <th>Id</th>
            <th>Asset Module</th>
            <th>Code</th>
            <th>Name</th>
            <th>Suburb Name</th>
            <th>Road Name</th>
            <th>Type Of Asset</th>
            <th>Purchase Value</th>
            <th></th>
        </tr>
    </thead>
</table>

View - JS

var table = $('#tblList').DataTable({
            initComplete: function () {
                var col = 0;
                this.api().columns().every(function () {
                    if (col === 2 || col === 7 ) {
                        var column = this;
                        var select = $('<select><option value=""></option></select>')
                            .prependTo($('#tblList').find('thead tr:eq(0) th:eq(' + col + ')'))      
                            .on('change', function () {
                                var val = $.fn.dataTable.util.escapeRegex(
                                    $(this).val()
                                );

                                column
                                    .search(val ? '^' + val + '$' : '', true, false)
                                    .draw();
                            });
                        column.data().unique().sort().each(function (d, j) {
                            select.append('<option value="' + d + '">' + d + '</option>')
                        });
                    }
                    col++;
                });
            },
            "searching": true,
            "bPaginate": true,
            "bInfo": true,
            "bAutoWidth": true,
            "processing": true,
            "order": [3, 'asc'],
            "pagingType": "full_numbers",
            "ajax": {
                "url": urlRequest, 
                "type": "POST"
            },
            "deferRender": true,
            "serverSide": true,
            //"searchDelay": 800,
            "autoWidth": true,
            "stateSave": true,
            "columns": [
                {
                    "className": 'details-control',
                    "orderable": false,
                    "data": "Asset.Id",
                    //"data": null,
                    "defaultContent": ''
                },
                { "data": "Asset.Id" },
                {
                    "data": "AssetModule.Name",
                    "editField": "Asset.AssetModuleId"
                },
                { "data": "Asset.Code" },
                { "data": "Asset.Name" },
                { "data": "Asset.SuburbName" },
                { "data": "Asset.RoadName" },
                {
                    "data": "TypeOfAsset.DisplayName",
                    "editField": "Asset.TypeOfAssetId"
                },
                { "data": "Asset.PurchaseValue" },
                {
                    "data": "Asset.Id",
                    "orderable": false,
                    render: function (data, type, row) {
                        return '<a href=/Assets/Details/' + data + ' class="btn btn-primary btn-xs">Details <span class="glyphicon glyphicon-edit" aria-hidden="true"></span></a>';
                    }
                }
            ],
            keys: {
                columns: ':not(:first-child)',
                keys: [9]
            },
            select: {
                style: 'os',
                selector: 'td:first-child'
            }
        });

Thanks a lot
Wilson

allan

In line 26 (currently empty) of the .NET controller above you would need to add suitable code that will do SELECT columnName FROM tableName from the database.

If you want to use the .NET Database class that Editor uses, the documentation for it is here. The Select() method will be the most useful one.

Allan

Mundaring

Hi Allan,

Sorry I have been trying different ways to do this but I have not succeed. I Reckon there is a problem just activating serverSide to true stops working the filter by these individual column drop down list filters. I have tested in other screens where I have similar functionality, when I just activate the server side feature stops working the searching. Can you help me on discover what is happening when there is a drop down list and server side is on true, why is not working.

I have a second question, I also did what you suggested to search on the first draw all the values for each drop down list but I don't know how to pass it like you said as a second parameter on the JSON.

[AcceptVerbs(HttpVerbs.Get | HttpVerbs.Post)]
        public ActionResult DTAsset()
        {
            var request = HttpContext.Request.Form;

            using (var dtDB = new DataTables.Database("sqlserver", conn))
            {
                var response = new Editor(dtDB, "ams.Asset", "Asset.Id")
                    .Model<VMAsset>()
                    .Field(new Field("Asset.AssetModuleId")
                        .Options("ams.AssetModule", "Id", "Name")
                    )
                    .Field(new Field("Asset.Code"))
                    .Field(new Field("Asset.Name"))
                    .Field(new Field("Asset.SuburbName"))
                    .Field(new Field("Asset.RoadName"))
                    .Field(new Field("Asset.TypeOfAssetId")
                        .Options("ams.TypeOfAsset", "Id", "DisplayName")
                    )
                    .Field(new Field("Asset.PurchaseValue"))
                    .LeftJoin("ams.TypeOfAsset", "Asset.TypeOfAssetId", "=", "TypeOfAsset.Id")
                    .LeftJoin("ams.AssetModule", "Asset.AssetModuleId", "=", "AssetModule.Id")
                .Process(request)
                .Data();                
                
                if (response.draw == 1)
                {                    
                    var ddl1 = new Editor(dtDB, "ams.TypeOfAsset").Db().Sql("select DisplayName from ams.TypeOfAsset").FetchAll();
                }

                return Json(response, JsonRequestBehavior.AllowGet);
            }            
        }

Thanks,
Wilson

allan

You need to use something like this:

            var request = HttpContext.Request.Form;
 
            using (var dtDB = new DataTables.Database("sqlserver", conn))
            {
                var response = new Editor(dtDB, "ams.Asset", "Asset.Id")
                    .Model<VMAsset>()
                    .Field(new Field("Asset.AssetModuleId")
                        .Options("ams.AssetModule", "Id", "Name")
                    )
                    .Field(new Field("Asset.Code"))
                    .Field(new Field("Asset.Name"))
                    .Field(new Field("Asset.SuburbName"))
                    .Field(new Field("Asset.RoadName"))
                    .Field(new Field("Asset.TypeOfAssetId")
                        .Options("ams.TypeOfAsset", "Id", "DisplayName")
                    )
                    .Field(new Field("Asset.PurchaseValue"))
                    .LeftJoin("ams.TypeOfAsset", "Asset.TypeOfAssetId", "=", "TypeOfAsset.Id")
                    .LeftJoin("ams.AssetModule", "Asset.AssetModuleId", "=", "AssetModule.Id")
                .Process(request)
                .Data();               
                 
                if (response.draw == 1)
                {                   
                    var select = dtDB.Select( "ams.TypeOfAsset", "DisplayName" ).FetchAll();
                    response.meta.assetTypes = select;
                }
 
                return Json(response, JsonRequestBehavior.AllowGet);

i.e. query the database, fetch it all and then add it to the JSON response.

Then in the initComplete callback you will be able to access it as json.meta.assetTypes.

Allan

Mundaring

Thanks Allan I will try this. Sorry to bother you but the main issue I am having right now is, I have my normal screens with individual drop down list on the top of some columns and they work properly until I change the feature serverSide to true. As soon I activate this feature they stop searching.

Thanks,
Wilson

Mundaring

Hi Allan

Finally your code did not worked exactly but you gave the idea and I did something around that and it is passing the data from the controller to the view now. (Image)

This was the final result

var ddl1 = dtDB.Sql("select DisplayName from ams.TypeOfAsset").FetchAll(); 
response.meta.Add("assetTypes", ddl1);

But I am still struggling with my previous question about activating serverSide. Could you please help me on that?

Thanks,
Wilson

allan

Sorry - you would use new string[] {"DisplayName"} for the second parameter as it expects an array.

Sorry I have been trying different ways to do this but I have not succeed. I Reckon there is a problem just activating serverSide to true stops working the filter by these individual column drop down list filters

The SELECT is needed to resolve that. As noted above, when server-side processing is enabled you have to select the values from the database that you want to show in the select. There is no other option (if you need server-side processing to be enabled).

Allan

Mundaring

Hi Allan,

All the values on the drop down list are coming from the database and from the same column. But just with a simple exercises I have made is occurring this. I got some individual column search using drop down list, with the values it captures on the draw and when I select a value it is not filtering anything. I did something but not tested completely I have change a line like this on the initComplete:

.search(val ? '^' + val + '$' : '', true, false)                        

to this:

.search(this.value)

It looks it is working now.

Thanks,
Wilson