JSON data request

JSON data request

jpujpu Posts: 16Questions: 5Answers: 0
edited September 2022 in Free community support

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.

Answers

  • jpujpu Posts: 16Questions: 5Answers: 0

    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);
    }
    }

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • jpujpu Posts: 16Questions: 5Answers: 0

    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.

  • jpujpu Posts: 16Questions: 5Answers: 0

    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.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • jpujpu Posts: 16Questions: 5Answers: 0
    edited 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

  • jpujpu Posts: 16Questions: 5Answers: 0

    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>
    
  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    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

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    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 :smile:

    Kevin

  • jpujpu Posts: 16Questions: 5Answers: 0

    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.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

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

    Allan

  • jpujpu Posts: 16Questions: 5Answers: 0

    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.

  • jpujpu Posts: 16Questions: 5Answers: 0
    edited 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.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • jpujpu Posts: 16Questions: 5Answers: 0

    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?

  • jpujpu Posts: 16Questions: 5Answers: 0

    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'.

  • jpujpu Posts: 16Questions: 5Answers: 0

    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.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

Sign In or Register to comment.