That's working perfectly now using the HYDRATE_RECORD and looping - I only had to change the query to group by r.id so it shows all requests with or without a comment made.
Very much appreciated. On May 13, 5:02 pm, grahamj42 <[email protected]> wrote: > $collection is a Doctrine_Collection object. This has a method get() > which can take a row number and returns a Doctrine_Record object. > > The Doctrine-Record object can be accessed by field name, so > $collection->get(0)->title should return the title of the first > request. > > Alternatively, you can use HYDRATE_ARRAY instead of HYDRATE_RECORD to > return the results as an array. One advantage of Doctrine_Collection > is that it can retrieve rows from the database on demand, which is > good with a pager on a large result set. > > Hope this will get you further. > > On May 13, 3:37 pm, El Duderino <[email protected]> wrote: > > > > > > > The simplified DQL looks good and returns the correct rows including > > the last created_at so definite progress. > > > You mentioned using > > $collection=$q->execute(array(),Doctrine_Core::HYDRATE_RECORD); // to > > retrieve all > > > the records > > > How exactly would I be able to use this to pull the comment's last > > date and display it on a typical indexSuccess.php page? > > > For example, I can get the request's created_at this way: <?php echo > > $request->getComments()->getCreatedAt() ?>, but how do you access the > > last_comment seeing as it isn't part of the model? > > > Thanks so much for this. > > > On May 13, 11:25 am, grahamj42 <[email protected]> wrote: > > > > 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 > > > 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 > > 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 > 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
