i have the folowing query in SQL
... where group_id IN (select group_id from alert where monitor_id = 4);
I want to write it in Doctrine but i don't know how to add the IN select into WHEREIN() clause ! any idea ?
this is what i did
$q = $this->createQuery('u')
->select('u.email_address')
->distinct(true)
// ->from('sf_guard_user u')
->innerJoin('u.sfGuardUserGroup ug')
->where('ug.group_id IN(select group_id from alert where monitor_id=?',$monitor);
$q->execute();
In the sfGuardUserTable.class:
public function getMailsByMonitor($monitor) {
$q = Doctrine_Query::create()->from("alert a")->where("a.monitor_id", $monitor);
$groups_raw = $q->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
$groups = array();
print_r($groups_raw);
foreach ($groups_raw as $gr) {
$groups[] = $gr->id; //line 33
}
$q2 = $this->createQuery('u')
->select('u.email_address')
->distinct(true)
->innerJoin('u.sfGuardUserGroup ug')
->whereIn("ug.group_id", $groups);
return $q2->execute();
}
Usually you would do something like:
$q = Doctrine_Query::create()
->from('User u')
->whereIn('u.id', array(1, 2, 3));
But I think this one better fits your needs:
$q = Doctrine_Query::create()
->from('Foo f')
->where('f.group_id IN (SELECT f.group_id FROM Alert a WHERE a.monitor_id = ?)', 4);