Is it possible for replacing the default product collection from model/resourcemodel with some stored procedure having some dynamic query which use different product ids as parameter based on current category on listing pages ?
Theoretically Stored procedures are known to be improving performance of sql queries, So just curious is there any way we could achieve the same in magento ?
I already know that we can call stored procedure like this in magento 2 and i know using objectmanager is not a proper way, it is just for example and experiment purpose.
$objectManager = MagentoFrameworkAppObjectManager::getInstance();
$resource = $objectManager->get('MagentoFrameworkAppResourceConnection');
$connection = $resource->getConnection();
$sql = 'CALL GetProducts()';
$_productCollection = $connection->fetchAll($sql);
And this is the SQL for the stored procedure.
DELIMITER $$
CREATE PROCEDURE GetProducts()
BEGIN
SELECT 1 AS `status`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`created_at`, `e`.`updated_at`, `e`.`sku`, `e`.`name`, `e`.`short_description`, `e`.`image`, `e`.`small_image`, `e`.`thumbnail`, `e`.`url_key`, `e`.`free`, `e`.`number_of_downloads`, `e`.`sentence1`, `e`.`url_path`, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_flat_1` AS `e` INNER JOIN `catalog_category_product_index_store1` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN (3,2,4) AND cat_index.category_id='2' INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id WHERE (e.entity_id NOT IN (13863,14096,13856,13924,15875,15869,13788)) AND (e.free = 1) ORDER BY `e`.`number_of_downloads` DESC LIMIT 60;
END$$
This is just a sample query that i tested, it can be anything as per your requirements.
Any suggestions are appreciated.