Server side pagination with Individual column searching

Dorababu

Hello all I am binding data using server side code which is working as expected, on top of it I am trying to achieve this

https://www.datatables.net/examples/api/multi_filter_select.html

But some how the values from second page are not getting loaded to dropdown

Here is my MVC code to get the data

       public ActionResult LoadData()
         {

            var draw = Request.Form.GetValues("draw").FirstOrDefault();
            var start = Request.Form.GetValues("start").FirstOrDefault();
            var length = Request.Form.GetValues("length").FirstOrDefault();
            //Find Order Column
            var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault();
            var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();
            string search = Request.Form.GetValues("search[value]")[0];

            int pageSize = length != null ? Convert.ToInt32(length) : 0;
            int skip = start != null ? Convert.ToInt32(start) : 0;
            int recordsTotal = 0;
            using (BMA_DEVEntities context = new BMA_DEVEntities())
            {
                // dc.Configuration.LazyLoadingEnabled = false; // if your table is relational, contain foreign key
                var v = from vd in context.VesselDetails
                        join ld in context.LookupDatas on vd.VesselTypeId equals ld.LookupDataId into ldt
                        from ldata in ldt.DefaultIfEmpty()
                        join re in context.ReportableEvents on vd.ReportableEventId equals re.ReportableEventId
                        join rc in context.ReportableCategories on re.CategoryId equals rc.CategoryId
                        join rd in context.ReportableEventDetails on re.ReportableEventId equals rd.ReportableEventId into rdt
                        from edt in rdt.DefaultIfEmpty()
                        join es in context.EventSeverities on edt.EventSeverityId equals es.EventSeverityId into esv
                        from sev in esv.DefaultIfEmpty()
                        join ps in context.Personnels on edt.AssigneeId equals ps.PersonnelId into per
                        from psn in per.DefaultIfEmpty()
                        join status in context.EventStatus on edt.EventStatusId equals status.EventStatusId into est
                        from estatus in est.DefaultIfEmpty()
                        where re.IsInternalReview == false && re.SubmitToShip == false
                        where vd.VesselName.Contains(search) || ldata.LookupDataName.Contains(search)
                        || rc.CategoryName.Contains(search) || sev.EventSeverityName.Contains(search)
                        || estatus.EventStatus.Contains(search)
                        || psn.PersonnelName.Contains(search)
                        || vd.BriefDescription.Contains(search)
                        select new
                        {
                            EventId = string.Concat(re.ReportableEventId, " ", re.CreatedDt.Value.Year),
                            re.ReportableEventId,
                            vd.VesselName,
                            vd.VesselDetailId,
                            VesselType = ldata.LookupDataName,
                            EventType = rc.CategoryName,
                            vd.DateofEvent,
                            sev.EventSeverityName,
                            estatus.EventStatus,
                            Assignee = psn.PersonnelName,
                            vd.BriefDescription
                        };
                string vesselName = Request.Form.GetValues("columns[1][search][value]").FirstOrDefault().ToString().Trim();
                string vesselType = Request.Form.GetValues("columns[2][search][value]").FirstOrDefault().ToString().Trim();
                string eventDate = Request.Form.GetValues("columns[4][search][value]").FirstOrDefault().ToString().Trim();
                if (!string.IsNullOrWhiteSpace(vesselName) && !string.IsNullOrWhiteSpace(vesselType) && !string.IsNullOrWhiteSpace(eventDate))
                {
                    vesselType = vesselType.Replace("\\", "");
                    vesselName = vesselName.Replace("\\", "");
                    eventDate = eventDate.Replace("\\", "");
                    DateTime dateTime = DateTime.ParseExact(eventDate, "MM-dd-yyyy", CultureInfo.InvariantCulture);

                    v = v.Where(a => a.VesselName == vesselName && a.VesselType == vesselType && a.DateofEvent == dateTime);
                }
                else
                {
                    if (!string.IsNullOrEmpty(vesselName))
                    {
                        vesselName = vesselName.Replace("\\", "");
                        v = v.Where(a => a.VesselName == vesselName);
                    }
                    if (!string.IsNullOrEmpty(vesselType))
                    {
                        vesselType = vesselType.Replace("\\", "");
                        v = v.Where(a => a.VesselType == vesselType);
                    }
                    if (!string.IsNullOrEmpty(eventDate))
                    {
                        eventDate = eventDate.Replace("\\", "");
                        DateTime dateTime = DateTime.ParseExact(eventDate, "MM-dd-yyyy", CultureInfo.InvariantCulture);
                        v = v.Where(a => a.DateofEvent == dateTime);
                    }
                }
                //SORT
                if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
                {
                    v = v.OrderBy(sortColumn + " " + sortColumnDir);
                }

                recordsTotal = v.Count();
                var data = v.Skip(skip).Take(pageSize).ToList();
                return Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data }, JsonRequestBehavior.AllowGet);
            }
        }

