I created a widget which is a display product section for a specific catalog price rule selected by admin.

Ex: Admin select 20% off on pants from backend & '10' will be limit of displaying product on the frontend, it will display 10 products which is affected by that specific catalog price rule.

I am using custom SQL query to fetch product id as per catalog price rule, everting is working perfectly except SQL query also count child product as a parent product, I need to filter that.

Here is my block where I am getting product ids:

use Magento\Catalog\Model\AbstractModel;
use Magento\Framework\App\Config\ScopeConfigInterface;
use Magento\Catalog\Block\Product\Context;
use Magento\Widget\Block\BlockInterface;

class Posts extends AbstractModel implements BlockInterface {

  public function myFunction(){
    //Getting the value of catalog rule id & number of limitations for the product.

    $myvalue = $this->_scopeConfig->getValue('CustomSection/Customgroup/Customfield', \Magento\Store\Model\ScopeInterface::SCOPE_STORE);
    $myvalue2 = $this->_scopeConfig->getValue('CustomSection/Customgroup/CustomSecondfield', \Magento\Store\Model\ScopeInterface::SCOPE_STORE);

    $objectManager = \Magento\Framework\App\ObjectManager::getInstance(); // Instance of object manager
    $resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
    $connection = $resource->getConnection();
    $tableName = $resource->getTableName('catalogrule_product'); //gives table name with prefix
    $sql = "SELECT DISTINCT `product_id` FROM ". $tableName." WHERE rule_id=".$myvalue." LIMIT ".$myvalue2;
    $results = $connection->fetchAll($sql);

    return $results;
 protected $_template = "widget/posts.phtml";

currently, it's displaying 10 products which is similar like Parent Product and other 9 will be its child product which is different by color or size, etc

I appreciate if someone helps me to get product id collection by any other way.

1 Answer 1


Use Model\Rule to get a collection of products by catalog price rules.

I also used Type\Configurable to get product type, so the product can filter by parent or child.

   public function __construct(Context $context,
                              \Magento\CatalogRule\Model\Rule $rule,
                            \Magento\ConfigurableProduct\Model\ResourceModel\Product\Type\Configurable $catalogProductTypeConfigurable,
                            array $data = [])
    parent::__construct($context, $data);
    $this->_scopeConfig = $_scopeConfig;
    $this->rule = $rule;
    $this->_catalogProductTypeConfigurable = $catalogProductTypeConfigurable;

    public function myFunction(){
    $getRuleId //get catalog rule by scopeconfig
    $getProductLimit // get product limit by scopeconfig

    $results = $this->rule->load($getRuleId);
    $products = $results->getMatchingProductIds();
    $finalproducts = [];
    foreach ($products as $key=>$value){
        $parentByChild = $this->_catalogProductTypeConfigurable->getParentIdsByChild($key);
        if (isset($parentByChild[0]) && !in_array($parentByChild[0],$finalproducts)) {
            $finalproducts[] = $parentByChild[0];
    $output = array_slice($finalproducts, 0, $getProductLimit);
    return $output;

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.