Codeigniter 3 query builder auto quote wrongly

问题:

I'm am trying to get the sql format below

SELECT * FROM `ci_nest` WHERE `lft` > 9 AND `rgt` < 28 AND `rgt` = `lft` + 1 ORDER BY `lft`

However Codeigniter 3 is inserting the quotes at the wrong place.

My Code as follows

$this->db->where($leftcol . ' > ' . $leftval . ' AND ' . $rightcol . ' < ' . $rightval);
$this->db->where($rightcol . " = " . $leftcol . " +1");
$this->db->order_by($leftcol);
$query = $this->db->get($this->table_name);

What codeigniter query output is

SELECT *
FROM `ci_nest`
WHERE `lft` > 9 AND `rgt` < 28
AND `rgt` = `lft` `+1`
ORDER BY `lft`

As you can see at the line and rgt = lft + 1 is being formatted wrongly by codeigniter 3 query builder.

Any workaround for this issue would be appreciated.

回答1:

Disable the backticks by the optional 3rd parameter and create them by your own.

$this->db->where($rightcol, '`'.$leftcol.'`+1', FALSE);

Or with double quotes, seems better.

$this->db->where($rightcol, "`$leftcol`+1", FALSE);

回答2:

For now a quick fix I found is to use Codeigniter 3 query method instead of the query builder.

$query = $this->db->query("SELECT * FROM `{$this->table_name}` WHERE 
`{$leftcol}` > {$leftval} AND `{$rightcol}` < {$rightval} AND
`{$rightcol}` = `{$leftcol}` + 1 ORDER BY `{$leftcol}`");

回答3:

Try this :

$this->db->where($leftcol . ' > ' . $leftval . ' AND ' . $rightcol . ' < ' . $rightval);
$this->db->where($rightcol . " = " . $leftcol + 1);
$this->db->order_by($leftcol);
$query = $this->db->get($this->table_name);