I would start by simplifying the query: SELECT r.*, MAX(c.created_at) AS last_comment FROM request r LEFT JOIN comment c on r.id=c.request_id GROUP BY c.request_id;
This can be expressed using DQL as: $q=Doctrine_Query::create()->select( 'r.*, MAX(c.created_at) AS last_comment' )->from( 'request r, r.Comments c' )-> groupBy( 'c.request_id' ); echo $q->getSqlQuery(); // to see the SQL generated $collection=$q->execute(array(),Doctrine_Core::HYDRATE_RECORD); // to retrieve all the records See the chapter on DQL in the Doctrine manual. Unless I'm going crazy, it seems that the manual has been temporarily withdrawn from the site. I could mail you a copy. Regards, Graham On May 13, 10:22 am, El Duderino <[email protected]> wrote: > To simplify a project I am working on it has two tables, one for > posting requests, the other for making comments on that request (one- > to-many relationship). The objective is to return one line per request > (whether there has been a comment or not) and show the date for the > latest comment made on that request. Although I have found a way to do > this with a regular SQL query, I'm stumped on how to get this working > with Doctrine on Symfony, specifically how to work with the subquery. > > SQL query: > SELECT * > FROM request > LEFT JOIN ( > SELECT s1.* > FROM comment as s1 > LEFT JOIN comment AS s2 > ON s1.request_id = s2.request_id > AND s1.created_at < s2.created_at > WHERE s2.request_id IS NULL > ) AS comment_tmp > ON (request.id = comment_tmp.request_id) > > Schema: > Request: > actAs: > Timestampable: ~ > columns: > title: { type: string(255), notnull: true } > > Comment: > actAs: > Timestampable: ~ > columns: > request_id: { type: integer(10), notnull: true } > comments: { type: string(10000), notnull: true } > relations: > Request: { onDelete: CASCADE, local: request_id, foreign: id, > foreignAlias: Comments, foreignType: one } > > Any insights appreciated. > > Thanks! > > -- > If you want to report a vulnerability issue on symfony, please send it to > security at symfony-project.com > > You received this message because you are subscribed to the Google > Groups "symfony users" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group > athttp://groups.google.com/group/symfony-users?hl=en -- If you want to report a vulnerability issue on symfony, please send it to security at symfony-project.com You received this message because you are subscribed to the Google Groups "symfony users" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/symfony-users?hl=en
