DT 1.10 - table.columns().search().draw();

deliator

Hi,

I don't understand why this is working with an html table and not working with a server side table ...
An idea ?

Its an automatic button filter on a single column value

$('.filterSect').click(function(){
       /*  onclick get data-attribute of a button */
        id = $(this).data("sect")
    oTable.columns( 2 ).search( id ).draw();
});

Thanks

kthorngren

With server side processing enabled the server script is responsible for the search code. When the above is executed you should see an ajax request sent to the server script with the parameters described here:
https://datatables.net/manual/server-side

One of the parameters you should see is this:
columns[2][search][value]: <value of id>

Your server script is expected to perform the search, based on the parameters sent, and return the results.

Kevin

deliator

Thanks for the answer, but i don't really understand what you mean ...

I just want to make a search on a specific column. In my example, it works on a hrml datatable, but i have no result if i use a server side datatable

oTable.columns( 2 ).search( 'myOccurence').draw();

don't execute the search

kthorngren

not working with a with a server side table

Are you saying the you have serverSide: true in your Datatable config?

If you have "serverSide: true" read this:
The doc I linked to states this:

When using server-side processing, DataTables will make an Ajax request to the server for each draw of the information on the page (i.e. when paging, ordering, searching, etc.).

In that ajax request are parameters (Sent parameters section) that the server side code is to process. Among these are the search requests.

You can see this by looking at the Network tab of your browser's dev tools. Click your search button to and you will see the request sent to the server with the parameters to search column 2. This tech note will show you how to view this:
https://datatables.net/manual/tech-notes/1

If you don't have "serverSide: true" read this:
More information is needed. We need to understand your table structure, data in the table and what you are searching on. Can you provide a link to your page?

If not then start by collecting debugger info:
https://datatables.net/manual/tech-notes/10

Kevin

deliator

Ok i understand better.

Just like you said, when i push the button I see in the dev console the complete url send to the server, and the specific column().search().value i'm looking for.

But the search is not applied to the DT...

Edit : when i copy the link to the browser i can see the json clearly with the values returned
Edit 2 : what i wrote above is true if my code still to be

$('.filterSect').click(function(){
        id = $(this).data("sect")
        oTable.columns( 2 ).search( id ).draw();
        $(".btn-group .btn").removeClass("active");
        $(this).addClass("active");
    });

So what must i change in that piece of code to replace oTable.columns( 2 ).search( id ).draw(); ?

kthorngren

But the search is not applied to the DT...

What data is being returned from the server?

when i copy the link to the browser i can see the json clearly with the values returned

You state "values returned". Is it performing a DB query for the specified "id" or returning all the data?

What code are you using for the server side? Is it one that is provided by Datatables?

Kevin

deliator

You state "values returned". Is it performing a DB query for the specified "id" or returning all the data?

Yes. A DB query is performing, it returned the number of lines (records) specified in the display length.

Something like :

