Backticks breaking complex joins when using Active Record

Backticks breaking complex joins when using Active Record - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Backticks breaking complex joins when using Active Record (/showthread.php?tid=53943)

Pages: 1 2

Backticks breaking complex joins when using Active Record - El Forum - 08-15-2012

[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???

Backticks breaking complex joins when using Active Record - El Forum - 08-15-2012

[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.

Backticks breaking complex joins when using Active Record - El Forum - 08-15-2012

[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]

Backticks breaking complex joins when using Active Record - El Forum - 08-15-2012

[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();

Backticks breaking complex joins when using Active Record - El Forum - 08-15-2012

[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! Smile

Backticks breaking complex joins when using Active Record - El Forum - 08-15-2012

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

Backticks breaking complex joins when using Active Record - El Forum - 08-15-2012

[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)

Backticks breaking complex joins when using Active Record - El Forum - 08-15-2012

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

Backticks breaking complex joins when using Active Record - El Forum - 08-15-2012

[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.

Backticks breaking complex joins when using Active Record - El Forum - 08-15-2012

[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.