How to implement filtering when database values don't match display values?

DaveBurns

Not a problem with DataTables per se but I imagine others may have run into this design problem using it. The db table providing data for my table view is large (>100k rows) so I'm doing all server-side processing over AJAX. I have a column which is stored in the db as integers but when forming my JSON response I format it into values like (phone, tablet, laptop). I now want to allow the user to filter on those values, i.e. type "tab" and see only rows with "tablet" in them.

How have others dealt with this given that I can't do a simple SQL LIKE clause on the "tab" string to implement filtering? One possible way is to have a mapping table in the db between integers and strings. I'd do another query first using LIKE to look up the integer value there and then use that value in the "main" filtering query. This seems cumbersome - is this the only way to deal with it though or is there some better way?

db

Ironwil616

Since you're mapping integers to text values, I'd use an enum and do all sorting/filtering server-side. It's what I do anytime I have to use integer values that are essentially IDs for values in another table, for ease of use within code. For instance, I have a Model that maps to my dataTables JSON data. I'm using C# and LINQ, and here's a bit of my code:

[code]
// For dataTables, passed by ref to the 'GetAllProjects' method.
int prefilterCount = 0;
int postfilterCount = 0;

// Generic collection of 'Project' objects. 'SortColumn' is an enum
// mapping values 0-29 to column names.
List allProjects = serviceClass.GetAllProjects(status,
globalSearch, ((SortColumn)sortColIndex).ToString(),
ref prefilterCount, ref postfilterCount);

totalRecords = prefilterCount;
totalDisplayRecords = postfilterCount;

// Columns abbreviated for readability (actually 30 columns).
// Convert columns to an array of strings for 'aaData'.
var results = from p in allProjects select new[]
{
p.Join, p.ID, p.Name, p.Description, p.CourseNumber, p.Program,
p.DateCreated, p.StartDateEstimate, p.StartDateActual,
p.EndDateEstimate, p.EndDateActual
};

var json = Json(new
{
sEcho = sEcho,
iTotalRecords = totalRecords,
iTotalDisplayRecords = totalDisplayRecords,
aaData = results
});

return json;
[/code]

Hopefully the point didn't get lost in all that. Anyway, in my case I had a number of integers that map to several string values, and I hate trying to remember that stuff in code. I'm not sure what language you're using, but in C#, I use enums like this:

[code]
// From a string value, create an enum instance:
string projectStatus = formCollection["status"];
(ProjectStatus)Enum.Parse(typeof(ProjectStatus), projectStatus);
// From an integer value, create an enum instance:
int statusID = Convert.ToInt32(formCollection["statusID"]);
ProjectStatus status = (ProjectStatus)statusID;
// Convert an enum instance to an integer value:
int statusID = (int)ProjectStatus.Active;
// Convert an enum instance to a string value:
string status = ProjectStatus.Active.ToString();
[/code]

Takes the guess work out of assigning integer values to properties of classes, and comes in very handy here. The problem you're going to run into is that you're sorting string values on a table that holds integer values, most probably mapped by foreign key to a table that contains their 'names' as strings. You can write a stored procedure to take care of this, but I didn't take that route, because I'm not a SQL guru. I love ORMs like LINQ to SQL, and that's what I use.

In my case, I'm constructing a collection of Model objects I create from the database objects. My database table 'projects' holds only some of the data that needs to be rendered to the page. The other columns of data are derived from values in the table or related to it. I construct my own 'Projects' class that holds all the values and sort that. So your integer values would already have been converted to their string values and placed in a collection in memory you could then sort or filter however you choose.

I have to admit I cheated a little here, and it's pretty cheesy. Since I have over 7,000 records that would have to be converted to my expanded Projects class before sorting and filtering, this introduced a huge amount of overhead. Specifically, the names of all people working on a project, broken down by down, was required for 6 columns of data. Since the mapping goes Projects -> ProjectUsers -> Users, this wasn't a minor thing. I actually added columns to the original table and ran some code that gathered all this data for me and put them in each of the 7,000 records, and changed the CRUD code to add these values when needed. So, I'm not really converting on the fly anymore, but since I can perform sorting/filtering and a skip/take prior to doing any object conversion, my processing time went down from about 30 seconds to .267 seconds. Anyway, that was a lot of info (sorry if I digressed too much), but this is how I made a large record set work with dataTables.

DaveBurns

Your answer is pretty long and I don't know if you answered my question or not. Your last sentence seems to indicate though that my problem is how to work with large data sets. I have that part handled just fine. My question is how to map a substring for filtering ("tab" in my example") to the proper value in the database when those values are numbers, not the displayed strings. I have a feeling I need to create a database table with the number=>string mappings and join against that. I wanted to know if anyone else has done this before and come up with an alternate approach - maybe some built-in DataTables feature that I missed. Thanks for all your info though.