Latest Comment of a Post

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.

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

In this case you should access lastComment as:




echo $post->lastComment[0]->msg;



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.

Thank you for this solution!! I modified this method to reflect few things that were suiting me. And could manage what I was trying.

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)