Magento 2 : How to get collection of review with it's rating information

Question

I get review information from the review_detail table and get rating information from rating_opetion_vote Separately but I try to join these two tables and return collection but it is not working.

I try to Join both tables using the Join query as per the following.

protected $_productsreviewFactory;

public function __construct(
Magento\Review\Model\ResourceModel\Review\Product\CollectionFactory $productsreviewFactory)
{
    $this->_productsreviewFactory = $productsreviewFactory;
}

$collection = $this->_productsreviewFactory->create();
$collection->getSelect()
        ->join(
            ['rating_option_vote'],
            'review_detail.review_id = rating_option_vote.review_id',
            []
        );
return $collection->getData();

Answer 1

Now I found a solution and It is working for me.

class Exportreviews extends AbstractConfig
{    
    protected $ratingsFactory;
    public function __construct(
    \Magento\Review\Model\ResourceModel\Rating\CollectionFactory $ratingsFactory)
    {
        $this->ratingsFactory = $ratingsFactory;
    }
     public function execute()
     {
          $rating = $this->ratingsFactory->create();
          $rating->getSelect()
                ->join(
                    ['rating_option_vote'],
                    'main_table.rating_id=rating_option_vote.rating_id',
                    [
                        'rating_option_vote.review_id',
                        'rating_option_vote.value',
                    ]
                )
    }
}

      

Answer 2

First you can try to use this command for get query string (before get data line) $collection->getSelect()->__toString()

And then you will get query as a string, copy and paste it to mysql and you can see it can work ot not, if not you can see error.

Second collection not working with getDate method because 1 review can have many votes, so when you join like this -> can have some record with same review_id -> i think this is problem you can't getData of collection

Example

Answer 3

I can see at least 2 issues in your code:

1. The alias for the review table to be used in join conditions is rt and not review_detail. Additionally, in case you set an empty array for the $cols argument of the join function, then it will not select any columns from the joined table. If you want to select all the columns, you should just leave this arg empty or set it to '*'. So the code should look like this:

   protected $reviewCollectionFactory;
   
   public function __construct(
       CollectionFactory $reviewCollectionFactory
   ) {
       $this->reviewCollectionFactory = $reviewCollectionFactory;
   }
   
   public function getReviews()
   {
       $collection = $this->reviewCollectionFactory->create();
   
       $collection->getSelect()
           ->join(
               ['vote' => $collection->getTable('rating_option_vote')],
               'rt.review_id = vote.review_id'
           );
   
       return $collection;
   }
   

2. You should keep in mind that there may be several records in the rating_option_vote table related to one review. Because depending on your website configuration, there may be separate vote options for price, works as described, customer support, etc. In case you just need to get an average rating for each review, you should aggregate the data like this:

   protected $reviewCollectionFactory;
   
   protected $voteCollectionFactory;
   
   public function __construct(
       \Magento\Review\Model\ResourceModel\Review\Product\CollectionFactory $reviewCollectionFactory,
       \Magento\Review\Model\ResourceModel\Rating\Option\Vote\CollectionFactory $voteCollectionFactory
   ) {
       $this->reviewCollectionFactory = $reviewCollectionFactory;
       $this->voteCollectionFactory = $voteCollectionFactory;
   }
   
   public function getReviews()
   {
       $reviewCollection = $this->reviewCollectionFactory->create();
       $voteCollection = $this->voteCollectionFactory->create();
   
       $voteCollection->getSelect()
           ->reset(\Zend_Db_Select::COLUMNS)
           ->columns(['review_id', 'avg_percent' => new \Zend_Db_Expr('ROUND(SUM(percent) / COUNT(*))')])
           ->group('review_id');
   
       $reviewCollection->getSelect()
           ->joinLeft(
               ['vote' => $voteCollection->getSelect()],
               'rt.review_id = vote.review_id',
               ['vote.avg_percent']
           );
   
   
       return $reviewCollection;
   }