How can I retrieve the latest question of each thread in Propel 1.6?

Question

I want to get the newest entries for each of my threads (private messaging system) with Propel 1.6 making use of the fluid ModelQuery interface. This would allow me to reuse both methods for getting newest entries and only getting entries where a user is involved (nobody wants to see messages not for him).

I already found out that in standard-SQL I have to use a subquery to get the newest entry for each of my forum threads. I also found out that in Propel you have to use a Criteria::CUSTOM query to achieve this, but the whole Criteria::CUSTOM stuff seems to be pre-Propel-1.6, because none of the examples makes use of the new ModelQuery.

Now the problem is, that I want to make use of the concenation feature in ModelQueries, where you can attach several own methods to each other like this:

$entries = MessageQuery::create()
     ->messagesInvolvingUser($user) // user retrieved or sent the message
     ->newestFromThread() // get the latest entry from a lot of Re:-stuff

I do not think that this would still be possible if I had to use

$c = new Criteria();
$c->add([the subquery filter]);

in newestFromThread().

What’s the best method to retrieve the latest entry for each thread given the following scheme (thread_id means that all messages belong to the same correspondence, I want only one entry per thread_id):

id(INT)
title(VARCHAR)
thread_id(INTEGER)
date(DATETIME)

The current PHP-implementation looks like this:

<?php

class MessageQuery extends BaseMessageQuery {
    public function messagesInvolvingUser($user) {
        return $this
            ->where('Message.AuthorId = ?', $user->getId())
            ->_or()
            ->where('Message.RecipientId = ?', $user->getId());
    }

    public function newestFromThread() {
        return $this;
        // To be implemented
    }
}

And I am using it like this:

$messages = MessageQuery::create()
    ->messagesInvolvingUser(Zend_Auth::getInstance()->getIdentity())
    ->newestFromThread()
    ->find();

score 1 · Answer 1 · answered Nov 02 '11 at 12:13

1

How about ordering results by date (DESC) and to limit to one result ?

answered Nov 02 '11 at 12:13

William Durand

5,439
1
26
37

score 0 · Accepted Answer · edited May 23 '17 at 12:20

Considering the answers in a similar question about pure SQL solutions, I guess it is easiest to add a new column newest indicating which message in a communcation is the newest. This probably fits the object-oriented approach of Propel better, too. I could write my application like this then:

public function preInsert(PropelPDO $con = null) {
    $this->setNewest(1);
    $this->getEarlier()->setNewest(0);

    return true;
}

How can I retrieve the latest question of each thread in Propel 1.6?

2 Answers2