Skip to content

magento 2 new categroy product missing in category page

I am using magento 2.4.4.p1 upgrade from 2.3 to 2.4.4-p1 i have some products in my site in that few are disabled few are enabled after upgradation i am trying to enable one product it enable but not showing in category page when i open direct URL it showing and search page also it showing.

status -> enabled
visibility -> catalog search
qty -10000
instock
assigned to category

it is simple product
same product if i click duplicate and save it showing in category page but exisitng product when enable it not showing
i did reindex rest and reindex no luck

i tried to debug started list.phtml
getLoadedProductCollection() function to listproduct.php _getProductCollection() in this function print query in that query i am seeing only 2 product but category have 3
below is the query

SELECT e.*, price_index.price, price_index.tax_class_id, price_index.final_price, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS minimal_price, price_index.min_price, price_index.max_price, price_index.tier_price, IFNULL(review_summary.reviews_count, 0) AS reviews_count, IFNULL(review_summary.rating_summary, 0) AS rating_summary, stock_status_index.stock_status AS is_salable FROM catalog_product_entity AS e
INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = ‘1’
LEFT JOIN review_entity_summary AS review_summary ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND review_summary.entity_type = (SELECT review_entity.entity_id FROM review_entity WHERE (entity_code = ‘product’))
LEFT JOIN cataloginventory_stock_status AS stock_status_index ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 WHERE (e.entity_id IN (23462, 23513)) ORDER BY FIELD(e.entity_id,23462,23513)

i did more in to it _catalogLayer object in this file refers Layer.php printed query in getProductCollection() function i can see only same query with 2 products only
i have checked addIsInStockFilterToCollection() function in MagentoCatalogInventoryModelResourceModelStockstatus.php file see only

SELECT e.*, price_index.price, price_index.tax_class_id, price_index.final_price, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS minimal_price, price_index.min_price, price_index.max_price, price_index.tier_price, IFNULL(review_summary.reviews_count, 0) AS reviews_count, IFNULL(review_summary.rating_summary, 0) AS rating_summary, stock_status_index.stock_status AS is_salable FROM catalog_product_entity AS e
INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = ‘1’
LEFT JOIN review_entity_summary AS review_summary ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND review_summary.entity_type = (SELECT review_entity.entity_id FROM review_entity WHERE (entity_code = ‘product’))
LEFT JOIN cataloginventory_stock_status AS stock_status_index ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1

this query i want know where the below condition adding

WHERE (e.entity_id IN (23462, 23513)) ORDER BY FIELD(e.entity_id,23462,23513)
i check MagentoCatalogModelResourceModelProductCollection.php but did not find which function it adding please can any one tell me whic fucntion it adding i will check more in to it

Expected query

SELECT e.*, price_index.price, price_index.tax_class_id, price_index.final_price, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS minimal_price, price_index.min_price, price_index.max_price, price_index.tier_price, IFNULL(review_summary.reviews_count, 0) AS reviews_count, IFNULL(review_summary.rating_summary, 0) AS rating_summaryFROM catalog_product_entity AS e
INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = ‘1’
LEFT JOIN review_entity_summary AS review_summary ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND review_summary.entity_type = (SELECT review_entity.entity_id FROM review_entity WHERE (entity_code = ‘product’))
LEFT JOIN cataloginventory_stock_status AS stock_status_index ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 WHERE (e.entity_id IN (23462, 23513,16380)) ORDER BY FIELD(e.entity_id,23462,23513,16380);