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.

Magento ver. 1.7.0.2

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

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.