Convert SQL Select Query to Magento Collection

Question

I have a complex sql select query which actually returns sales report against specified categories. I want to load data as a collection to display but I'm not able to convert the query into Magento's equivalent getCollection statement. my query is below. thanks in advance

SELECT
 distinct catalog_product_entity_varchar.value,
 review_entity_summary.reviews_count,
 review_entity_summary.rating_summary
FROM
 catalog_product_entity_varchar
join 
 review_entity_summary on
 catalog_product_entity_varchar.entity_id = entity_pk_value
WHERE
 attribute_id = (
 SELECT
 attribute_id
 FROM
 eav_attribute
 WHERE
 entity_type_id = 4
 AND attribute_code = 'name'
 )
order by
 rating_summary ASC ;

Answer 1

You can do something like this:

$productCollection = $objectManager->create('Magento\Catalog\Model\ResourceModel\Product\Collection');
 
 $productCollection->getSelect()->joinLeft(
 ['review_entity_summary' => $productCollection->getTable('review_entity_summary')],
 'e.entity_id = review_entity_summary.entity_pk_value',
 ['reviews_count', 'rating_summary']
 );
 $productCollection->getSelect()->where(
 'catalog_product_entity_varchar.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE entity_type_id = 4 AND attribute_code = "name")'
 );
 $productCollection->getSelect()->distinct(true)->order('rating_summary ASC');
 $data = $productCollection->getData();

Note: You may need to make some changes to the above code as needed.

Also use of ObjectManager is deprecated so replace the same with dependency injection.

Answer 2

You can convert the above SQL select query to Magento 2 collection as follows:

use Magento\Catalog\Model\ResourceModel\Product;
use Magento\Review\Model\ResourceModel\Review\Collection as ReviewCollection;
$productAttributeCode = 'name';
/** @var Product $productResource */
$productResource = $objectManager->create(Product::class);
$productNameAttributeId = $productResource->getAttribute($productAttributeCode)->getId();
/** @var ReviewCollection $reviewCollection */
$reviewCollection = $objectManager->create(ReviewCollection::class);
$reviewCollection->getSelect()
 ->join(
 ['r' => $reviewCollection->getTable('review_entity_summary')],
 'catalog_product_entity_varchar.entity_id = r.entity_pk_value',
 ['reviews_count', 'rating_summary']
 )
 ->join(
 ['cp' => $productResource->getEntityTable()],
 'catalog_product_entity_varchar.entity_id = cp.entity_id',
 []
 )
 ->where('attribute_id = ?', $productNameAttributeId)
 ->distinct()
 ->order('rating_summary ASC');
$productNames = $reviewCollection->getColumnValues('value');