Editor PHP Library: Issues with where

dclar43

Hello,

Still working on converting my current project over to Editor and have hit a snag.<br/>

In my non-Editor DataTable I am currently doing the following:

Using serverSide

All footers have search inputs of varying types.

Columns that contain a date(stored as epoch timestamps) have a button that opens a jQueryUI dialog containing two jQueryUI datepickers. One for the "From" date and one for the "To" date.

When I click "submit" on the jQueryUI dialog I run something similar to this:

column.search("1438464697,1441143097",).draw();

On the server side when the search is performed I explode at the comma and add the following to my WHERE command(don't worry, I bind everything in the actual code):

WHERE colName BETWEEN fromEpoch AND toEpoch

My current solution for an Editor equivalent of the above looks like this:

$data = $editor
    ->where(function($q) {
        if(!isset($_GET["columns"]))
            return;

        foreach($_GET["columns"] as $col) {
            if(!$col["searchable"] || empty($col["search"]["value"]))
                continue;
            
            switch($col["name"]) {
                case "colName":
                    $range = explode(",", $col["search"]["value"]);

                    $q
                        ->where($col["name"], $range[0], ">=")
                        ->and_where($col["name"], $range[1], "<=");
                    break;
                    /* other special search columns will be added here */
                default:
                    break;
            }
        }
    })
    ->process($_GET)
    ->data();

echo json_encode($data);

This does not populate the table, even when records are found. I tested with the following values:

fromEpoch: 1435726800
toEpoch: 1443589200

Both of the following SQL commands came back with one matching record:

SELECT * FROM tableName WHERE colName BETWEEN 1435726800 AND 1443589200;
SELECT * FROM tableName WHERE colName >= 1435726800 AND Frozen_Spec <= 1443589200;

While running the same values through my code resulted with the following JSON:

{
    "data":[],
    "options":[],
    "files":[],
    "draw":11,
    "recordsTotal":"1",
    "recordsFiltered":"0"
}

Where am I going wrong? Or is there a better or built-in solution to this?

Edit:

Another issue. When setting ajax to GET and responding with the above method using Selector results in this error:

Uncaught TypeError: b.rows(...).ids is not a function
    (anonymous function) @  dataTables.select.js:400
    jQuery.event.dispatch @ jquery-1.11.3.js:4670
    jQuery.event.add.elemData.handle @ jquery-1.11.3.js:4338
    jQuery.event.trigger @ jquery-1.11.3.js:4579
    (anonymous function) @ jquery-1.11.3.js:5289
    jQuery.extend.each @ jquery-1.11.3.js:384
    jQuery.fn.jQuery.each @ jquery-1.11.3.js:136
    jQuery.fn.extend.trigger @ jquery-1.11.3.js:5288
    _fnCallbackFire @ jquery.dataTables.js:5242
    _fnBuildAjax @ jquery.dataTables.js:2494
    _fnAjaxUpdate @ jquery.dataTables.js:2543
    _fnDraw @ jquery.dataTables.js:2007
    _fnReDraw @ jquery.dataTables.js:2126
    (anonymous function) @ jquery.dataTables.js:7285
    _Api.iterator @ jquery.dataTables.js:6875
    (anonymous function) @ jquery.dataTables.js:7284
    _Api.extend.methodScoping @ jquery.dataTables.js:7038
    (anonymous function) @ (index):286
    jQuery.event.dispatch @ jquery-1.11.3.js:4670
    jQuery.event.add.elemData.handle @ jquery-1.11.3.js:4338

where dataTables.select.js is https://cdn.datatables.net/select/1.0.1/js/dataTables.select.js

I am currently using $_GET as I was running into issues using $_POST with my above search code.

jLinux

So the JSON you showed, where it shows total records 1, the data should contain that record, right?

Have you tried to add some debugging to the PHP for more info? Perhaps logging the $_GET array somewhere?

Are you using a framework? Possibly CI? If you are using CI, I know you can use the db->last_query() to see the query, can you log that and provide it?

If its something to do with the PHP, then I think I can help you out. I dont have EDITOR, but I know a good amount of PHP

dclar43

Yes the JSON would normally contain something like(different data format)

{ "data": [{": "Tiger Nixon",": "System Architect",": "$320,800",": 1351507881,": ",": "5421",": 768596160}}

Yes I've gone through the supplied $_GET params which are correct. I've also simply hard coded in the values that supply a known result to cut off client-supplied bugs.

No framework being used.

I don't believe it's PHP related, but more so my usage or expectation of how the PHP Library works.

Thank you for the input though!

allan

Uncaught TypeError: b.rows(...).ids is not a function

This is the easy one - likely you have an old version of DataTables that doesn't support rows().ids(). If you could update to 1.10.9 that should be resolved.

The other question is a little more tricky - I has a suspicion that the libraries are performing their own where condition based on the submitted data, as well as your custom where statement - which is causing the data set to be filtered out fully.

The reason I suspect this to be the case is that you are using $col["search"]["value"] which is part of the DataTables default parameters it submits for server-side processing, and Editor will act on them. so for example - if you submit a timestamp of 1234567890 for the column you will get something along the lines of:

WHERE
  col LIKE '%1234567890' AND % Editor's default filtering
  col >= 1234567890 AND
  col <= 1234567890

That obviously is not what you want!

So two options:

1. Remove the search value from the GET data once you have used it yourself (i.e. before it is passed to ->process(), or
2. Use a custom parameter.

For 1 (since it is probably the least work), I would suggest changing:

foreach($_GET["columns"] as $col) {

to be:

foreach($_GET["columns"] as &$col) {

and then at the end of your loop, once you have used the parameter, set it to be an empty string:

$col["search"]["value"] = '';

Regards,
Allan

dclar43

Thanks Alan,

Looks like that sorted everything out. As it stands I've decided to grab the values and build an array for all search terms. For anyone who might care how I ended up implementing it:

<?php

// pseudo code
// $_POST["columns"][columnIndex]["search"]["value"] is 1441083600,1443589200
// I use a switch statement to pick the columns that need special search handling
// Once I land on a column that searches for a date range

$range = explode(",", $col["search"]["value"]);

// Add the search values to an array. The structure ends up producing something similar to the array below
$searchColumns = [
    "colName" => [
        [
            "value" => $range[0],
            "operator" => ">="
        ],
        [
            "value" => $range[1],
            "operator" => "<="
        ]
    ],
    // More columns go here
];
    
// Set the search value to an empty string. This prevents Editor from processing it as well
// Be sure to unset the reference to avoid any accidental usage
    
Editor::inst($db, "dbName")
->fields(
    // Fields
)
->where(function($q) use ($searchColumns) {
    // PHPDoc to tell PHPStorm what type $q is (had to escape the at sign due to markdown)
    /** \@var \DataTables\Database\DriverMysqlQuery $q*/

    foreach($searchColumns as $colName => $terms) {
        foreach($terms as $term) {
            $q->where($colName, $term["value"], $term["operator"]);
        }
    }
})
->process($_POST)
->json();

allan

Excellent - thanks for posting back with your code.

Allan