paul0001
(Kits2c22e)
February 3, 2012, 9:11am
1
Hi all,
Below are the schema for Post and Comment
CREATE TABLE Post (
postID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
msg VARCHAR(100),
) ENGINE = InnoDB;
CREATE TABLE Comment (
commentID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
postID INT NOT NULL,
date DATE NOT NULL,
msg VARCHAR(100),
CONSTRAINT fk_comment_post
FOREIGN KEY (postID)
REFERENCES Post (postID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) ENGINE = InnoDB;
and the relation in Post Model is specified as:
public function relations() {
return array(
'comments' => array(self::HAS_MANY, 'Comment', 'postID'),
);
}
How to add a relation ‘lastestComment’ to the Post model to just get the latest Comment?
Thanks.
weavora
(Yury Tolochko)
February 3, 2012, 9:28am
2
I have no solution how to do that with relations but you could do it with regular method:
class Post extends CActiveRecord {
private $_latestComment = null;
...
public function getLatestComment($refresh = false) {
if (is_null($this->_latestComment) || $refresh) {
$c = new CDbCriteria();
$c->order = '`date` desc';
$c->compare('postID', $this->postID);
$this->_latestComment = Comment::model()->find($c);
}
return $this->_latestComment;
}
}
// usage
echo $post->latestComment->msg; // sql query executed
echo $post->latestComment->date; // no more query
echo $post->getLatestComment(true)->msg; // refresh latest comment -> sql query executed
Based on http://www.yiiframework.com/doc/api/1.1/CActiveRecord#relations-detail
public function relations() {
return array(
'comments' => array(self::HAS_MANY, 'Comment', 'postID'),
'lastComment' => array(self::HAS_MANY, 'Comment', 'postID', 'order'=>'lastComment.date DESC','limit'=>1)
);
}
Totally untested (I normally use DAO for this type of operations)
PS: You can also use scopes
You set your scope on Comments
public function last(){
$this->getCDbCriteria()->order = 'date DESC';
$this->getCDbCriteria()->limit = 1;
return $this;
}
Then you can call it like this, having your comments relation set
// returns all posts with its last comment
$posts = Post::model()->with('comments:last')->findAll();
Reference: http://www.yiiframework.com/doc/guide/1.1/en/database.arr#relational-query-with-named-scopes
weavora
(Yury Tolochko)
February 3, 2012, 9:31am
4
public function relations() {
return array(
'comments' => array(self::HAS_MANY, 'Comment', 'postID'),
'lastComment' => array(self::HAS_MANY, 'Comment', 'postID', 'order'=>'lastComment.date DESC','limit'=>1)
);
}
In this case you should access lastComment as:
echo $post->lastComment[0]->msg;
andy_s
(Arekandrei)
February 3, 2012, 9:57am
5
I’m not sure this will work using “with”:
Post::model()->with('lastComment')->findAll();
Also I wouldn’t create a relation just to get a last comment.
I have no solution how to do that with relations but you could do it with regular method:
class Post extends CActiveRecord {
private $_latestComment = null;
...
public function getLatestComment($refresh = false) {
if (is_null($this->_latestComment) || $refresh) {
$c = new CDbCriteria();
$c->order = '`date` desc';
$c->compare('postID', $this->postID);
$this->_latestComment = Comment::model()->find($c);
}
return $this->_latestComment;
}
}
// usage
echo $post->latestComment->msg; // sql query executed
echo $post->latestComment->date; // no more query
echo $post->getLatestComment(true)->msg; // refresh latest comment -> sql query executed
Thank you for this solution!! I modified this method to reflect few things that were suiting me. And could manage what I was trying.
fburhan89
(Sefburhan)
June 16, 2013, 11:34am
7
paul0001:
Hi all,
Below are the schema for Post and Comment
CREATE TABLE Post (
postID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
msg VARCHAR(100),
) ENGINE = InnoDB;
CREATE TABLE Comment (
commentID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
postID INT NOT NULL,
date DATE NOT NULL,
msg VARCHAR(100),
CONSTRAINT fk_comment_post
FOREIGN KEY (postID)
REFERENCES Post (postID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) ENGINE = InnoDB;
and the relation in Post Model is specified as:
public function relations() {
return array(
'comments' => array(self::HAS_MANY, 'Comment', 'postID'),
);
}
How to add a relation ‘lastestComment’ to the Post model to just get the latest Comment?
Thanks.
http://www.yiiframework.com/doc/guide/1.1/en/database.arr#relational-query-options
I think this were a better solution:
public function relations() {
return array(
'comments' => array(self::HAS_MANY, 'Comment', 'postID'),
'lastComment' => array(self::HAS_ONE, 'Comment', 'postID', 'order'=>'commentID DESC')
);
}
(Not tested, btw)