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) ASminimal_price
,price_index
.min_price
,price_index
.max_price
,price_index
.tier_price
, IFNULL(review_summary.reviews_count, 0) ASreviews_count
, IFNULL(review_summary.rating_summary, 0) ASrating_summary
,stock_status_index
.stock_status
ASis_salable
FROMcatalog_product_entity
ASe
INNER JOINcatalog_product_index_price
ASprice_index
ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = ‘1’
LEFT JOINreview_entity_summary
ASreview_summary
ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND review_summary.entity_type = (SELECTreview_entity
.entity_id
FROMreview_entity
WHERE (entity_code = ‘product’))
LEFT JOINcataloginventory_stock_status
ASstock_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) ASminimal_price
,price_index
.min_price
,price_index
.max_price
,price_index
.tier_price
, IFNULL(review_summary.reviews_count, 0) ASreviews_count
, IFNULL(review_summary.rating_summary, 0) ASrating_summary
,stock_status_index
.stock_status
ASis_salable
FROMcatalog_product_entity
ASe
INNER JOINcatalog_product_index_price
ASprice_index
ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = ‘1’
LEFT JOINreview_entity_summary
ASreview_summary
ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND review_summary.entity_type = (SELECTreview_entity
.entity_id
FROMreview_entity
WHERE (entity_code = ‘product’))
LEFT JOINcataloginventory_stock_status
ASstock_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) ASminimal_price
,price_index
.min_price
,price_index
.max_price
,price_index
.tier_price
, IFNULL(review_summary.reviews_count, 0) ASreviews_count
, IFNULL(review_summary.rating_summary, 0) ASrating_summary
FROMcatalog_product_entity
ASe
INNER JOINcatalog_product_index_price
ASprice_index
ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = ‘1’
LEFT JOINreview_entity_summary
ASreview_summary
ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND review_summary.entity_type = (SELECTreview_entity
.entity_id
FROMreview_entity
WHERE (entity_code = ‘product’))
LEFT JOINcataloginventory_stock_status
ASstock_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);