How can I filter Reviews based on their average rating to only display above a certain amount?

Question

I'm trying to change up the review list block on our website such that it only displays the top three latest four star or higher reviews.

Currently I am trying to go about this in the following manner:

    $_store_id = Mage::app()->getStore()->getId();
    $_reviews = Mage::getModel('review/review')->getResourceCollection();
$_reviews->addStoreFilter($_store_id)
        ->addStatusFilter( Mage_Review_Model_Review::STATUS_APPROVED )
        ->setDateOrder()
        ->addRateVotes();
// prepare for ratings
$_reviews->getSelect()->join(array('ratings'=>'rating_option_vote_aggregated'), 'main_table.review_id=ratings.primary_id', array('percent_approved'));
$_reviews->getSelect()->where('ratings.percent_approved >= 80');
echo $_reviews->getSelect();
$_reviews->load();
    <?php if (count($_reviews)):?>
        <h2><?php echo $this->__('Our Top Favorite Reviews') ?></h2>
        <ul>
        <?php $i=0; foreach ($_reviews as $_review):?>
        <li>Each review goes here</li>
        <?php endforeach; ?>
    <?php endif; ?>

The gist of it is that I want to perform a join with the aggregated ratings table and have a filter to strip out the reviews with a rating under 80%. The problem I'm having is that even though the echo $_reviews->getSelect(); outputs the desired SQL, the $_reviews object count does not change or even filter out these reviews that are under 80%.

Here is the outputted SQL:

SELECT `main_table`.*, `detail`.`detail_id`, `detail`.`title`, `detail`.`detail`, `detail`.`nickname`, `detail`.`customer_id`, `ratings`.`percent_approved` 
FROM `review` AS `main_table` 
INNER JOIN `review_detail` AS `detail` 
    ON main_table.review_id = detail.review_id 
INNER JOIN `review_store` AS `store` 
    ON main_table.review_id=store.review_id 
INNER JOIN `rating_option_vote_aggregated` AS `ratings` 
    ON main_table.review_id=ratings.primary_id 
WHERE (store.store_id IN('1')) 
    AND (main_table.status_id=1) 
    AND (ratings.percent_approved > 60) 
ORDER BY main_table.created_at DESC

Does anybody have a clue why my select statements on Mage_Review_Model_Resource_Review_Collection aren't taking place? I've already tried modifying to core file app\code\core\Mage\Review\Model\Resource\Review\Collection.php to make these changes through a function call, however that didn't work either.

I'm feeling quite stumped and could use some help. Thanks in advance.

Answer 1

$_reviews->addRateVotes() iterates through $this->getItems(), which does an implicit load() before your changes to SELECT get to apply. This is because load() sets a flag and does not actually load multiple times. You can use isLoaded() to observe this behaviour.

Move the addRateVotes() call after the SELECT updates and it should work as the rest of it looks good.

Answer 2

public function clearOrdering() { $this->_orders = array(); return $this; }

  **public function getTableName($model)
{
    $resources = Mage::getSingleton('core/resource');
    return $resources->getTableName($model);
}**


**public function setDateOrder($dir = 'DESC')
{
   /* $this->setOrder('main_table.created_at', $dir);*/
    $ratingTableName = $this->getTableName('rating/rating_option_vote');
            $this->_select->joinInner(
                array('ov' => $ratingTableName), '(ov.review_id = main_table.review_id)'
               /* array('summa' => 'SUM(percent)')*/
            )->where('ov.rating_id',array('eq' => 3));
            $this->clearOrdering()->setOrder('percent','desc');
    return $this;
}**

Answer 3

 public function clearOrdering()
    {
        $this->_orders = array();
        return $this;
    }
      public function getTableName($model)
    {
        $resources = Mage::getSingleton('core/resource');
        return $resources->getTableName($model);
    }
    public function setDateOrder($dir = 'DESC')
    {
       /* $this->setOrder('main_table.created_at', $dir);*/
        $ratingTableName = $this->getTableName('rating/rating_option_vote');

                $this->_select->joinInner(
                    array('ov' => $ratingTableName), '(ov.review_id = main_table.review_id)'
                   /* array('summa' => 'SUM(percent)')*/
                )->where('ov.rating_id',array('eq' => 3));


                $this->clearOrdering()->setOrder('percent','desc');

        return $this;
    }