0

I am trying to fecth data form review and rating_option_vote. i need all values of rating and all Rating count group by Rating Id.

public function getRatingSummeryByAttribute($currentProductId)
{
    $attributesValues = $this->ratingColFactory->create()
        ->addFieldToSelect('rating_id')
        ->addFieldToSelect('rating_code')
        ->getData();
    if ($attributesValues)
    {
        $totalRatingRecord = $this->_ratingFactory
            ->addFieldToFilter ('entity_pk_value', $currentProductId);
        $totalRatingRecord->getSelect()
            ->columns('SUM(value) as sum_vales')
            ->columns('count(rating_id) as total_rating')
            ->group('rating_id')->join(
                [
                    'review' => 'review'
                ],
                "main_table.review_id = review.review_id"
            )->where('status_id = 1')
        ;
echo $totalRatingRecord->getSelect()->__toString();

Query Printed

SELECT main_table., SUM(value) AS sum_vales, count(rating_id) AS total_rating, review. FROM rating_option_vote AS main_table INNER JOIN review ON main_table.review_id = review.review_id WHERE (entity_pk_value = '14') AND (status_id = 1) GROUP BY rating_id

Result : enter image description here

1

Two table column has a common column name is entity_pk_value. Try following way:


public function getRatingSummeryByAttribute($currentProductId)
{
    $attributesValues = $this->ratingColFactory->create()
        ->addFieldToSelect('rating_id')
        ->addFieldToSelect('rating_code')
        ->getData();
    if ($attributesValues)
    {
        $totalRatingRecord = $this->_ratingFactory
            ->addFieldToFilter ('main_table.entity_pk_value', $currentProductId);
        $totalRatingRecord->getSelect()
            ->columns('SUM(value) as sum_vales')
            ->columns('count(rating_id) as total_rating')
            ->group('rating_id')->join(
                [
                    'review' => 'review'
                ],
                "main_table.review_id = review.review_id"
            )->where('status_id = 1')
        ;
echo $totalRatingRecord->getSelect()->__toString();

And query


SELECT main_table.*, SUM(value) AS sum_vales, count(rating_id) AS total_rating FROM rating_option_vote AS main_table INNER JOIN review ON main_table.review_id = review.review_id WHERE (main_table.entity_pk_value = '14') AND (status_id = 1) GROUP BY rating_id
  • what if i want to join another table like the above query result with rating table. i need rating code according to rating Id. – sudo55 Feb 19 at 9:21
  • You can as many as you want but you need to specify which table column you want for a duplicate for avoiding ambiguous column. – Sohel Rana Feb 19 at 9:25
  • should i use one more join? – sudo55 Feb 19 at 9:30
  • i used another join, like this i.imgur.com/hQFlPR7.png but i got this error i.imgur.com/zgSH9p1.png – sudo55 Feb 19 at 9:31
  • 1
    yes, same column name exists in both table – Sohel Rana Feb 19 at 9:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.