Can I run a subquery using $this->db->where_not_in?

[eluser]DanRomanchik[/eluser]
I'm trying compose an Active Record query similar to this:

SELECT `events`.* FROM `events` WHERE `events`.`id` NOT IN (select event_id2 from related_events where event_id1 = 39) ORDER BY `entry_date`

To generate that where clause I'm using the statement:

$this->db->where_not_in("events.id","SELECT event_id2 FROM related_events WHERE event_id1 = ".$id);

The problem seems to be that this function wants to put single quotes around that subquery, so of course it doesn't run. Is there anyway that I can get that function to properly compose the query?

Thanks, Dan

[eluser]pickupman[/eluser]
It's a bit of a workaround, but here is a blog post from a member explaining how to do subqueries.

CodeIgniter Subqueries

[eluser]DanRomanchik[/eluser]
Thanks for the tip, but, unfortunately, that still didn't work. With the lines:

$this->db->select('event_id2');
$this->db->from('related_events');
$this->db->where("related_events.event_id1",$id);
$subQuery = $this->db->_compile_select();

I'm composing the subquery,

SELECT `event_id2` FROM (`related_events`) WHERE `related_events`.`event_id1` = '39'

but when I use that string in this function:

$this->db->where_not_in("events.id",$subQuery);

I'm getting the clause,

WHERE `events`.`id` NOT IN ('SELECT `event_id2`\n
FROM (`related_events`)\nWHERE `related_events`.`event_id1` = \'39\'')

Those extra single quotes are still killing me.

Dan

[eluser]pickupman[/eluser]
You could resort to using the boring $this->db->query("your query string"), or extending the ar class, and adding a parameter to not escape your string.

[eluser]DanRomanchik[/eluser]
Thanks again for your help.

What I ended up doing is this:

$this->db->where("events.id NOT IN (SELECT event_id2 FROM related_events WHERE event_id1 =".$id.")");

It's not ideal, but it worked.

Dan