SearchBuilder Criteria

funneldrive

So search criteria values send:

searchBuilder[criteria][0][value1]: Cool
searchBuilder[criteria][0][value][]: Cool

What's the difference between the 2 and which one should I be using in my queries so far they always seem to be the same in my case. This stuff is tough to work in coldfusion because those variable names are difficult since the syntax is illegal in CF.

I'm writing some nice functions for CF to parse it all out. Nobody has one for me to "borrow" took me awhile to figure it out. I'll posit it if people are interested.

Thanks!

kthorngren

[value][] is an array of search terms. For example using a between search for dates. [value1] is the first value in the array. If a between search is performed there would be a [value2] with the second array value. This is a date range example:

searchBuilder[criteria][0][condition]: between
searchBuilder[criteria][0][data]: Start date
searchBuilder[criteria][0][origData]: start_date
searchBuilder[criteria][0][type]: date
searchBuilder[criteria][0][value][]: 2023-08-02
searchBuilder[criteria][0][value][]: 2023-08-26
searchBuilder[criteria][0][value1]: 2023-08-02
searchBuilder[criteria][0][value2]: 2023-08-26
searchBuilder[logic]: AND

See the searchBuilder.preDefined.criteria.value docs for more details.

This stuff is tough to work in coldfusion because those variable names are difficult since the syntax is illegal in CF.

Possible you can use ajax.data to convert the parameters sent to JSON format. See the last example in the docs.

Kevin

funneldrive

Wow! I wish I had know about that ajax json would have saved me a lot of time parsing stuff, But it introduced a really weird problem. So when I load the table the first time everything looks great I get the json parse it up No problem thought I'm home free. But now When I click the column to say sort by it I get a javascript error and it won't post the data.

 datatables.min.js:26 Uncaught TypeError: Cannot create property 'searchBuilder' on string '{"draw":2,"columns":[{"data":"ID","name":"","searchable":false,"orderable":false,"search":{"value":"","regex":false}},{"data":"UHID","name":"","searchable":true,"orderable":true,"search":{"value":"","regex":false}},{"data":"CATEGORY","name":"","searchable":true,"orderable":true,"search":{"value":"","regex":false}},{"data":"QUESTION","name":"","searchable":true,"orderable":true,"search":{"value":"","regex":false}},{"data":null,"name":"","searchable":true,"orderable":true,"search":{"value":"","regex":false}}],"order":[{"column":2,"dir":"asc"}],"start":0,"length":25,"search":{"value":"","regex":false}}'
    at HTMLTableElement.<anonymous> (datatables.min.js:26:59267)
    at HTMLTableElement.dispatch (jquery.min.js:2:43064)
    at v.handle (jquery.min.js:2:41048)
    at Object.trigger (jquery.min.js:2:71515)
    at HTMLTableElement.<anonymous> (jquery.min.js:2:72110)
    at Function.each (jquery.min.js:2:3003)
    at S.fn.init.each (jquery.min.js:2:1481)
    at S.fn.init.trigger (jquery.min.js:2:72086)
    at R (datatables.min.js:16:47659)
    at Tt (datatables.min.js:16:26510)

Here is my table code:

