Codeigniter ActiveRecord: join backticking

I’ve a simple question: how can I use CodeIgniter’s ActiveRecord join function? I want this:

LEFT JOIN cimke ON (mk_terem.id_kicsoda=5 AND mk_terem.id_target=cimke.id_cimke)
LEFT JOIN tanar ON (mk_terem.id_kicsoda=1 AND mk_terem.id_target=tanar.id_tanar)

But if I use $this->db->join(cimke,"mk_terem.id_kicsoda=5 AND mk_terem.id_target=cimke.id_cimke"), the value 5 will be between backticks.

How can I do this?

UPDATE

What I want? If mk_terem.id_kicsoda is 1, then I want tanar.nev and when mk_terem.id_kicsoda is 5, I want cimke.nev.

The full SQL-query:

SELECT
terem.nev terem_nev,
elem_tipus.nev tipus_nev,
(IFNULL(cimke.nev,tanar.nev)) nev
FROM mk_terem
LEFT JOIN terem ON mk_terem.id_terem=terem.id_terem
LEFT JOIN cimke ON (mk_terem.id_kicsoda=5 AND mk_terem.id_target=cimke.id_cimke)
LEFT JOIN tanar ON (mk_terem.id_kicsoda=1 AND mk_terem.id_target=tanar.id_tanar)
LEFT JOIN elem_tipus ON (mk_terem.id_kicsoda=elem_tipus.id_kicsoda)

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Only a simple workaround, ugly, not elegant, but it works in this case:

$original_reserved = $this->db->_reserved_identifiers;
$this->db->_reserved_identifiers[] = 5;
$this->db->_reserved_identifiers[] = 1;
// or any other values
$this->db->join('with critical values and conditions');
// some db-stuff
$this->db->_reserved_identifiers = $original_reserved;

If anybody knows better please show it!

Method 2

$this->db->join('cimke', 'mk_terem.id_target = cimke.id_cimke');
$this->db->join('tanar', 'mk_terem.id_target = tanar.id_tanar');
$this->db->where('mk_terem.id_kicsoda', 5);
$this->db->where('mk_terem.id_kicsoda', 1);

or use

$this->db->query('AND_YOUR_RAW_SQL_QUERY_HERE');

Method 3

Another simple solution would be to temporarily set the protect_identifiers off like so:

$this->db->_protect_identifiers = false;

After making the query you could set it back to true

Method 4

I know i am very answering this question very late. Just i am sharing my knowledge. It may help us to known something. If I am wrong, Please tell me. I am answering this question by the full query you posted on your question. Kindly check it below.

$this->db->from('mk_terem');
$this->db->select('terem.nev terem_nev, elem_tipus.nev tipus_nev, (IFNULL(cimke.nev,tanar.nev)) nev');
$this->db->join('terem','mk_terem.id_terem=terem.id_terem','left');
$this->db->join('cimke','(mk_terem.id_kicsoda=5 AND mk_terem.id_target=cimke.id_cimke)','left');
$this->db->join('tanar','(mk_terem.id_kicsoda=1 AND mk_terem.id_target=tanar.id_tanar)','left');
$this->db->join('elem_tipus','(mk_terem.id_kicsoda=elem_tipus.id_kicsoda)','left'); $this->db->get();

I got the query which you posted in the question as full Query. Check it below.

SELECT `terem`.`nev` terem_nev, `elem_tipus`.`nev` tipus_nev, (IFNULL(cimke.nev, `tanar`.`nev))` nev FROM (`mk_terem`) LEFT JOIN `terem` ON `mk_terem`.`id_terem`=`terem`.`id_terem` LEFT JOIN `cimke` ON `mk_terem`.`id_kicsoda`=`5` AND mk_terem.id_target=cimke.id_cimke) LEFT JOIN `tanar` ON `mk_terem`.`id_kicsoda`=`1` AND mk_terem.id_target=tanar.id_tanar) LEFT JOIN `elem_tipus` ON `mk_terem`.`id_kicsoda`=`elem_tipus`.`id_kicsoda)`

I believe everything is possible to do by codeignitor Active record. No need to execute the query in $this->db->query() as raw sql.

@uzsolt: I am explaining this because of the argument you made under the topic “http://stackoverflow.com/questions/8344769/writing-sql-queries-in-codeigniter-2-0”. And I will try your other bugs you posted under the above URL too shortly and get back to you. If i am anything wrong, kindly let me know. Thanks. 🙂

Method 5

If you swap the conditions around AND, it will work! CodeIgniter only escapes the first part of the condition.
So, inthis should work:

$this->db->join('cimke',"mk_terem.id_kicsoda = 5 AND mk_terem.id_target = cimke.id_cimke", "left")

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0