{"recordsFiltered":349,"recordsTotal":349,"draw":7,"persons[{"field1":value,"field2":value}],"incomplete_results":false }

For the server side i use the Coldfusion framework

kthorngren

I may have made an incorrect assumption. I thought that id in oTable.columns( 2 ).search( id ).draw(); represented one row. Maybe a better question is.... Does the server JSON contain the expected number of rows from your search query?

{"recordsFiltered":349,"recordsTotal":349,

Is this the returned JSON after your search? If so then it doesn't seem correct as recordsFiltered should be the number of records after filtering and recordsTotal should be the total number of records.

Kevin

deliator

the return json after the search is {"recordsFiltered":25,"recordsTotal":349," ...
(if i fill manually the search field of DT)

If I click the button i must have the same result

Marc

kthorngren

the return json after the search is {"recordsFiltered":25,"recordsTotal":349," ...
(if i fill manually the search field of DT)

That is a global search.

If I click the button i must have the same result

That is a column based search. Is your server code parsing and processing the column searches? It seems that your server code only processes the global search.

What is the value of id in your function? You can use console.log or alert to see.

As a test try changing oTable.columns( 2 ).search( id ).draw(); to oTable.search( id ).draw(); or oTable.search( "<string>" ).draw(); (where <string> is what you typed in the global search) to perform a global search when you click the button. What is the result?

Kevin

deliator

My explanations are not clear ...

by the way oTable.search( id ).draw(); is working!

my ID value is PROT or PIMP or ...

What i initially wanted was :

I have a column (in my DT) call "sectors" containing for each row one of these strings (PROT, POPE, PIMP, ENDO, OCCL)

I also have a buttons group (outside the DT) with data-attribute containing these string, a button with data-sect=PROT, another with data-sect=PIMP, etc ...

I just what to know how passing how apply the corresponding search for each of these button.

kthorngren

I created a server side example based on your initial code posting:
http://live.datatables.net/birerohu/1/edit

The only thing I changed is the column number to use the "Office" column. Created Tokyo, London and Clear buttons using data-sect=. The example works.

by the way oTable.search( id ).draw(); is working!

If this is working but oTable.columns( 2 ).search( id ).draw(); this is not then either you have the wrong column specified (columns start counting at 0) or your server side code does not process the column search parameters. What code are you using for server side processing?

Kevin

deliator

Column number is right.
~~~~
Sorry, what you mean with "What code are you using for server side processing?"

kthorngren

Sorry, what you mean with "What code are you using for server side processing?"

What is the code at the AJAX URL? Is it one of the Datatables provided scripts or a custom script? That is the code responsible for performing the column search.

Kevin

deliator

Ok

'ajax': {
    'url': 'dataCodes.cfm',
    'dataSrc': 'persons'
        },

file contains

search = url["search[value]"]

Is it what you mean ?

kthorngren

search = url["search[value]"]

Looks like that is the code to extract the global search value. There are also specific column search values sent in the AJAX request. They are described here:
https://datatables.net/manual/server-side#Sent-parameters

columns[i][search][value]

Is your code parsing those?

Kevin

deliator

Hi Kevin,

No, my code don't parse that.

Regarding the link you pointed to i've add this line

<cfset filterAuto = url["columns[i]search[value]"]>

to my existing server side code (Coldfusion)

<cfset search = url["search[value]"]>
<cfset start = ++url["start"]>
<cfset length = url["length"]>
<cfset orderBy = "" >
<cfset orderByDir = url["order[0][dir]"]>

But it returned an error

Error Occurred While Processing Request
Element columns[i]search[value] is undefined in a Java object of type class coldfusion.filter.UrlScope.

In the objects.php file of your example, can ou give me the corredponding lines ?

Thanks

kthorngren

columns[i][search][value]

i is the column number. If you look at the ajax request you will see parameters with columns[0], columns[1], etc. Your code will need to loop through the columns parameters to build the query to search and sort by the proper fields.

For example with oTable.columns( 2 ).search( id ).draw(); you will need to parse columns[2][search][value].

Kevin

deliator

What is the good way to customize the ajax request with these parameters ?

allan

The ajax.data option lets you modify the data that is being sent to the server.

Allan

deliator

This is my code

    oTable = $('#myTable').DataTable({
        'dom'   : 'Bftpi',
        'select': "single",
        'bSort': true,
        'sPaginationType': "full_numbers",      
        'processing': true,
        'serverSide': true,
        'ajax': {
            'url': 'data.cfm',
            'dataSrc': 'persons'
        },

How to modify the data send to the server ?

allan

Using the ajax.data option I mentioned above. It is a link to its documentation which details how it can be used.

Allan

deliator

Can i take a look to the source of the file /ssp/objects.php mentionned Kevin's example ?

allan

Of course.

deliator

Hello Allan,

I don't see where is the ajax data option in the objects.php file ...

Thanks,

Marc

allan

That's the server-side script that you asked about. The client-side aspect is available here.

Allan