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 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 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 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
-~--~~~~--~~--~--~---
