0

I want a SQL query to get product reviews by SKU using Magento2 for example,

It should contain,

SKU,id,title,discription,name 

Please help me someone for the same. since I am new to Magento 2.

1
  • Why not use product model to get it instead of sql query? Jan 1, 2021 at 4:47

3 Answers 3

0
<?php
namespace VendorName\Reviews\Model;

use Magento\Catalog\Api\ProductRepositoryInterface;
use Magento\Review\Model\ResourceModel\Review\Collection as ReviewCollection;

class Results
{
    /**
     * @var ReviewCollection
     */
    private $reviewsCollection;
    /**
     * @var \Magento\Review\Model\ResourceModel\Review\CollectionFactory
     */
    private $reviewsColFactory;
    /**
     * @var \Magento\Store\Model\StoreManagerInterface
     */
    private $storeManager;
    /**
     * @var ProductRepositoryInterface
     */
    private $productRepository;

    /**
     * Constructor
     *
     * @param \Magento\Review\Model\ResourceModel\Review\CollectionFactory $reviewsColFactory
     * @param \Magento\Store\Model\StoreManagerInterface $storeManager
     * @param ProductRepositoryInterface $productRepository
     */
    public function __construct(
        \Magento\Review\Model\ResourceModel\Review\CollectionFactory $reviewsColFactory,
        \Magento\Store\Model\StoreManagerInterface $storeManager,
        ProductRepositoryInterface $productRepository
    ) {
        $this->reviewsColFactory = $reviewsColFactory;
        $this->storeManager = $storeManager;
        $this->productRepository = $productRepository;
    }

    public function execute()
    {
        // your code

        ############ function to load reviews by sku ################
        $sku = 'YOUR-SKU';
        $reviews = $this->getReviewsCollectionBySku($sku);
        ############ end - function to load reviews by sku ################

        // your code           
    }

    /**
     * @param string $sku
     * @return ReviewCollection
     * @throws \Magento\Framework\Exception\NoSuchEntityException
     */
    public function getReviewsCollectionBySku(string $sku): ReviewCollection
    {
        if (null === $this->reviewsCollection) {
            //$sku = 'PRODUCT-1';
            $product = $this->productRepository->get($sku);

            $this->reviewsCollection = $this->reviewsColFactory->create()
            //->addFieldToSelect(['detail_id', 'title', 'detail', 'nickname'])
            ->addStoreFilter(
                $this->storeManager->getStore()->getId()
            )->addStatusFilter(
                \Magento\Review\Model\Review::STATUS_APPROVED
            )->addEntityFilter(
                'product',
                $product->getId()
            )->setDateOrder();
        }
        return $this->reviewsCollection;
    }
}

This might do your work, it's NOT TESTED though. You can tweak this and do what you need to do. Un-comment the addFieldToSelect line in getReviewsCollectionBySku function to load the required fields. SKU of cause you already have at this point.

I wrote this based on below function in Magento core:

vendor/magento/module-review/Block/Product/View.php::getReviewsCollection
0

You can work with joins on below tables

Tables

catalog_product_entity

  • entity_id
  • sku

review

  • review_id
  • entity_id --> product entity id

review_detail

  • review_id
  • title
  • detail
  • nickname
  • store_id --> if using multi store

eav_attribute

  • attribute_id
  • entity_type_id --> usually is equal to 4 for product entity
  • attribute_code --> find out attribute id for product description, name

catalog_product_entity_text (for description) catalog_product_entity_varchar (for name)

  • attribute_id --> id from eav_attribute
  • entity_id --> product entity id
  • value
  • store_id --> if using multi store
0

You will need to join review & review_detail tables.

select r.review_id,sku,title,detail,nickname from review r
inner join catalog_product_entity p on p.entity_id = r.entity_pk_value
inner join review_detail d on d.review_id = r.review_id;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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