Peter Hollenberg Mon Jan 17 08:34:30 -0500 2011

Subject: Order by association

Hello,

I'm creating a forum with threads and posts, now I want to order through a association or 'joined' table. Both tables have a column "timestamp".

The following example would sort through the timestamp field of thread.
-----
//show threads from category
$template['thread'] = Thread::find_by_id($route3, array('order' => 'timestamp desc'));
$template['posts'] = $template['thread']->posts;
-----

This doesn't work:
-----
//show threads from category
$template['thread'] = Thread::find_by_id($route3, array('order' => 'posts.timestamp desc'));
$template['posts'] = $template['thread']->posts;
-----

To be clear, I want to order through the timestamp of "posts" table.
Don't know how to solve it, can anybody point me in the right direction?


Kien La Mon Jan 17 21:19:00 -0500 2011

You need to add a join for the posts table to your query otherwise posts.timestamp is not available.

Something like:

Thread::find_by_id($route3,array(
'select' => 'threads.*',
'joins' => 'JOIN posts ON(posts.thread_id=threads.id)',
'order' => 'posts.timestamp desc'));

Peter Hollenberg Tue Jan 18 06:56:52 -0500 2011

Would be better if there was a solution like:

$template["thread"]->posts.order('timestamp desc');

Or my previous tryouts, because I already have the "join" in my models.
Wich means the posts.timestamp field would be already there, because
$template["thread"]->timestamp and $template["thread"]->post->timestamp are both there.

Thread model:
----------------
class Thread extends ActiveRecord\Model {
static $belongs_to = array(array('member'), array('forumcategory'));
static $has_many = array(
array('posts')
);

static $alias_attribute = array(
'original' => 'content');
}
?>

Post model:
----------------
class Post extends ActiveRecord\Model {
static $belongs_to = array(array('member'), array('thread', 'order' => 'timestamp asc'));

static $alias_attribute = array(
'original' => 'content');
}
?>

(1-2/2)