Hi,
I’m trying to make a rather complicated SQL to fetch some topics in a forum.
What I want to do:
Topic::model()->lastUpdated(5)->with(array('thread', 'thread.threadChildsCount'))->findAll();
The goal is something like this:
SELECT `t`.*, `threads`.* COUNT(`threadChilds`.`id`) FROM `topics` as `t`
LEFT JOIN (`threads`) ON (`threads`.`id` = `t`.`threadId`)
LEFT JOIN (`threadChilds`) ON (`threadChilds`.`threadId` = `threads`.`id`)
GROUP BY `t`.`id`
ORDER BY MAX(`threadChilds`.`created`) DESC
Topic.php
public function relations() {
return array(
'user'=>array(self::BELONGS_TO , 'User', 'userId'),
'category'=>array(self::BELONGS_TO , 'TopicCategory', 'categoryId'),
'thread'=>array(self::BELONGS_TO , 'Thread', 'threadId'),
);
}
public function lastUpdated( $limit=12 ) {
$criteria = new CDbCriteria();
$criteria->order = 'MAX(`threadChilds`.`created`) DESC';
$criteria->limit = $limit;
$criteria->group = 't.id';
$criteria->with = array('thread','thread.threadChildsCount');
$this->getDbCriteria()->mergeWith($criteria);
return $this;
}
Thread.php
public function relations() {
return array(
'threadChilds'=>array(self::HAS_MANY, 'ThreadChild', 'threadId',
'order' => '`threadChilds`.`created` ASC'
),
'latestThreadChild'=>array(self::HAS_ONE, 'ThreadChild', 'threadId',
'order' => '`created` DESC'
),
'threadChildsCount'=>array(self::STAT, 'ThreadChild', 'threadId'),
);
}
All relations seem to work just fine, but when combining them, threadChildsCount isn’t included as a join, therefore giving the error:
Unknown column 'threadChilds.created' in 'order clause'
Thanks in advance!