Hi SeeVik,
I've done only small change (MAX(p2.posted) instead of MAX(posted))
and your DQL works fine for me:
$q = Doctrine_Query::create()
- from( 'Topic t' )
- innerJoin( 't.Posts p' )
- where( 'p.posted = (SELECT MAX(p2.posted) from post p2 where
p2.topic_id = p.topic_id)' );
var_dump($q-getSql());
string(231) SELECT t.id AS t__id, p.id AS p__id, p.topic_id AS
p__topic_id, p.posted AS p__posted FROM topic t INNER JOIN post p ON
t.id = p.topic_id WHERE p.posted = (SELECT MAX(p2.posted) AS
p2__0 FROM post p2 WHERE p2.topic_id = p.topic_id)
I have symfony 1.2.5-DEV from SVN, rev 15495.
cheers,
Tomek
On Feb 14, 11:30 pm, SeeVik vikramvmalhotra1...@gmail.com wrote:
Hello Tomek. Yes I will try that query. In the mean time I have
figured out something similar.
The SQL query...
SELECT *
FROM topic t INNER JOIN post p
ON ( t.id = p.topic_id )
WHERE p.posted = ( SELECT MAX(posted) FROM post
WHERE topic_id = p.topic_id );
I have a problem figuring out the DQL for it though. I tried this
one...
$q = Doctrine_Query::create()
- from( 'Topic t' )
- innerJoin( 't.Posts p' )
- where( 'p.posted = (SELECT MAX(posted) from post p2 where
p2.topic_id = p.topic_id)' );
But what this does is. it genrates a sql query looking like
this
SELECT *
FROM topic t INNER JOIN post p
ON ( t.id = p.topic_id )
WHERE p.posted = ( SELECT MAX(posted FROM post
WHERE topic_id = p.topic_id ));
Notice that paranthesis after MAX has been placed at the end. That
gives an SQL syntax error.
Is this a problem in Doctrine?
On Feb 15, 12:34 am, Tomek nexor1...@gmail.com wrote:
Hi Vikram,
One way to do it would be with the subquery.
I'm assuming your schema is:
Topic:
columns:
Post:
columns:
topic_id: integer
posted: timestamp
relations:
Topic:
foreignAlias: Posts
In SQL the query you are looking for looks like that:
SELECT * FROM topic t INNER JOIN post p ON (t.id=p.topic_id)
WHERE p.id=(SELECT id FROM post WHERE topic_id=p.topic_id ORDER BY
posted DESC LIMIT 1 );
In DQL:
$q = Doctrine_Query::create()-from('Topic t')-innerJoin('t.Posts
p')
-where('p.id = (SELECT p2.id from post p2 where
p2.topic_id=p.topic_id order by p2.posted desc limit 1)');
I hope that helps,
Tomek
On Feb 13, 11:06 am, SeeVik vikramvmalhotra1...@gmail.com wrote:
Hello all
I am using Symfony with Doctrine plugin.
I am making a simple bulletin board in my application. I want to find
out the last post in every topic and display the time in front of
topic.
In Symfony, in the controller for the screen, I do this...
$list_topics = Doctrine::getTable( Topics )-getLastPost();
In the model layer, in PostsTable.class.php, I have this
public function getLastPost()
{
$q = $this-createQuery( 'a' )
- innerJoin( 'a.Topics t' )
- groupBy( 'a.topic_id' )
- having( 'a.posts_id = ?', ' Query to find out the posts having
maximum time for a particular topic ' );
return $q-execute();
}
Can somebody help me out with this?
Thanks and Regards
Vikram
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
