JSON data request

jpu · September 2022

I am working in a legacy java environment at the server side and find myself having problems to parse the JQuery Datatables request (either sent as GET or POST) because the nested '[]' notation (e.g. "columns[0][search][Regex]=false" using classical bean libraries as this notation doesn't really conform to a bean standard).

Therefore I was wondering whether I could have Datatable post the query parameters as a JSON string (e.g. what you get with 'table.ajax.params()'). That would sound logical since it also expects a JSON result. I am a novice at javascript and JQuery/Datatable so I don't see directly how to do this.

jpu · September 2022

Searching this forum I found the following ajax option configuration to work:

"ajax" : {
url": "/myData.json",
"contentType" : "application/json",
"type" : 'POST',
"data": function(d) {
return JSON.stringify(d);
}
}

allan · September 2022

Thanks for posting back - yes, the ajax.data option can be used in exactly that way as shown in the last example on the documentation for that option.

Just a note - I've changed the title of your post. It had your e-mail address before, which probably isn't want you want as the thread title

Allan

jpu · October 2022

Ok, thanks.
Do you happen to know how I can add the search criteria of the searchBuilder plugin to the JSON data request. I can see the searchBuilder parameters (as request parameters) if I don't use "data" function above but Ican't find a way to add it to JSON data request.

jpu · October 2022

Why doesn't something like the following work:

"data": function(d) {
    var dt = $('#myTable').DataTable();
    var sbDetails = JSON.stringify(dt.searchBuilder.getDetails());
    d.sbCrit = sbDetails;
    return JSON.stringify(d);
}

When debugging the loading of my page the 'dt' variable and 'dt.searchBuilder' exist, but 'dt.searchBuilder.getDetails()' will result in an unrecoverable error.

allan · October 2022

I'm not sure I'm afraid. If you can give me a link to the page in question I can take a look.

Allan

jpu · October 2022

It is a local trial project not publicly available so I can't give you a link and since it uses serverside processing to load the data sending you the page won't help much.

Basically the Datatable part in the page looks like (omitting js,css includes):

    <script type="text/javascript" class="init">
        $(document).ready(function() {
            $('#myTable').DataTable({
                 "dom": '<"top"Qflp<"clear">>rt<"bottom"ifp<"clear">>',
                    "columns": [
                        {   name: 'id',data: 'id',width:'0%',visible: false,searchable: false,orderable: false},
                        {   name: 'ondernemingsnummer',data: 'ondernemingsnummer',width:'15%',searchable: true,orderable: true },
                        {   name: 'naam',data: 'naam',width:'25%',searchable: true,orderable: true },
                        {   name: 'full_address',data: 'adres',width:'25%',searchable: true,orderable: true },
                        {   name: 'rekeningnummer',data: 'rekeningnummer',width:'15%',searchable: true,orderable: true } ,
                        {   name: 'actief',data: 'actief',width:'10%',searchable: true,orderable: true },
                        {   name: 'action',data: 'id',width:'10%',searchable: false,orderable: false,
                            render: function (data,type,row,meta) {
                                return '<a href="/idb-be/mdw/onderneming.do?command=wijzig&amp;backurl=/idb-be/mdw/onderneming.do&#63command=init&amp;id='+data+'"><button type="button" class="button">Edit</button></a>';
                            },
                        },
                    ],
                "rowId": function(d) {return 'id_'+d.ID;},
                "paging": true,
                "pagingType": "full_numbers",
                "lengthMenu": [ [20,50,100,500,-1], [20,50,100,500,'All'] ],
                "scrollY": '700px',
                "scrollCollapse": true,
                "stateSave": true,
                "stateDuraton": -1,
                "processing" : true,
                "serverSide" : true,
                "search": { return: true },
                "ajax" : {
                    "url": "/jqdto/services/jqdto/getData",
                    "contentType" : "application/json",
                    "type" : 'POST',
                    "data": function(d) {
                        var dt = $('#myTable').DataTable();
                        var sbDetails = JSON.stringify(dt.searchBuilder.getDetails());
                        d.sbCrit = sbDetails;
                        return JSON.stringify(d);
                    }
                },
                initComplete: function () {
                    // Apply the search
                    this.api()
                        .columns()
                        .every(function () {
                            var that = this;
                            var inputElem = $('input', this.header());
                            if (typeof(inputElem) != 'undefined' && (inputElem != null) && (inputElem.length != 0)) {
                                inputElem[0].value = that.search();
                            }
                            $('input', this.header()).on('change clear', function () {
                                if (that.search() !== this.value) {
                                    that.search(this.value).draw();
                                }
                            });
                        });
                }
            });
        });
    </script>

Edited by Kevin: Syntax highlighting. Details on how to highlight code using markdown can be found in this guide

jpu · October 2022

Should have used back ticks:

    <script type="text/javascript" class="init">
        $(document).ready(function() {
            $('#myTable').DataTable({
                 "dom": '<"top"Qflp<"clear">>rt<"bottom"ifp<"clear">>',
                    "columns": [
                        {   name: 'id',data: 'id',width:'0%',visible: false,searchable: false,orderable: false},
                        {   name: 'ondernemingsnummer',data: 'ondernemingsnummer',width:'15%',searchable: true,orderable: true },
                        {   name: 'naam',data: 'naam',width:'25%',searchable: true,orderable: true },
                        {   name: 'full_address',data: 'adres',width:'25%',searchable: true,orderable: true },
                        {   name: 'rekeningnummer',data: 'rekeningnummer',width:'15%',searchable: true,orderable: true } ,
                        {   name: 'actief',data: 'actief',width:'10%',searchable: true,orderable: true },
                        {   name: 'action',data: 'id',width:'10%',searchable: false,orderable: false,
                            render: function (data,type,row,meta) {
                                return '<a href="/idb-be/mdw/onderneming.do?command=wijzig&amp;backurl=/idb-be/mdw/onderneming.do&#63command=init&amp;id='+data+'"><button type="button" class="button">Edit</button></a>';
                            },
                        },
                    ],
                "rowId": function(d) {return 'id_'+d.ID;},
                "paging": true,
                "pagingType": "full_numbers",
                "lengthMenu": [ [20,50,100,500,-1], [20,50,100,500,'All'] ],
                "scrollY": '700px',
                "scrollCollapse": true,
                "stateSave": true,
                "stateDuraton": -1,
                "processing" : true,
                "serverSide" : true,
                "search": { return: true },
                "ajax" : {
                    "url": "/jqdto/services/jqdto/getData",
                    "contentType" : "application/json",
                    "type" : 'POST',
                    "data": function(d) {
                        var dt = $('#myTable').DataTable();
                        var sbDetails = JSON.stringify(dt.searchBuilder.getDetails());
                        d.sbCrit = sbDetails;
                        return JSON.stringify(d);
                    }
                },
                initComplete: function () {
                    // Apply the search
                    this.api()
                        .columns()
                        .every(function () {
                            var that = this;
                            var inputElem = $('input', this.header());
                            if (typeof(inputElem) != 'undefined' && (inputElem != null) && (inputElem.length != 0)) {
                                inputElem[0].value = that.search();
                            }
                            $('input', this.header()).on('change clear', function () {
                                if (that.search() !== this.value) {
                                    that.search(this.value).draw();
                                }
                            });
                        });
                }
            });
        });
    </script>

kthorngren · October 2022

I built a test case for you.
http://live.datatables.net/dehojelo/1/edit

It seems the problem isn't just with trying to use searchBuilder.getDetails() in ajax.data. It causes this error anytime its used.

Uncaught TypeError: Cannot read properties of undefined (reading '_searchBuilder')

Kevin

allan · October 2022

There is a little error in the example - #myTable and #example are used as the table ids which I think is causing the error with the button. The button works with the #example id. However, it does not work is the ajax.data call as SearchBuilder itself hasn't completed its initialisation there.

That said - I'm not sure what the goal is here. SearchBuilder already sends its data to the server when server-side processing is enabled:

That should work if you are using ajax.data to send the data as JSON in the request body does it not?

Allan

kthorngren · October 2022

There is a little error in the example - #myTable and #example are used as the table ids which I think is causing the error with the button.

Not enough coffee

Kevin

jpu · October 2022

I am aware of the example to get the searchbuilder criteria in JSON format (cf. https://datatables.net/extensions/searchbuilder/examples/api/rebuild.html).

I do get the searchBuilder criteria's when the ajax call is executed but only if I leave the ajax.data to JSONIFY the query parameters away. When adding:

"data": function(d) {
                    return JSON.stringify(d);
                }

to ajax.data the searchBuilder fields are missing in the generated JSON structure.

allan · October 2022

Does that not only happen when there are no search builder criteria applied? If you add a condition, there should be parameters there.

Allan

jpu · October 2022

No I tried adding a search builder criteria and I do see the getData XHR request going out, but it doesn't contain the searchBuilder criteria.
Could it be because I added manual column search criteria (as a first expirement) as shown in the initComplete function? I did something similar to https://datatables.net/examples/api/multi_filter.html but instead of adding the search input text fields to the footer I added them to the column headers.

jpu · October 2022

Since allan pointed out that this is an initialization error I could get the following to work:

                    "data": function(d) {
                        var sb = $('#myTable').DataTable().searchBuilder;
                        var sbDetails = null;
                        try {
                            sbDetails = sb.getDetails();
                        } catch(e) {
                            console.log('SearchBuilder not yet initialised!');
                        }
                        d.SearchBuilder = sbDetails;
                        return JSON.stringify(d);
                    }

On page load the searchBuilder settings are skipped (not good for pre-defined searches?) but performing a search afterwards will pass the searchBulder criteria, e.g.:

{
    "columns": [
        {
            "data": "id",
            "name": "id",
            "orderable": false,
            "search": {
                "regex": false,
                "value": ""
            },
            "searchable": false
        },
        {
            "data": "ondernemingsnummer",
            "name": "ondernemingsnummer",
            "orderable": true,
            "search": {
                "regex": false,
                "value": ""
            },
            "searchable": true
        },
        {
            "data": "naam",
            "name": "naam",
            "orderable": true,
            "search": {
                "regex": false,
                "value": ""
            },
            "searchable": true
        },
        {
            "data": "adres",
            "name": "full_address",
            "orderable": true,
            "search": {
                "regex": false,
                "value": ""
            },
            "searchable": true
        },
        {
            "data": "rekeningnummer",
            "name": "rekeningnummer",
            "orderable": true,
            "search": {
                "regex": false,
                "value": ""
            },
            "searchable": true
        },
        {
            "data": "actief",
            "name": "actief",
            "orderable": true,
            "search": {
                "regex": false,
                "value": ""
            },
            "searchable": true
        },
        {
            "data": "id",
            "name": "action",
            "orderable": false,
            "search": {
                "regex": false,
                "value": ""
            },
            "searchable": false
        }
    ],
    "draw": 2,
    "length": 20,
    "order": [
        {
            "column": 0,
            "dir": "asc"
        }
    ],
    "search": {
        "regex": false,
        "value": ""
    },
    "SearchBuilder": {
        "criteria": [
            {
                "condition": "contains",
                "data": "Naam",
                "origData": "naam",
                "type": "string",
                "value": [
                    "Sciot"
                ]
            }
        ],
        "logic": "AND"
    },
    "start": 0
}

But that doesn't solve the case when the page is initially loaded with a predefined search.

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan · October 2022

I'm afraid that in such a case you would probably need to add the predefined search condition manually I'm afraid. I don't currently have a way around that other than to have SearchBuilder initialise up front - which might be how we have to resolve this ultimately.

Allan

jpu · October 2022

Indeed, even when specifying predefined searchCriteria the searchBuilder object isn't initialized when specifying the ajax.data option:

$(document).ready(function() {
    $('#myTable').DataTable({
         "searchBuilder":{
             "preDefined": {
                 "criteria": [
                    {
                        data: 'id',
                        condition: '!null'
                    }
                ],
                 "logic": 'AND'
             }
         },
        "serverSide" : true,
        "search": { return: true },
        "ajax" : {
            "url": "/jqdto/services/jqdto/getData",
            "contentType" : "application/json",
            "type" : 'POST',
            "data": function(d) {
                var sb = $('#myTable').DataTable().searchBuilder;
                var sbDetails = null;
                try {
                    sbDetails = sb.getDetails();
                } catch(e) {
                    console.log('SearchBuilder not yet initialised!');
                }
                d.SearchBuilder = sbDetails;
                return JSON.stringify(d);
            }
        }
    });
});

So any suggestions on how to get the searchBuilder object initialized?

jpu · October 2022

For clarity, the above javascript will log the message 'SearchBuilder not yet iniitialized!' on page load and perform a server-side query without SearchBuilder criteria's. Any search triggered afterwards (defining global search parameter, pagination action, specify SearchBuilder condition,...) will send a server-side request where the searchBuilder critera's are passed in the json field 'SearchBuilder'.

jpu · October 2022

Well I suppose could always do the following:

$(document).ready(function() {
    $('#myTable').DataTable({
        "serverSide" : true,
        "search": { return: true },
        "ajax" : {
            "url": "/jqdto/services/jqdto/getData",
            "contentType" : "application/json",
            "type" : 'POST',
            "data": function(d) {
                var sb = $('#myTable').DataTable().searchBuilder;
                var sbDetails = null;
                try {
                    sbDetails = sb.getDetails();
                } catch(e) {
                    console.log('SearchBuilder not yet initialised!');
            sbDetails = { criteria: [{data: "id",condition: "!null"}],logic: "AND"};
                }
                d.SearchBuilder = sbDetails;
                return JSON.stringify(d);
            }
        }
    });
});

Not really nice, but it will work.

allan · October 2022

Yeah that's more or less what I was thinking. As you say, it is a rubbish solution, but I don't really have a better one atm I'm afraid.

Allan

JSON data request

JSON data request

Answers

Howdy, Stranger!

Categories

DataTables

JSON data request

JSON data request

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables