ci mysql in,在codeigniter中使用Mysql WHERE IN子句

I have the following mysql query. Could you please tell me how to write the same query in Codeigniter's way ?

SELECT * FROM myTable

WHERE trans_id IN ( SELECT trans_id FROM myTable WHERE code='B')

AND code!='B'

解决方案

You can use sub query way of codeigniter to do this for this purpose you will have to hack codeigniter. like this

Go to system/database/DB_active_rec.php

Remove public or protected keyword from these functions

public function _compile_select($select_override = FALSE)

public function _reset_select()

Now subquery writing in available

And now here is your query with active record

$this->db->select('trans_id');

$this->db->from('myTable');

$this->db->where('code','B');

$subQuery = $this->db->_compile_select();

$this->db->_reset_select();

// And now your main query

$this->db->select("*");

$this->db->where_in("$subQuery");

$this->db->where('code !=', 'B');

$this->db->get('myTable');

And the thing is done. Cheers!!!

Note : While using sub queries you must use

$this->db->from('myTable')

instead of

$this->db->get('myTable')

which runs the query.

Watch this too

Note : In Codeigntier 3 these functions are already public so you do not need to hack them.