Harry Truong Wed Dec 01 16:43:18 -0500 2010

Subject: Using finder FROM option to submit a subquery

Hello all,

I'm trying to find the "last 3 most recent, in ascending order" through a relationship.
The simplest explain to explain this:

-- POST
---- Comment 1/2/10 #not selected
---- Comment 1/3/10 #not selected
---- Comment 1/4/10 #not selected
---- Comment 1/5/10 #selected
---- Comment 1/6/10 #in this
---- Comment 1/7/10 #order

I would like to declare the association in my Post model as follows:

class Post extends ActiveRecord\Model {
    static $has_many = array(
        array('comments',
            ## Association options go here.
        )
    );
}

The ideal query would then be:

$post = Post::first();
var_dump($post->comments); 
#returns an array of 3 most recent comments, in ascending order.

My current solution is a custom static function for my Comment model, which doesn't use an association:

class Comment extends ActiveRecord\Model {

    static function lastthree ($offset = null, $post_id = null) {
        if ((int)$offset < 0) { $offset = 0; } //fixes a negative offset
        ## $offset is determined by another relationship
        ## in the Post model, as explained after this
        return self::all(array(
            'order' => 'created_at asc',
            'limit' => 3,
            'offset' => (int)$offset,
            'conditions' => array('post_id = ?',(int)$post_id)
        ));
    }
}

I added a $has_one association to the Post model:

class Post extends ActiveRecord\Model {
    static $has_one = array(
        array('coms', 
            'class_name' => 'post',
            'select' => 'COUNT(id) as num')
    ); 
    # this allows for ($post->coms->num - ###)
    # to determine the correct offset number
}

My solution's query would be:

$post = Post::first();
var_dump(Comment::lastthree($post->coms->num - 3, $post->id));
# returns the array of 3 most recent comments, in ascending order.

This is a very ugly solution... I think the ideal way would be to declare the association appropriately, all within in the Post model, but it would require a "FROM" option to allow for a subquery, as follows:

class Post extends ActiveRecord\Model {
    static $has_many = array(
        array('comments',
            'from' => 'SELECT * FROM comments ORDER BY created_at DESC LIMIT 3',
            'order' => 'created_at ASC'
        )
    );
}

Any thoughts? Does anyone have a better solution to doing this?
And would I be on the wrong track to submit a feature request for this?


Harry Truong Wed Dec 01 16:51:46 -0500 2010

P.S. My example problem might seem overkill but it's because im aiming for the comment threading that would you see on facebook, when a single item might have many comments and only the last few are shown:

-- POST
---- Comment 1/2/10 #not selected/hidden
---- Comment 1/3/10 #not selected/hidden
---- Comment 1/4/10 #not selected/hidden
---- Comment 1/5/10 #shown
---- Comment 1/6/10 #in this
---- Comment 1/7/10 #order
kirk bushell Tue Dec 07 17:27:37 -0500 2010

What you could do in this instance, is set up an association to comments, and set an order and limit clause for that association. See associations here: http://www.phpactiverecord.org/projects/main/wiki/Associations

What you would then do, is something like the following:

In post model:

static $has_many = array(array('recent_comments', 'order' => 'created_at DESC', 'limit' => 3));

Then in your code:

$posts = Post::all(array('include' => 'recent_comments'));

In your view (or wherever you're displaying it):

foreach ($posts as $p) { foreach ($p->recent_comments as $comment) ....

.etc.

PS - if you want it like facebook and want it in ascending order - simply write a public function on the posts model that gets the comments for that post, and sorts it manually. You have to get the 3 most recent, in descending order in mysql, then order it specifically using PHP.

For that, you'd do something like (on posts model):

public function new_comments() {
$comments = $this->recent_comments;
// loop through comments, sort as necessary
return $comments;
}

Then in your view, instead of accessing recent_comments, you'd access new_comments()

Harry Truong Tue Dec 07 19:04:01 -0500 2010

Thanks for the reply! You're right, I don't see why I didn't think of that setup.
(Thats what I get for late night coding.)

I'm afraid the 'includes' attribute won't work in this case (theres a bug in the 1.0 release thats causing eager loading to ignore all association attributes -- unless I'm wrong?) but your idea would work if I just did the simple $post->comments method.

I'm looking forward to test this out when I get home. The only change is that I'd use the php function "reverse_array($array)" that I just realized exists (sigh.)

Thanks a lot!

kirk bushell Tue Dec 07 19:08:31 -0500 2010

If that won't work then just do a normal join on the query itself. Even so, when it's fixed - definitely use that functionality as it will keep your code nice and clean. You'll still need the association for writing your custom method, it just means that you'll keep the work inside your model rather than somewhere else.

And no problem :)

Trying to help this library become much more popular - by far one of the best ORMs around!

(1-4/4)