phpmysqlcodeigniteractiverecordsql-like

How to JOIN a table with a LIKE condition using CodeIgniter's active record

I am having issues with a particular like in my active records query.

When I use:

->join(
    'users parent',
    'child.treePath LIKE CONCAT(parent.treePath,"%")'
)

CodeIgniter renders:

JOIN `users` parent ON `child`.`treePath` `LIKE` CONCAT(parent.treePath,"%")

So, the issue is that CodeIgniter is wrapping LIKE in backticks. How can I tell it to not attempt to format this block?

Complete query:

$this->db
    ->select('child.uuid')
    ->from('users child')
    ->join('users parent', 'child.treePath LIKE CONCAT(parent.treePath,"%")')
    ->where('parent.uuid', $uuid)
    ->where("LENGTH(REPLACE(child.treePath, parent.treePath, '')) - LENGTH(REPLACE(REPLACE(child.treePath, parent.treePath, ''), '/', '')) <= ",  $levels, 'false')
    ->where("LENGTH(REPLACE(child.treePath, parent.treePath, '')) - LENGTH(REPLACE(REPLACE(child.treePath, parent.treePath, ''), '/', '')) > ",  0, 'false')
    ->group_by('child.treeId');
Solution

  • If you are chaining all these functions together in a single call, you might as well just use 

    $this->db->query("Write all your specific SQL here");

    Not seeing the benefit of wrestling with Codeigniter's query builder in your case.