$this->db->where_in()

[eluser]theprodigy[/eluser]
quick question (hopefully quick answer):

In the docs, it shows two parameters for the where_in function, the first being the field name, and the second being an array of strings.

I mostly use the where [field] IN syntax with a nested select query: "Where user_id IN (select user_id from ...)"

Is there any way to do this using the Active Record without having to perform two separate hits to the database (do inner select, get result_array(), then do outer select passing in result_array to the where_in function)?

I've been doing a work around with manually typing out my where statement into a $where variable, and passing it in to the where() function, but is there another way to do this?

Example of how I have been doing it:

Code:

function get_friends()
{
    $user_id = $this->session->userdata('user_id');
    
    $where = "user_profiles.user_id IN (SELECT if(friend_id='$user_id',user_id,friend_id) as user_id FROM user_friends WHERE ((user_id='$user_id' and friend_id <> '$user_id') or (friend_id='$user_id' and user_id <> '$user_id')))";
        
    $this->db->select('user_profiles.user_id, user_profiles.fname, user_profiles.lname');
    $this->db->from('user_profiles');
    $this->db->where($where);
    return $this->db->get();
}

I appreciate any and all help.

Thanks

[eluser]theprodigy[/eluser]
any thoughts at all on this would be appreciated.

Thanks

[eluser]slowgary[/eluser]
I, too, would like to find out more about using the where_in with an inner select statement. I've been resorting to $this->db->query() and skipping the active record altogether when I need an inner select.

Anyone? Bueller? Bueller? Bueller?

[eluser]TheFuzzy0ne[/eluser]
I think that using query is the only way to do it at the current time. It is possible to compile the SQL statement, retrieve it, clear it, and then use it within another statement, but I can't remember how of the top of my head.