Get random 5 star review

Question

What is the most efficient way to get a single, random, 5 star review (from any/all products).

I'm trying to display a single random review on our homepage. Here's what I'm working with currently but it has several issues.

$review = Mage::getModel('review/review');
$collection = $review->getProductCollection();
$collection
    ->addAttributeToSelect('*')
    ->getSelect()
            ->limit(5)
            ->order('rand()');
$review->appendSummary($collection);

foreach($collection as $product) {
$ratingSummary = $product->getRatingSummary();
$starRating = $ratingSummary['rating_summary'];

if($starRating == 100) {
    echo "<h1>". $product->getDetail() ."</h1>";
}

Answer 1

Try something like this:

$reviews = Mage::getModel('review/review')
    ->getResourceCollection()
    ->addStoreFilter(Mage::app()->getStore()->getId())
    ->addStatusFilter(Mage_Review_Model_Review::STATUS_APPROVED)
;

$reviews->getSelect()
    ->joinInner(array('rs' => 'review_entity_summary'), 'rs.review_id = main_table.entity_id')
    ->where('rs.rating_summary = 100')
    ->order(new Zend_Db_Expr('rand()'))
;

$randomReview = $reviews->getFirstItem();

LIMIT is not needed here as you are displaying only one record.

Be aware that RAND() is not the best idea if you have a lot of records.

Answer 2

Based on Rafal-Kos suggestion. Needed to update tables and columns:

    $reviews = Mage::getModel('review/review')
    ->getCollection()
    ->addStoreFilter(Mage::app()->getStore()->getId())
    ->addStatusFilter(Mage_Review_Model_Review::STATUS_APPROVED)
;

$reviews->getSelect()
    ->joinInner(array('rs' => 'rating_option_vote'), 'rs.review_id = main_table.review_id')
    ->where('rs.percent = 100')
    ->order(new Zend_Db_Expr('rand()'))
;

$randomReview = $reviews->getFirstItem();