Use HAVING to Filter collection by computed columns (COUNT, SUM,...)

Hi, guys! In this post, I will introduce you how to filter collection by computed columns (such as COUNT, SUM,...).

Here is an example: Let's see rating_option_vote table that contains columns:

vote_id, option_id, remote_ip, remote_ip_long, customer_id, entity_pk_value, rating_id, review_id, percent, value

This table contains information about each vote (star) in reviews. And we are going to filter all reviews with average of votes greater than 50.

First of all, we must compute the average of votes for each review:

$collection = Mage::getSingleton('rating/rating_option_vote')->getCollection();
->columns('SUM(percent)/COUNT(*) AS average_percent')

After that, we use HAVING to filter by this new column:

$collection->getSelect()->having('average_percent > ?', '50');

Why do we have to use getSelect()->having... instead of normal addFieldToFiler... or getSelect()->where...?

The reason is because normal RDBMSs (relational database management systems), in my case is MySQL, do NOT support for putting the condition of computed columns.

However, I recommend you should NOT use HAVING instead of WHERE because HAVING is much slower than WHERE. HAVING restricts results after returning rows while WHERE restricts results before rows are returned.


Thai Nguyen