Server side processing not searching specific column

pgl

Hi, i have a server side enabled datatable. I can search by other columns except one of the columns status_name

The implementation code is as follows.

// DB table to use | Use a subquery as table
        $subQuery = "select table1.id,.......
                    , ifnull(table2.id, 2) as processing_status
                    , ifnull(table2.description, 'Pending') as processing_status_name
                    from table1
                    left join table2 .................
                    ......................................
                    where ..... order by table1.created desc";
        $table = "($subQuery) AS table1";

        // Table's primary key
        $primaryKey = 'id';

        $columns = array(
            ..................................................................,
            array('db' => 'processing_status_name', 'dt' => 9),
            array('db' => 'processing_status', 'dt' => 10),
            array('db' => 'id', 'dt' => 11),
            array('db' => 'application_id', 'dt' => 12)
        );

        return SSP::complex($_GET, $this->em->getConnection(), $table, $primaryKey, $columns, null, null);

Here is the datatable initialization

<table id="records-datatable"
                           class="table cell-border hover stripe app-table app-table-clickable">
                        <thead>
                        <tr>
                            <th>Serial#</th>
                            <th>Number</th>
                            <th>Surname</th>
                            <th>Firstname</th>
                            <th>Mobile Number</th>
                            <th>Date of Birth</th>
                            <th>State</th>
                            <th>CNT</th>
                            <th>Expiry Date</th>
                            <th>Processing Status</th>
                            <th>Actions</th>
                        </tr>
                        </thead>
                        <tfoot>
                        <tr>
                            <th>Serial#</th>
                            <th>Number</th>
                            <th>Surname</th>
                            <th>Firstname</th>
                            <th>Mobile Number</th>
                            <th>Date of Birth</th>
                            <th>State</th>
                            <th>CNT</th>
                            <th>Expiry Date</th>
                            <th>Processing Status</th>
                            <th>Actions</th>
                        </tr>
                        </tfoot>
                    </table>

recordsDataTable = $('#records-datatable').DataTable({
                "processing": true,
                "serverSide": true,
                "ajax": ".....................",
                "deferRender": true,
                "order": [],
                "pageLength": 25,
                "language": {
                    "emptyTable": "No matching records found",
                    "processing": '<div class="spinner-border text-dark mt-2 mb-2" role="status">\n' +
                        '  <span class="sr-only">Loading...</span>\n' +
                        '</div>'
                },
                "columnDefs": [
                    {
                        "targets": -1,
                        "data": null,
                        "orderable": false,
                        "render": function (data, type, row, meta) {
                            return '<div class="dropdown app-dropdown-md dropright">\n' +
                                '  <button class="btn btn-primary app-btn-xs dropdown-toggle" type="button" id="dropdownMenuButton" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">' +
                                '    Action' +
                                '  </button>' +
                                '  <div class="dropdown-menu" aria-labelledby="dropdownMenuButton">' +
                                '    <a class="dropdown-item app-fancybox-iframe" href="' + recordDetailUrl + '?q=' + data[12] + '">Record Details</a>' +
                                '  </div>' +
                                '</div>';
                        }
                    }
                    , {
                        "targets": -2,
                        "data": null,
                        "render": function (data, type, row, meta) {
                            let badgeType = (data[9] === "Pending") ? 'danger' : 'success';
                            return '<span class="badge badge-' + badgeType + ' tx-white">' + data[9] + '</span>';
                        }
                    }
                ]
            });

I cannot search by the processing status column. It returns 0 records when i do

allan

I suspect it will be related to the sub-query you are cunningly putting in using the sub-query, since the SSP class has no special logic for that.

What I would suggest doing is echoing out the query that the class builds, and its bindings and then work back from there (including posting it here ).

Allan

pgl

Hi @allan , thanks for your reply.

But i replaced data[..] with row[..] and added the data index and its working okay

{
    "targets": -2,
    "data": 9,
    "render": function (data, type, row, meta) {
        let badgeType = (row[9] === "Pending") ? 'danger' : 'success';
        return '<span class="badge badge-' + badgeType + ' tx-white">' + row[9] + '</span>';
     }
}

pgl

Hi @allan , i noticed your comment like 'sub-query you are cunningly putting' . How can one use joins with server side without using a subquery. If you can point to some examples, that would be great.

Thanks again

allan

With the demo SSP class - there isn't a join API I'm afraid. You could use a VIEW, but a better option would be to use the Editor PHP classes, which do support joins. Even if you don't have an Editor license, you can still use the PHP libraries for it (which are MIT licensed).

Allan