server-side search, show empty table

server-side search, show empty table

rldean1rldean1 Posts: 126Questions: 58Answers: 1
edited December 2019 in DataTables 1.10

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 + '%'


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)) {
                        else if (this.value.length > 2) {

This question has an accepted answers - jump to answer


  • kthorngrenkthorngren Posts: 10,621Questions: 25Answers: 2,421
    edited December 2019 Answer ✓

    I use deferLoading

    deferLoading itself doesn't seem to cause the info to display (filtered from NaN total entries) as seen in this example:

    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?


  • rldean1rldean1 Posts: 126Questions: 58Answers: 1
    edited December 2019


    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.


Sign In or Register to comment.