Backticks breaking complex joins when using Active Record

[eluser]Nathan Pitman (Nine Four)[/eluser]
I'm using active record to return some data and need to apply a couple of conditions to a JOIN. One of these conditions evaluates as to whether a column in the joined table has a value matching a string which I provide:

Code:

$this->db->select("c.id");
  $this->db->from("clients c");
  $this->db->join("clients_programmes cp", "cp.p_close_reason='none' AND cp.client_id=c.id", "left");
  $query = $this->db->get();

However whenever I try to apply this simple join CI throws an error because backticks are inserted around the 'none' string. I'm aware that 'select' has a parameter to disable backticks but this makes no odds to the join... any ideas?

I don't really want to have to rewrite an entire function to construct the query manually and pass it to '$this->db-query()', I'm hoping there's some other way to work around this or 'insert' the join into active record without using the 'join' function???

[eluser]Nathan Pitman (Nine Four)[/eluser]
Well I found a solution of sorts, If the JOIN is going to include anything more complex than a simple comparison I prepend it with "$this->db->_protect_identifiers = false;" and then append "$this->db->_protect_identifiers = true;".

Hacky but it works.

[eluser]Nathan Pitman (Nine Four)[/eluser]
Also, looks to be fixed in CI 3 by this commit:

[url="https://github.com/EllisLab/CodeIgniter/commit/428702387ca071db4686ec6d6c60bd35b01c33e4"]https://github.com/EllisLab/CodeIgniter/commit/428702387ca071db4686ec6d6c60bd35b01c33e4[/url]

[eluser]Brad K Morse[/eluser]

Code:

$this->db->select("c.id");
  $this->db->from("clients c");
  $this->db->join("clients_programmes cp", "c.id = cp.client_id", "left");
  $this->db->having("cp.p_close_reason", "none");
  $query = $this->db->get();

Try that, $this->db->having() might do.

Or

Code:

$this->db->select("c.id");
  $this->db->from("clients c");
  $this->db->join("clients_programmes cp", "c.id = cp.client_id", "left");
  $this->db->where("cp.p_close_reason", "none");
  $query = $this->db->get();

[eluser]Nathan Pitman (Nine Four)[/eluser]
Thanks Brad, in the end I took a different approach and got at the same result with a sub query in the select. I've not used having before but I'll have to read up a bit on that. Thanks for taking the time to reply!

[eluser]Brad K Morse[/eluser]
Care to share the query? Maybe I can help optimize it in Active Record, it makes for easier readability.

[eluser]Nathan Pitman (Nine Four)[/eluser]
Sure though it's a little divorced from my original question now here it is for reference:

Code:

SELECT c.id,
   (SELECT COUNT(*) FROM clients_programmes cp
      WHERE (cp.client_id=c.id) AND (c.account_type='online') AND (c.active=1) AND ((cp.used > cp.the_limit AND cp.hold=0) OR (date_add(cp.date, interval 1 YEAR) < date(now()) AND cp.the_limit > 1))) as attn,
   (SELECT COUNT(*) FROM clients_programmes cp
      WHERE (cp.client_id=c.id) AND (cp.p_type <> 'none' AND cp.p_close_reason = 'none')) as approvals
FROM (`clients` c)

[eluser]Brad K Morse[/eluser]
I guess you're right, if that works, great!

[eluser]Nathan Pitman (Nine Four)[/eluser]
It does, frustratingly I spent half my day trying to make it work as a join first. I'm a muppet.

[eluser]Brad K Morse[/eluser]
I see where it makes it hard to make it a join, trying to get those count aliases with the sub queries.