Using a subquery with CodeIgniter WHERE IN clause

Question

How do I write this MySQL query for Codeigniter?

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

Subqueries in a WHERE clause can be expensive (because they make a separate query for each potentially qualifying row). Can this query be refactored due to some known aspect of the database table? What range of values might code have? Would a GROUP BY and HAVING possible work for your intention so that you don't need any subqueries? — mickmackusa
– mickmackusa ♦, Commented Mar 31 at 4:17
I asked the question back in 2012, but I don't even remember which project I was working on. Thank you for asking, though. — black_belt
– black_belt, Commented Apr 19 at 20:48
A more modern script can be found at SELECT WHERE IN subquery using CodeIgniter's where_in() method — mickmackusa
– mickmackusa ♦, Commented Jun 9 at 7:57

Community · Accepted Answer · 2017-05-23 12:18:14Z

25

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

How can I rewrite this SQL into CodeIgniter's Active Records?

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

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Jun 14, 2012 at 10:44

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mickmackusa Jun 9 at 7:59

What version of CodeIgniter was this designed for? What is the rendered WHERE clause when you only pass the first parameter to where_in()? Only passing one parameter to where_in() will result in a NULL second parameter. When either the first or second parameter of a where_in() (which calls _where_in() internally) is NULL, then the method does an early return without adding the WHERE IN expression at all. My suspicion is that developers who have used this advice have implemented a silently inaccurate query.

areeb · Accepted Answer · 2013-04-18 14:20:11Z

17

$data = $this->db->get_where('columnname',array('code' => 'B'));
$this->db->where_in('columnname',$data);
$this->db->where('code !=','B');
$query =  $this->db->get();
return $query->result_array();

answered Apr 18, 2013 at 14:20

areeb

4223 silver badges9 bronze badges

1 Comment

mickmackusa Mar 27 at 8:53

I do not endorse making two trips to the database. Did this answer ever work? It appears to be using a CI result object as the second parameter of where_in() (which typically expects a flat array).

Code Prank · Accepted Answer · 2012-06-14 08:36:30Z

7

Try this one:

$this->db->select("*");
$this->db->where_in("(SELECT trans_id FROM myTable WHERE code = 'B')");
$this->db->where('code !=', 'B');
$this->db->get('myTable');

Note: $this->db->select("*"); is optional when you are selecting all columns from table

answered Jun 14, 2012 at 8:36

Code Prank

4,2706 gold badges35 silver badges48 bronze badges

1 Comment

mickmackusa Oct 14 at 20:15

Only passing one parameter to where_in() will result in a NULL second parameter. When either the first or second parameter of a where_in() (which calls _where_in() internally) is NULL, then the method does an early return without adding the WHERE IN expression at all.

Colin Brock · Accepted Answer · 2012-06-13 21:23:22Z

1

try this:

return $this->db->query("
     SELECT * FROM myTable 
     WHERE trans_id IN ( SELECT trans_id FROM myTable WHERE code='B') 
     AND code!='B'
     ")->result_array();

Is not active record but is codeigniter's way http://codeigniter.com/user_guide/database/examples.html see Standard Query With Multiple Results (Array Version) section

edited Jun 13, 2012 at 21:23

Colin Brock

21.6k9 gold badges51 silver badges62 bronze badges

answered Jun 13, 2012 at 21:20

mvpasarel

7855 silver badges13 bronze badges

Comments

Dharman · Accepted Answer · 2021-10-14 06:49:49Z

0

you can use a simpler approach, while still using active record

$this->db->select("a.trans_id ,a.number, a.name, a.phone")
$this->db->from("Name_Of_Your_Table a");
$subQueryIn = "SELECT trans_id FROM Another_Table";
$this->db->where("a.trans_id in ($subQueryIn)",NULL);

edited Oct 14, 2021 at 6:49

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Oct 14, 2021 at 6:44

Hasan

324 bronze badges

Comments

mickmackusa · Accepted Answer · 2025-10-14 20:37:20Z

For most RDBMSs, using a JOIN will outperform performing a subquery for each potentially qualifying row.

SELECT a.*
FROM myTable a
JOIN myTable b USING (trans_id)
WHERE a.code != 'B' AND b.code = 'B'

CodeIgniter:

return $this->db
    ->select('a.*')
    ->join('myTable b', 'trans_id')
    ->get_where(
        'myTable b',
        [
            'a.code !=' => 'B',
            'b.code' => 'B'
        ]
    )
    ->result();

If your script cannot be refactored to use a JOIN (maybe you are using [WHERE IN subquery] as part of an UPDATE query and your database dialect complicates that), you can use where_in() with a pre-compile subquery if you disable the auto-quoting in where_in().

$sub = $this->db
    ->select('trans_id')
    ->where('code', 'B')
    ->get_compiled_select('myTable');

return $this->db
    ->where_in('trans_id', $sub, false)
    ->get_where('myTable', ['code !=' => 'B'])
    ->result();

Rendered SQL:

SELECT *
FROM `myTable`
WHERE trans_id IN(SELECT `trans_id` FROM `myTable` WHERE `code` = 'B')
AND `code` != 'B'

Technically, you can avoid the temporary subquery variable declaration if it is compiled before any part of the parent query is built (same result).

return $this->db
    ->where_in(
        'trans_id',
        $this->db
            ->select('trans_id')
            ->where('code', 'B')
            ->get_compiled_select('myTable'),
        false
    )
    ->get_where('myTable', ['code !=' => 'B'])
    ->result();

Collectives™ on Stack Overflow

Using a subquery with CodeIgniter WHERE IN clause

6 Answers 6

1 Comment

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related