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');