SearchBuilder Criteria

SearchBuilder Criteria

funneldrivefunneldrive Posts: 9Questions: 0Answers: 0

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!

Replies

  • kthorngrenkthorngren Posts: 21,167Questions: 26Answers: 4,921

    [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

  • funneldrivefunneldrive Posts: 9Questions: 0Answers: 0
    edited August 2023

    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.

  • kthorngrenkthorngren Posts: 21,167Questions: 26Answers: 4,921

    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

  • funneldrivefunneldrive Posts: 9Questions: 0Answers: 0

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

  • funneldrivefunneldrive Posts: 9Questions: 0Answers: 0
    edited August 2023

    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'
            }```
    
  • kthorngrenkthorngren Posts: 21,167Questions: 26Answers: 4,921
    edited August 2023

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

  • funneldrivefunneldrive Posts: 9Questions: 0Answers: 0

    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.

  • kthorngrenkthorngren Posts: 21,167Questions: 26Answers: 4,921
    edited August 2023

    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

  • kthorngrenkthorngren Posts: 21,167Questions: 26Answers: 4,921

    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

  • funneldrivefunneldrive Posts: 9Questions: 0Answers: 0

    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.

Sign In or Register to comment.