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 at
http://groups.google.com/group/symfony-users?hl=en