The drop downs are filled as follows

but this value is missing in drop down

Can some one tell how can I do that

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

colin

I'm not clear when that code is called, or when the dropdowns are inserted into client-side select elements. It would be worth debugging to determine whether the issue is because the server-side script isn't extracting all the necessary fields, or whether the client isn't adding it correctly into the select.

Colin

Dorababu

Ho Colin on the page load it is getting called, I mean on document.ready

kthorngren

If you have serverSide enabled only the rows shown in the table are in the client. The code in the multi filter select example only has access to the rows in the client.

In your server script you can look at the draw value. If its 1 then this is the first request to the server. At this time you can run some extra queries to get all the options for each column and return them in a new object in the JSON response. So your response would look something like this:

{
  "draw": 1,
  "recordsTotal": 57,
  "recordsFiltered": 57,
  "data": [ .... ],
  "options": [ ... ]
}

Where options will contain the column search options. In initComplete you can use the second parameter (json) to access the options in the return JSON. Use this instead of the column data to populate the select lists.

Kevin

Dorababu

So you mean to say I need to set these values on initcomplete event? if so can I get an example

{
  "draw": 1,
  "recordsTotal": 57,
  "recordsFiltered": 57,
  "data": [ .... ],
  "options": [ ... ]
}

kthorngren

So you mean to say I need to set these values on initcomplete event

No. That is an example of the JSON data that is returned from the server side script. Your server side script currently returns everything but options. You will need to create queries in your server script to get the unique data from each column that you want to populate into the select lists. Do this only when draw is 1. Store it in a new object that is returned in the JSON data.

Sorry, I don't have a way to build a server side script to populate the options. This example shows the json response in initComplete.
http://live.datatables.net/pubatiwo/1/edit

Instead of using this code from the multi select example:

                column.data().unique().sort().each( function ( d, j ) {
                    select.append( '<option value="'+d+'">'+d+'</option>' )
                } );

You will access the options object in the JSON to build the select lists.

Kevin

Dorababu

Hi Kevin, I am already doing that but my feeling is as on server side I am returning the data for only first 10 rows I guess it is loading those 10

var data = v.Skip(skip).Take(pageSize).ToList();

kthorngren

Yes with server side processing your server script is currently returning 10 rows of data. The client has access to only those 10 rows to build the select lists. You will need to add more queries, when the draw value is 1, to get the unique values from the DB for each column. Then return those values so they can be processed in initComplete. This is only done once on the first request (draw == 1).

The other option is to turn off server side processing and load all the data into the client. This just depends on the amount of data you have and if it causes performance issues.

Kevin

kthorngren

Maybe this will help explain what I mean. I'm not familiar with MVC so this is pseudo code:

         recordsTotal = v.Count();
         var data = v.Skip(skip).Take(pageSize).ToList();

         if ( draw == 1) {
            options = 'select distinct col1, distinct col2 from mytable'
         } else {
           options = {}
         }
         return Json(new { options = options, draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data }, JsonRequestBehavior.AllowGet);

This tutorial explains what the SQL DISTINCT statement does. You will need to convert this options = 'select distinct col1, distinct col2 from mytable' to something that works in your framework to get the unique values for each column. I added an options object to the return statement.

In initComplete you will use the returned options to build the select lists for each column.

Kevin

Dorababu

OK l had 7 columns at present, do I need to send all them in options?

kthorngren

For all the columns that you want a complete list data for the select lists you will need to fetch the unique data and send.

Kevin

Dorababu

Any such example either in PHP or .Net?