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

Improve this question
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
Improve this answer
7
  • 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 '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 '19 at 9:25
  • should i use one more join?
    – sudo55
    Feb 19 '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 '19 at 9:31
  • 1
    yes, same column name exists in both table
    – Sohel Rana
    Feb 19 '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.