Tag Filter - (Pagination)

Custom group by pagination and a calculated field

This is a basic example of how to use the CakePHP paginator helper with the group by condition, it took a little mucking around but this is how I got it working.

In this example we have a Comment controller which I want to paginate a custom query displaying an IP address and a calculated count field of the number of spam comments from this particular IP address...

Filed under: Cakephp  Tags: Pagination
(default) 9 queries took 8 ms
NrQueryErrorAffectedNum. rowsTook (ms)
1DESCRIBE `posts`17171
2DESCRIBE `comments`11111
3DESCRIBE `tags`220
4DESCRIBE `categories`221
5DESCRIBE `posts_tags`220
6DESCRIBE `categories_posts`220
7SELECT Post.* FROM posts Post, posts_tags pt, tags t WHERE t.tag = ('Pagination') AND t.id = pt.tag_id AND pt.post_id = Post.id AND Post.status = 1 ORDER BY Post.created DESC112
8SELECT category, COUNT(*) AS cat_cnt FROM categories_posts c2p INNER JOIN categories Cat ON c2p.category_id = Cat.id GROUP BY category15151
9SELECT tag, COUNT(*) AS tag_cnt FROM posts_tags t2p INNER JOIN tags Tag ON t2p.tag_id = Tag.id GROUP BY tag49492