Can't get searchBuilder to read formatted field server side.

Stacey1134

In a particular table I need search builder to select by birth month. Because of the size of my recordset it has to be server side.
I am using self-referencing join that way the data is always accurate and updated. The problem is, searchBuilder is still grabbing the raw data.

I am not getting errors, because nothing is wrong with the code, and I cannot fix it from outside the function.
Any guidance would be a great help. I have included a screenshot of what is showing, instead of the three character month.

/* Server Side */
Editor::inst($db, 'Members', 'FOW_ID')
    ->field( 
        Field::inst('Members.FOW_ID'),
        ...       
        Field::inst('Members.Birth_Date')
            ->searchBuilderOptions(SearchBuilderOptions::inst()),
        Field::inst('Birthday.Birth_Date')
            ->getFormatter(function ($val, $data, $opts) {
                if ($val === '0000-00-00' || empty($val)) {
                    return 'UNK'; // Handle invalid dates
                }
                return date('M', strtotime($val));
            })
            ->searchBuilderOptions(SearchBuilderOptions::inst()),
        ... 
    )
    ->leftJoin( 'Members as Birthday', 'Birthday.FOW_ID', '=', 'Members.FOW_ID') 
    ->debug(true)
    ->process($_POST)
    ->json();

/* Client Side */
var memberTable = $('#memberTable').DataTable( {
    serverSide: true,
    processing: true,
    language: {
    processing: '<i class="fa-duotone fa-gear fa-spin"></i> Data Loading...',    
        searchBuilder: {
            button: '<i class="fa-sharp fa-solid fa-magnifying-glass-arrow-right"></i> ' ,
            title: 'Choose Filter Options'
            }
        },
    buttons:[
        '<i class="fa-sharp fa-solid fa-magnifying-glass-arrow-right"></i>' 
    ],
    ajax: {
        url: 'dataMember.php',
        type: "POST"

    },
    drawCallback: function (settings) { 
        // Here the response
        var response = settings.json;
        console.log(response);
    },                    
    pageLength : 5,
    lengthMenu: [[5, 10, 20, -1], [5, 10, 20, 'All']],              
    columns: [
        { data: "Members.FOW_ID", visible: false },
        ...
        { data: "Members.Birth_Date", 
            render: function (data, type, row) {
                if (type === 'filter' || type === 'type') {
                        return data === '0000-00-00' ? null : data;
                    }
                return data;
            },                            
            visible: false, searchable: false  },
        {
            data: "Birthday.Birth_Date",
            visible: true,
            searchable: true,
            render: function (data, type, row) {
                return data ? data : 'UNK'; // Ensure it returns 'UNK' if data is null
            }
        },
    ],
    dom: 'Blfrtip',
    buttons: [
        {
        ...
    select: true,
    lengthMenu: [[5,10,25,50,-1],[5,10,25,50,"All"]],
    pageLength: 5,
    autoWidth: true,
    order: [[ 3, 'asc' ], [ 4, 'asc' ]],
    initComplete: function() {
        // Show the table once DataTables has finished initializing
    // $('#memberTable').removeClass('hidden');
    // $('#historyTable').removeClass('hidden');
    $('#massHide').removeClass('hidden');
    memberTable.columns.adjust().draw();
    historyTable.columns.adjust().draw();
    $('.spinner-border').hide();
}  
      

kthorngren

You might need to define the format for Datatables. See these examples:
https://datatables.net/extensions/searchbuilder/examples/initialisation/date-fmt.html
https://datatables.net/extensions/searchbuilder/examples/initialisation/date-fmt-luxon.html

Kevin

Stacey1134

I tried that lead, and it was worse off unfortunately. I added:

$.fn.dataTable.moment('MMM');

which required me to add

<script src="https://cdn.datatables.net/plug-ins/1.10.24/sorting/datetime-moment.js">

That undid all the formatting sent from the server-side-script and left nothing for the searchBuilder to use. Not to mention datetime-moment.js is not compatible with DataTable's Bootstrap 5 plugin, it gives a formatting error that Allen was never able to fix on my site.

Using that lead I also added

        config: {
            depthLimit: 1
        }

to my searchBuilder button. Lastly, I also earlier added a postGet function and to no effect

    ->leftJoin('Members as Birthday', 'Birthday.FOW_ID', '=', 'Members.FOW_ID')
    ->on('postGet', function ($editor, &$data, $id) {
        foreach ($data as &$row) {
            if (isset($row['Birthday']['Birth_Date'])) {
                $val = $row['Birthday']['Birth_Date'];
                if ($val === '0000-00-00' || empty($val)) {
                    $row['Birthday']['Birth_Date'] = 'UNK';
                } else {
                    $row['Birthday']['Birth_Date'] = date('M', strtotime($val));
                }
            }
        }
    })

At this point I don't know what to do. I even used a formatting function serverside, and that didn't an actual field, and the best searchBuilder did was read give me 0-12 as an option. I am calling it a day, cause I know there is something simple I am missing.

kthorngren

Sorry I misread your question. I'm not familiar with the PHP libraries. Use the browser's network inspector to see the JSON response. do you see the raw date or the three character month?

Looks like your server code is returning UNK if the date is 0000-00-00. You will likely need to update the Members.Birth_Date to handle this. Something like this:

        { data: "Members.Birth_Date",
            render: function (data, type, row) {
                if (type === 'filter' || type === 'type') {
                        return data === 'UNK' ? null : data;
                    }
                return data;
            },                           
            visible: false, searchable: false  },

Kevin

Stacey1134

Members.Birth_Date is okay, it reads correctly, and updates fine. UNK is being sent to my alias Birthday.Birth_Month is formatted as I requested and shows up in datatables fine. The problem is searchBuilder is not reading the format I am sending from the server on Birthday.Birth_Month. Yes, the JSON Response is fine for both Members.Birth_Date and Birthday.Birth_Month. I have no idea why searchBuilder refuses to accept the format.