magento 2 new categroy product missing in category page

Question

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 Magento\CatalogInventory\Model\ResourceModel\Stock\status.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 Magento\Catalog\Model\ResourceModel\Product\Collection.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);

Answer 1

About the catalog page issue. Totally get that the products are from Elasticsearch, not MYSQL, and you've done a reindex. Good move!

Consider checking the reindexed docs in Elasticsearch or OpenSearch. If you've got Kibana or an OpenSearch dashboard, that'd be super handy. If not, maybe think about setting up Kibana or Open-search dashboard for easier monitoring.

A cool starting point could be adding a breakpoint in Magento\Elasticsearch7\SearchAdapter\Adapter::query(RequestInterface $request) to peek into Elasticsearch requests and responses.

Give it a shot and let me know how it goes. If you need more help, I'm around!