<script>
    $(document).ready(function() {
        var table = $('#aiDataTable').DataTable({
            dom: 'Qlfrtip',
            ajax: {
                url: 'aimgmtController.cfm',
                type: 'POST',
                contentType: "application/json",
                data: function ( d ) {
                    return JSON.stringify( d );
                },
                dataSrc: 'data'
            },
            
            searchBuilder: { 
                searchBuilder: true
            },

            serverSide: true,
            processing: true,
            search: {
                return: true
            },
            pageLength: 25, // Initial number of entries per page
            
            columnDefs: [
                {
                    targets: 0,
                    data: 'ID',
                    searchBuilderTitle: 'ID',
                    orderable: false,
                    searchable: false,
                    render: function(data, type, row) {
                        return '<input type="checkbox" class="row-checkbox" value="' + row.ID + '">';
                    }
                },
                {
                        targets: 1, // UHID column
                        data: 'UHID',
                        render: function(data, type, row) {
                            var url = '/Hotel/HotelRoomTypes.htm?HotelID=' + data + '&idtype=Universal#HotelName';
                            return '<a href="' + url + '" target="_blank">' + data + '</a>';
                        }
                },                

                { targets: 2, data: 'CATEGORY' },
                { targets: 3, data: 'QUESTION' },
                {
                    targets: 4,
                    data: null,
                    searchBuilderTitle: 'Answer',
                    render: function(data, type, row) {
                        return '<div class="split-cell"><strong>' + row.PRETTY_QUESTION + '</strong></div><div class="split-cell">' + row.NEW_ANSWER + '</div>';
                    }
                },
            ],
            order: [],
        language: {
            // Customize the information at the bottom of the table
            info: 'Showing _START_ to _END_ of _TOTAL_ entries',
            infoEmpty: 'No entries to show',
            infoFiltered: ''
            // ... you can customize other text strings as well ...
        },
        drawCallback: function(settings) {
            var api = this.api();
            var info = api.page.info();
            var customInfo = 'Displaying records ' + (info.start + 1) + ' to ' + info.end + ' out of ' + info.recordsTotal;
            $('.dataTables_info').html(customInfo);
        }
        });

        var headerCheckbox = $('#selectAllCheckboxes');
        var rowCheckboxes = $('.row-checkbox');

        headerCheckbox.on('change', function() {
            rowCheckboxes.prop('checked', this.checked);
        });

        rowCheckboxes.on('change', function() {
            headerCheckbox.prop('checked', rowCheckboxes.length === rowCheckboxes.filter(':checked').length);
        });

        // Synchronize checkboxes on table draw
        table.on('draw', function() {
            rowCheckboxes = $('.row-checkbox');
            headerCheckbox.prop('checked', rowCheckboxes.length === rowCheckboxes.filter(':checked').length);
        });

    $('#submitSelectedRows').on('click', function() {
        var selectedIDs = [];
        var unselectedIDs = [];

        // Get the IDs of selected rows and unselected rows
        rowCheckboxes.each(function() {
            var id = $(this).val();
            if (this.checked) {
                selectedIDs.push(id);
            } else {
                unselectedIDs.push(id);
            }
        });

        // Display selected and unselected IDs in the console
        console.log('Selected IDs:', selectedIDs);
        console.log('Unselected IDs:', unselectedIDs);
    });
    table.searchBuilder.container().prependTo(table.table().container());
 });
</script>

Strange part is this wasn't an issue until I added the json content and data properties If I remove those no javascript errors. I'm at a loss here. BTW, I want to say thank you for your prompt responses. I'm working on a deadline and trying to get this done. You guys rock.

kthorngren

It seems like SearchBuilder might be adding its parameters after the ajax.data function runs. Looks like SearchBuilder's parameters are added before the preXhr which allows JSON.stringify() to be used at the proper time. For example:
https://live.datatables.net/qoliyehi/50/edit

Kevin

funneldrive

Ok looks like you are right. But, this is kinda way over my js knowledge here. How would I go about fixing that? Seems to be kind of a big bug to use searchbuilder with ssp...

funneldrive

In your example you left out the contentType: "application/json", and thats where it kinda falls apart. I really want to use json really bad and avoid all the crazy string parsing I had to write. You get the same error when you add that. Everythings works fine if you don't opt for json send.

