[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.

[Weavora Team]

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

[Antonio Ramirez]

Based on http://www.yiiframew...elations-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.yiiframew...th-named-scopes

[Weavora Team]

Antonio Ramirez, on 03 February 2012 - 04:28 AM, said:

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]

Weavora Team, on 03 February 2012 - 04:31 AM, said:

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.

[Sankalp Singha]

Weavora Team, on 03 February 2012 - 04:28 AM, said:

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]

paul0001, on 03 February 2012 - 04:11 AM, said:

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.yiiframew...l-query-options

[Da:Sourcerer]

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)