Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT post.id, post.title, post.more_columns , COUNT(comments.post_id) AS numofcomments FROM post INNER JOIN comments ON post.id = comments.post_id GROUP BY post.id, post.title, post.more_columns
- $con = Propel::getConnection(PostPeer::DATABASE_NAME);
- $sql = "SELECT post.* , COUNT(comments.post_id) AS numcomments FROM post INNER JOIN comments ON post.id = comments.post_id GROUP BY post.id";
- $stmt = $con->prepare($sql);
- $stmt->execute();
- $result = PostPeer::populateObjects($stmt);
- return $result;
- class Post extends BasePost {
- protected $numComments;
- public function setNumComments($v)
- {
- $this->numComments = $v;
- }
- public function getNumComments()
- {
- return $this->numComments;
- }
- ...
- }
- public static function doSelectWithCount() {
- $c = new Criteria();
- self::addSelectColumns($c); //add all columns from PostPeer
- $c->addAsColumn('numofcomments', 'COUNT('.CommentPeer::POST_ID.')');
- $c->addJoin(PostPeer::ID, CommentPeer::POST_ID, Criteria::LEFT_JOIN);
- $c->addGroupByColumn(PostPeer::ID);
- $c->addGroupByColumn(PostPeer::TITLE);
- // ...
- // more group-by columns if needed
- $rs = PostPeer::doSelectRS($c);
- $posts = array();
- while ($rs->next()) {
- $post = new Post();
- $post->hydrate($rs);
- $post->setNumComments($rs->getInt(PostPeer::NUM_COLUMNS + 1));
- $posts[] = $post;
- }
- return $posts;
- }
- SELECT post.*,sub.numcomments from post,
- (select post.post_id as post_id,COUNT(comments.post_id) AS numcomments
- FROM post INNER JOIN comments ON post.id = comments.post_id GROUP BY post.id) as sub
- where post.post_id = sub.post_id;
- $c = new Criteria();
- // count comments with
- $c->addAsColumn('numofcomments', 'COUNT('.CommentPeer::POST_ID.')');
- $c->addJoin(PostPeer::ID, CommentPeer::POST_ID, Criteria::LEFT_JOIN);
- $c->addGroupByColumn(PostPeer::ID);
- $c->addGroupByColumn(PostPeer::TITLE);
- // you can add more groupby column here
- //$c->addGroupByColumn(...more);
- $this->posts = PostPeer::doSelect($c);
- //inside a static function in class PostPeer:
- $c = new Criteria();
- self::addSelectColumns($c); //add all columns from PostPeer
- $c->addJoin(PostPeer::ID, CommentPeer::POST_ID, Criteria::LEFT_JOIN);
- $cu->addAsColumn('numofcomments', 'COUNT('.CommentPeer::POST_ID.')');
- $cu->addGroupByColumn(PostPeer::ID);
- $cu->addGroupByColumn(PostPeer::TITLE);
- :
- :
- //more group-by columns if needed
- return PostPeer::doSelectStmt($c);
- <div id="title">
- <?php echo post_array[1] ?>
- </div>
- //...and so on for other Post fields
Add Comment
Please, Sign In to add comment