How expensive is find('count') in CakePHP?

Question

I have a CakePHP application where I am trying to get a count of how many of each type of post a user has. I am also using the paginate helper to display all the user's posts on their profile.
I could:
1) Make a find('all', $conditions) request for all the user's posts then parse that for the counts I need (by going post by post and checking if it matches what I'm looking for).
2) Make multiplie find('count', $conditions) requests, getting all the counts I need individually.
3) Maintain new columns in the user's table to track these numbers when the posts are created (put that would involve writing to the user account whenever a new post is made).
Which one would be the best choice?

Answer 1

You should modify your find call to include a group by condition to aggregate the count by type. See here for more info: http://debuggable.com/posts/how-to-group-by-in-cakephps-new-release-part-2:4850d2c9-edbc-4f0e-9e0e-07d64834cda3

Answer 2

Fetching all records from the database and counting them in PHP is hugely wasteful nonsense. That's exactly what databases are for.

A find('count') just makes an SQL query like SELECT COUNT(*) FROM … WHERE …, which is really the fastest way to go. Add appropriate conditions to get the count you want, you may need to brush up on your SQL to know what conditions these are.

There's also the special of counter-caching, which you might want to look into if you're counting hasMany relations.