I'm still getting the error: SearchBuilder not yet initialised! datatables.min.js:26 Uncaught TypeError: Cannot create property 'searchBuilder' on string '{"draw":2,"columns":[{"data":"UHID","name":"","searchable":true,"orderable":true,"search":{"value":"","regex":false}}],"order":[{"column":0,"dir":"asc"}],"start":0,"length":25,"search":{"value":"","regex":false},"SearchBuilder":{}}' at HTMLTableElement.<anonymous> (datatables.min.js:26:59267) at HTMLTableElement.dispatch (jquery.min.js:2:43064) at v.handle (jquery.min.js:2:41048) at Object.trigger (jquery.min.js:2:71515) at HTMLTableElement.<anonymous> (jquery.min.js:2:72110) at Function.each (jquery.min.js:2:3003) at S.fn.init.each (jquery.min.js:2:1481) at S.fn.init.trigger (jquery.min.js:2:72086) at R (datatables.min.js:16:47659) at Tt (datatables.min.js:16:26510)

using this: ``` var table = $('#aiDataTable').DataTable({
dom: 'Qlfrtip',
ajax: {
url: 'aimgmtController.cfm',
type: 'POST',
contentType: "application/json",

                data: function ( d ) {
                      var sb = $('#aiDataTable').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 );
            },


            dataSrc: 'data'
        }```

kthorngren

That is the wrong example - it was something I was looking at from another thread. It doesn't work as you see. It seems my changes to that example weren't saved Try this instead:
https://live.datatables.net/gupumija/1/edit

Will let @allan comment on whether he feels this is a bug or not. The preXhr docs have this comment:

The preXhr event is designed for use by plug-in authors who can listen for this event in their extension and send any additional data that might be required

Since SearchBuilder is an extension it doesn't directly affect/interact with the Datatables server side processing code/ajax. Instead it probably uses something like the `-event preXhr event to add its parameter data.

Kevin

funneldrive

Hi Kevin, First let me just say I'm very grateful for your help here. Unfortunately, your solution kinda works but not really. Yes, the js error went away but its not really doing the stringfy.

contentType: "application/json", now its sending unstringified json

otherwise in the example its just sending it the standard way. Doesn't solve my issue. So I tried adding the contentype and he
"data": function ( d ) { return JSON.stringify( d ); }
and getting js errors again. Ugggh, I'm thinking I'm doomed here... Man, I'd buy you a beer for just trying man... unless you have a better magic trick up your sleeve. I know enough js to be dangerous that's about it.

kthorngren

Sorry you are right. I didn't pay that close attention.

Its interesting that the preXhr data object can have objects added but converting it to a JSON string doesn't work. This example shows that the preXhr event fires before SearchBuilder adds it's objects. So it wouldn't work anyway. Perform a SearchBuilder search to see this.
https://live.datatables.net/gupumija/4/edit

I don't see a way to send JSON data when using SearchBuilder. @allan will need to provide input.

I'm not familiar with ColdFusion but I wonder if there are libraries to parse the Javascript Form Data sent in Ajax into JSON or format CF supports. I did something similar in Python once and found it quite a pain to parse the Form Data.

Kevin

kthorngren

I found a workaround. It requires changing the SearchBuilder.js source. Comment out line 3388 in dataTables.searchBuilder.js version 1.5.0, specifically this:

            this.s.dt.on('preXhr.dtsb', function (e, settings, data) {
                if (_this.s.dt.page.info().serverSide) {
                    //data.searchBuilder = _this._collapseArray(_this.getDetails(true));
                }
            });

Then that example I accidentally linked to works. The Javascript tab of this example has dataTables.searchBuilder.js with the commented line data.searchBuilder = _this._collapseArray(_this.getDetails(true));.
https://live.datatables.net/yudeboda/1/edit

It doesn't look like the searchBuilder[criteria][0][value1] objects are added but the SearchBuilder parameters are now in JSON format. You will need to access the value array to get the search term.

You can do this because the code is open source. The risk is the change will be reverted if you upgrade SearchBuilder. I haven't fully test this but it does seem to work.

Hopefully @allan has a better option.

Kevin

funneldrive

Thank you so much... I'm going to try this but I'm under the gun to get this delivered so I ended up parsing everything. I'm going to post up the CF code for it. But, yeah json would have been easy peasy. I'm going to double back and get this working because its ridiculous the code i had to write JSON should just be the default incoming/outgoing.