This forum is in READ-ONLY mode.
You can look around, but if you want to ask a new question, please use the new forum.
Home » legacy support » symfony 1.2 » How to convert a SQL query to a Doctrine left join with subquery
How to convert a SQL query to a Doctrine left join with subquery [message #98966] Thu, 13 May 2010 01:08
tleftwich  is currently offline tleftwich
Messages: 10
Registered: March 2010
Location: Madrid
Junior Member
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!
Previous Topic:Dropdown onchange method
Next Topic:Routing problem
Goto Forum:
  

powered by FUDforum - copyright ©2001-2004 FUD Forum Bulletin Board Software