Sql query (inner join + count + group by) using Propel

Question

I want to show the number of comments for each blog post (along with category, date, author etc) on a page that has a list of blog posts. How do i write the following mysql query in propel?

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

where post is a blog table and comments, a table of comments with post_id as a foreign key to post.id. I cant seem to get 'numofcomments' as a column in the resultset. Currently i am using a non-ORM approach (which will be my last resort):

$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;

How can i access 'numofcomments' in the resulting Propel resultset?

EDIT: What i wanted to know is how i can write the above query in Propel? What i can do now is get the post table with inner join on comments table and then run doCount on comments table for each post-id. This results in 1 query for Post table and many queries for comments table. I wish to reduce the sql queries to a minimum. Thanks :)

Answer 1

SELECT post.* , COUNT(comments.post_id) AS numcomments FROM post INNER JOIN comments ON post.id = comments.post_id GROUP BY post.id,post.secondcol,post.thirdcol; and so on, just list all the individual columns in your post table, since you select post.*, you have to list them all, not just post.post_id.

Alternativly

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;

(There's a 3. and easier way as well, which I forgot now..)

Answer 2

If you want to pack in additional data in your objects returned by a custom query, you can override the default doSelect() method in your peer class and add this additional data from your query into each object that is returned.

In your posts class, you can add a protected variable, let's call it "numComments".

class Post extends BasePost {
  protected $numComments;

  public function setNumComments($v)
  {
    $this->numComments = $v;
  }

  public function getNumComments()
  {
    return $this->numComments;
  }
  ...
}

and then in your PostPeer class in your static doSelectWithCount() method:

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;
}

Answer 3

Here is the propel version of your sql query.

$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);

You should use LEFT JOIN instead INNER JOIN. Because. with inner join you can only access the posts that have least one comment. With left join you can select uncommented posts.

I hope this helps.

Answer 4

Well this is what worked but its a sad way to do it :|

//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);

Then in template i access the array as:

<div id="title">
   <?php echo post_array[1] ?>
</div>
//...and so on for other Post fields

How do i make an array association in model so that i can write "post_array['title']" in template instead of "post_array[1]"? Also, is this workaround secure? Any better suggestion. I am using Propel 1.3