server-side search, show empty table

rldean1

My team has some peculiar requirements that I need some help jerry-rigging into my server-side search. Basically, on initial display, OR, if the the search is an empty string (on any draw), I don't want to display any results in the table...

1. We don't want to hit the database (do a draw) unless there's at least 2 characters in the search input. I've achieved this.

2. I use deferLoading so that I can make sure nothing is shown at the beginning on init. However, there's a problem where the info section shows "Showing 0 to 0 of 0 entries (filtered from NaN total entries)". I need to get NaN to say 0.

3. When the search input is cleared, a draw happens. All records are returned. We don't want that -- We want to show nothing. I'm trying to figure out how to achieve this on the JavaScript side. I thought about loading a blank data array [], and utilizing something like ajax.reload(), datatable.clear(), datatable.rows().add('[]'), and datatables.draw(), but I can't figure out how to use this alongside server-side search.

Here's a very small part of the complicated stored procedure behind the scenes:

                    @DT_Search is null
                    or _.FullName like '%' + @DT_Search + '%'
                    or _.EEID_Status like '%' + @DT_Search + '%'
                    or AllNames like '%' + @DT_Search + '%'
                    or PositionTitle like '%' + @DT_Search + '%'
                    or SchoolOffice like '%' + @DT_Search + '%'
                    or Department like '%' + @DT_Search + '%'

BEST THING I CAN THINK OF: (UPPER BLOCK DOES NOT WORK, THIS IS EXAMPLE ONLY)

The best thing I can come up with is "override" the search with a ridiculous search string, something like 'zzzzzzzzzz', but this feels really kludgy.

                        if ((this.value = '') || (this.value.length < 2)) {
                            tblResults.search('_zzzzzzzzzz_').draw();
                        }
                        else if (this.value.length > 2) {
                            tblResults.search(this.value.trim()).draw();
                        };

kthorngren

I use deferLoading

deferLoading itself doesn't seem to cause the info to display (filtered from NaN total entries) as seen in this example:
http://live.datatables.net/gulabita/1/edit

You must have something else causing that display. I have deferLoading: [ 0, 100 ],. Do you have a string where I have 100?

When the search input is cleared

Would it be possible to check the search value/length to see if its empty and just return an empty dataset along with all the other expected server side parameters?

Kevin

rldean1

@kthorngren

I failed to include the 2nd item in the array. I could have either done deferLoading: [0, 0] or deferLoading: 0. Instead, I was doing: deferLoading: [0].

Also, I will have to make the assumption that you cannot manually clear/reload new data [] if you're using server-side search. I suppose that defeats the purpose of using serverSide. I moved the logic to detect the search input to the SQL stored procedure. SO, basically, if it detects a null, empty, or length @DT_Search <= 2, it will return a specific structure: {"draw":7,"recordsTotal":138364,"recordsFiltered":0,"data":""}

I was trying to avoid it because I find SQL esoteric, monolithic, dull, boring, and frustrating... plus I really hate programming in SQL to be honest. Just venting, that is not your issue, LOL.

Thanks!