DB Error - WHERE () IS NULL

[sargac]

Hi all,

following a "Basic DatatableModel Implementation", and a basic example usage, I got a "A Database Error Occurred" error message, where assembled query looks like:

SELECT u.id FROMusers`u WHERE () IS NULL`

I thought I had to write "where" clause using a "whereClauseArray" function, but then I got this:

SELECT u.id FROMuser`u WHERE () IS NULL AND u.id = 4`

In Controller there is only this:

$this->load->library('Datatable', array('model' => 'datatable_model')); $json = $this->datatable->datatableJson(); $this->output->set_header("Pragma: no-cache"); $this->output->set_header("Cache-Control: no-store, no-cache"); $this->output->set_content_type('application/json')->set_output(json_encode($json));

Can anybody help me understand where does this WHERE () IS NULL come from, and where do I assemble this basic WHERE (maybe I got that wrong).

Best!

[maxleaver]

In the whereClauseArray function in your model, you should have either:

return NULL;

or an array of key/value pairs where the key is the name of your column:

return array('u.id' => 4);

It looks like you may be passing an empty key/value pair somehow. Can you post the content of your whereClauseArray function?

[sargac]

Thank you for helping me out! I tried both ways, so I try to use either:

public function whereClauseArray() { return NULL; }

in that case I get WHERE () IS NULL or I try to use this:

public function whereClauseArray() { return array( 'u.id' => 4 ); }

and in that case I get this: WHERE () IS NULL AND u.id= 4

Here's my complete model:

class Datatable_model extends CI_Model implements DatatableModel
{
    public function appendToSelectStr() {
        return NULL;
    }
    public function fromTableStr() {
        return 'user u';
    }
    public function joinArray() {
        return NULL;
    }
    public function whereClauseArray() {
        return NULL;
    }
}

[maxleaver]

Very strange. Your model and controller look correct. The code in the library that appends the WHERE clause to the query is pretty straightforward:

$wArray = $this->model->whereClauseArray();
if (is_null($wArray) === FALSE && is_array($wArray) === TRUE && count($wArray) > 0) {
    $this->CI->db->where($wArray, $this->protectIdentifiers);
}

With the way Codeigniter stacks Query Builder/Active Record calls until they're executed, is it possible you have a stray unexecuted $this->db->where() somewhere that's getting added to the datatables query? It should be easy enough to test by adding the following to your controller before the library is loaded:

$this->db->flush_cache();
$this->db->reset_query();

[sargac]

I've just added flush_cache/reset_query and it didn't help. I've tested for stray $this->db statements as well, and there are none to be found. That's all I currently have in that controller:

class Users extends CI_Controller
{
    public function __construct()
    {
        parent::__construct();
    }

    public function index()
    {
        $this->load->library('design');
        $this->design->header('users');
        $this->load->view('users');
        $this->load->view('footer');
    }

    public function index_data()
    {
        $this->db->flush_cache();
        $this->db->reset_query();

        $this->load->library('Datatable', array('model' => 'datatable_model'));
        $json = $this->datatable->datatableJson();
        $this->output->set_header("Pragma: no-cache");
        $this->output->set_header("Cache-Control: no-store, no-cache");
        $this->output->set_content_type('application/json')->set_output(json_encode($json));
    }
}

[maxleaver]

Darn. I was hoping that would work. For what it's worth, I set your controller and model up on my end to try to reproduce the issue. The query ran fine with no additional WHERE () IS NULL statements and the table works fine. So, your controller and model code is fine.

I'm guessing it's something outside the library. Maybe try a normal Query Builder/Active Record call in place of the library to see if it also gets the extra where statement?

[zepernick]

Hello, would you be able to use a developer tool to see what the grid is posting, maybe there is an issue with the way a parameter is posting is causing that to happen somehow.

[sargac]

@maxleaver Thank you very much for taking time and test on your side. I tried using a normal Query Builder and there was no extra statement. Then I re-read @zepernick reply and although I thought it couldn't be JS causing the issue, cleaned up JS and it worked!

The culprit was a commented serverSide: true option, to minimize number of requests to the server. I am not sure how can a missing/false serverSide option cause the extra WHERE () IS NULL, but there it was.

So thank you both, for helping me out with the issue -- that I am closing now, as it's solved!

[sutejoramadhan]

i have same problem with you @sargac . please can you tell me how yo fix it?

[sargac]

As explained above, try to remove serverSide: true from your JS code. At least that helped in my case.

Best!