Datatable Server Side Searching With Custom Field

BuiBun

Hi Guys, I am trying to search at the server side with a datatable which has a custom field.

First, I have a Book:

public class Book { 
    public string id {get; set;} 
    public string name {get; set;} 
    public int type {get; set;} 
}

, a Bookdatatable:

public class Bookdatatable {
    public string id {get; set;}
    public string name {get; set;}
}

and a TypeClass:

public static class BookTypes{
     public static List<BookType> bookTypeList = new List<BookType>
     {
           new BookType {id = 1, name = "Maths"},
           new BookType {id = 2, name = "English"}
     }
}

Basically my datatable has to display ID, Name and the BookTypeName:

so for my server I am doing this:

using (var db = new Database("sqlserver",connectionString))
{
    var response = new Editor(db, "Book", "id")
                             .Model<Bookdatatable>()
                             .Field("BookTypeName").DbField("type")
                             .GetFormatter((id,row) => BookTypes.bookTypeList
                                                           .Where( t=>t.id == id.ToString())
                                                           .Select( t=>t.name)
                                                           .Single())
                              )
                              .Process(request)
                              .Data();
}

I am able to Display the datatable correctly, but when I do a search, I am able to search for the ID, Name but not the BookTypeName. I am doing server side search. Does anyone has any idea how to search for custom fields?

rf1234

The answer is that you can't do that. Server side search searches your data base table BEFORE rendering. Either you manipulate your search string sent from the client in a way so that it matches the database values. I did that for dates for example to convert DD/MM/YYYY into YYYY-MM-DD before sending it to the server.

I found that the much better way is to use a view and do the formatting in the view. So that the content is being found with server side search.

I built my own full text server side search that includes all uploaded and parsed documents (incl. OCR-recognition) and all kinds of different number formatting to help find things. This code is from a view that I created for one purpose only: Provide different formatted number rendering so that a number will be found regardless of whether the user types 1,000,000.98 or 1000000.98 or 1.000.000,98 or 1000000,98 into the search field:

..............
CONCAT(REPLACE(LEFT( FORMAT(a.annual_cost_income, 2), POSITION('.' IN FORMAT(a.annual_cost_income, 2)) - 1 ), ',', '.'), ',', SUBSTR(FORMAT(a.annual_cost_income, 2), POSITION('.' IN FORMAT(a.annual_cost_income, 2)) + 1))
                    AS annual_cost_income_german,
FORMAT(a.annual_cost_income, 2)     AS annual_cost_income_english,
CAST(a.annual_cost_income AS CHAR)  AS annual_cost_income_english_short,
REPLACE(a.annual_cost_income, '.', ',') AS annual_cost_income_german_short,