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.