We are using Magento 2.1.7 and building some API endpoints to serve our need.

Following is how we get Products list and send them as json from API call.

namespace Vendor\Customer\Model;

use Magento\Framework\Api\Filter;

class ProductRepository
    protected $_productCollection, $productRepository, $searchCriteriaBuilder;

    public function __construct(
        \Magento\Catalog\Api\ProductRepositoryInterface $productRepository,
        \Magento\Framework\Api\SearchCriteriaBuilder $searchCriteriaBuilder
        $this->productRepository = $productRepository;
        $this->searchCriteriaBuilder = $searchCriteriaBuilder;

    * @return \Magento\Catalog\Api\Data\ProductSearchResultsInterface
    public function getProductCollection()
        $searchCriteria = $this->searchCriteriaBuilder->create();

        $this->_productCollection = $this->productRepository->getList($searchCriteria);

        return $this->_productCollection;

We need to know if we can add some search criteria to getProductCollection() method using Filter to get only those products associated to some active catalog price rule with discounted price.

Or if we have any other method to get Products with Catalog Price Rule and discounted Price, guide me in that direction.


1 Answer 1


When you create a catalog price rule, Magento creates some entries in catalogrule_product table. You can see which products are affected by the price rules created.

Unfortunately ProductRepository does not allow you to filter by catalog price rule. However, you can modify this class by extending it.

The only thing you need to add is the following line.

$collection->joinTable('catalogrule_product', 'product_id=entity_id', ['product_id'], null, 'inner');

Let's say create MyProductRepository as follows:

class MyProductRepository extends \Magento\Catalog\Model\ProductRepository
    public function getList(\Magento\Framework\Api\SearchCriteriaInterface $searchCriteria)
        /** @var \Magento\Catalog\Model\ResourceModel\Product\Collection $collection */
        $collection = $this->collectionFactory->create();

        foreach ($this->metadataService->getList($this->searchCriteriaBuilder->create())->getItems() as $metadata) {
        $collection->joinTable('catalogrule_product','product_id=entity_id', ['product_id'],null, 'inner');
        $collection->joinAttribute('status', 'catalog_product/status', 'entity_id', null, 'inner');
        $collection->joinAttribute('visibility', 'catalog_product/visibility', 'entity_id', null, 'inner');

        //Add filters from root filter group to the collection
        foreach ($searchCriteria->getFilterGroups() as $group) {
            $this->addFilterGroupToCollection($group, $collection);
        /** @var SortOrder $sortOrder */
        foreach ((array)$searchCriteria->getSortOrders() as $sortOrder) {
            $field = $sortOrder->getField();
                ($sortOrder->getDirection() == SortOrder::SORT_ASC) ? 'ASC' : 'DESC'

        $searchResult = $this->searchResultsFactory->create();
        return $searchResult;

Be careful, you cannot use \Magento\Catalog\Api\ProductRepositoryInterface anymore. You have to call your implementation.

$productRepository = $objectManager->get(\MyVendor\MyModule\Model\MyProductRepository::class);
  • 1
    Thank you @bunyamin for detailed answer. I have tried this approach and it gets all products with catalog rule price associated. We also need group by product_id in joinTable() as follows: $collection->joinTable('catalogrule_product','product_id=entity_id', ['product_id'],null, 'inner')->getSelect()->group('catalogrule_product.product_id');
    – mshakeel
    Jul 19, 2017 at 6:57
  • We also need to select it's discounted price on the basis of catalogrule, how can we select extra fields from joined table?
    – mshakeel
    Jul 19, 2017 at 7:06
  • @mshakeel I think the rule applied prices are stored in catalogrule_product_price table. You can also join this table. Jul 19, 2017 at 12:53
  • I am returning these products collection from an API call and json returned does not include reduced/special price.
    – mshakeel
    Jul 19, 2017 at 16:31
  • 2
    @mshakeel You need to define your interface and include the rule_price. Do not use Magento\Catalog\Api\Data\ProductSearchResultsInterface. Jul 19, 2017 at 17:21

Your Answer

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

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