Doctrine: How to do UPDATE with a SELECT MIN subquery?

Question

I have a photo table where users can have multiple photos.

I am trying to create the following query:

$q = Doctrine_Query::create() ->update('Photo p') ->set('p.photo_type', 1) ->where('p.user_id = ?', $id) ->andWhere('p.date_added = (SELECT MIN(p.date_added) FROM Photo p WHERE p.user_id = ?)', $id) 

The idea is to set the photo_type parameter to 1 for this particular photo of this user with the minimum date added (earliest photo).

I just can't get the syntax right. Can someone point me in the right direction?

Thanks.

EDIT:

It seems that I'm trying to do something that cannot be done, in accordance with the answer to this question ( MySQL Error 1093 - Unable to specify the target table for updating in FROM) . This question can be closed.

Answer 1

Try the following:

 $q = Doctrine_Query::create() ->update('Photo p') ->set('p.photo_type', 1) ->where('p.user_id = ?', $id) ->orderBy('p.date_added', 'desc') ->limit(1);