CakePHP-2.0: Refactor my code editing faster sql query, need a faster sql query

Question

mysql> describe posts;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id     | int(11)      | NO   |     | NULL    |                |
| title       | varchar(255) | NO   |     | NULL    |                |
| body        | text         | YES  |     | NULL    |                |
| category_id | int(11)      | NO   |     | NULL    |                |
| tags        | varchar(50)  | NO   |     | NULL    |                |
| mark        | tinyint(4)   | NO   |     | 1       |                |
| created     | datetime     | YES  |     | NULL    |                |
| modified    | datetime     | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql> describe comments;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| post_id  | int(11)      | NO   | MUL | NULL    |                |
| name     | varchar(255) | NO   |     | NULL    |                |
| email    | varchar(255) | NO   |     | NULL    |                |
| body     | varchar(500) | NO   |     | NULL    |                |
| mark     | tinyint(4)   | NO   |     | 1       |                |
| created  | datetime     | YES  |     | NULL    |                |
| modified | datetime     | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

I need the posts.title which has more comments limit 10 or there is no limit.

What i tried so far=>

        $conditions=array(
            'fields'=>array('Comment.post_id'),
            'group'=>array('Comment.post_id'),
            'order'=>array('count(Comment.post_id) DESC'),
            'limit'=>'5'
        );          
        $mostComments=$this->Post->Comment->find('all',$conditions);

        $postId=array();
        foreach($mostComments as $val){
            array_push($postId,$val['Comment']['post_id']);
        }
        $postsWithmostComments=$this->Post->find('all',array('conditions'=>array('Post.id '=>$postId)) );
        $this->set('postsWithmostComments',$postsWithmostComments);

Can anyone post sql query to find posts.it,posts.title with more comments? Or any cakephp find command?

Answer 1

Try this

$contain = array('Comment');

$posts = $this->Post->find('all', array('contain'=>$contain));

$posts_with_comments = array_filter($posts, 'ten_or_more');
uasort($posts_with_comments, 'order_by_comment_count');    

$this->set('postsWithmostComments',$posts_with_comments);

function order_by_comment_count($a, $b) {
    if (count($a['Comment'] == $b['Comment']) 
       return 0;
    return ($a['Comment'] < $b['Comment']) ? -1 : 1;
}

function ten_or_more($post) {
   return (count($post['Comment']) >= 10);
}