ZF Sub Query Question

I have a user table with user_id as the primary key and a user_dates table that has user_date_id as the primary key, user_id, which refers to the fields user and start_date and end_date.

I'm trying to run a query to get a list of users and order them by the earliest date, but only using dates that have not passed yet. So I'm trying to run a sub-query to get this date, and then use it to order users.

So far I have managed to link the tables, but I'm not sure how to add an extra query to ZF?

$oSelect = $this->getDbConnection()
  ->select()
  ->from(array('u' => 'users'), '*')
    ->joinInner(array('d' => 'user_dates')
    , 'u.user_id = d.user_id', array('start_date', 'end_date'))

The subquery will look something like this:

SELECT start_date FROM user_dates d
WHERE d.user_id=u.user_id && start_date > NOW()
ORDER BY start_date ASC LIMIT 0,1

thank

So, I could do the following:

$oSelect = $this->getDbConnection()
->select()
->from(array('u' => 'users'), '*')
->joinInner(array('d' => 'user_dates')
, 'u.user_id = d.user_id', array('start_date' =>
                          new Zend_Db_Expr('SELECT start_date FROM user_dates d WHERE d.user_id=u.user_id && start_date > NOW() ORDER BY start_date ASC LIMIT 0,1'), 'end_date;'))

I can’t cancel $ oSelect because I don’t have ZF on this machine, but I hope the following request:

SELECT u . * , (

SELECT start_date
FROM dates d
WHERE d.user_id = u.user_id && end_date > NOW( )
ORDER BY start_date ASC
LIMIT 0 , 1
) AS date
FROM `users` u
INNER JOIN dates d ON u.user_id = d.user_id
WHERE end_date > NOW()
GROUP BY u.user_id
ORDER BY date ASC
LIMIT 0 , 30

You are right, ignore the above, the request should be:

SELECT u. * , (

SELECT start_date
FROM dates d
WHERE d.user_id = u.user_id && end_date > NOW( )
ORDER BY start_date ASC
LIMIT 0 , 1
) AS date
FROM `users` u
GROUP BY u.user_id
HAVING date
ORDER BY date ASC
LIMIT 0 , 30