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 <graham...@orange.fr> 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 <tleftw...@gmail.com> 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 symfony-users@googlegroups.com
> > To unsubscribe from this group, send email to
> > symfony-users+unsubscr...@googlegroups.com
> > 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 symfony-users@googlegroups.com
> To unsubscribe from this group, send email to
> symfony-users+unsubscr...@googlegroups.com
> 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 symfony-users@googlegroups.com
To unsubscribe from this group, send email to
symfony-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/symfony-users?hl=en
