Product category reindex

smart_reindex_and_check.sh

#!/bin/bash

echo "Magento 2: Smart Reindex & Category Product Check 🚀"
echo "----------------------------------------------------"

# Check if we are in the Magento root directory
if [ ! -f "bin/magento" ]; then
    echo "⛔ Error: This script must be run from the Magento project root."
    exit 1
fi

# Ask for Category ID
read -p "Enter the Category ID to check: " CATEGORY_ID

# Check indexer status
echo ""
echo "✅ Checking indexer statuses:"
php bin/magento indexer:status

# Reindex necessary indexes
echo ""
echo "🔄 Reindexing catalog_category_product, catalog_category_product_index, catalogsearch_fulltext..."
php bin/magento indexer:reindex catalog_category_product
php bin/magento indexer:reindex catalog_product_category
php bin/magento indexer:reindex catalogsearch_fulltext

# Flush cache
echo ""
echo "♻️ Flushing Magento cache..."
php bin/magento cache:flush

# Connect to Database
echo ""
echo "🔎 Checking products in Category ID = $CATEGORY_ID..."

read -p "Enter the database name: " DB_NAME
read -p "Enter the database user: " DB_USER
read -s -p "Enter the database password: " DB_PASS
echo ""
read -p "Enter the store_id (e.g., 1 for default): " STORE_ID

# Query to find products
QUERY="SELECT
    cpe.sku AS 'SKU',
    cpev.value AS 'Product Name',
    CASE cpi.value
        WHEN 1 THEN 'Not Visible Individually'
        WHEN 2 THEN 'Catalog'
        WHEN 3 THEN 'Search'
        WHEN 4 THEN 'Catalog, Search'
        ELSE 'Unknown'
    END AS 'Visibility',
    CASE cps.value
        WHEN 1 THEN 'Enabled'
        WHEN 2 THEN 'Disabled'
        ELSE 'Unknown'
    END AS 'Status',
    CASE WHEN cisi.is_in_stock = 1 THEN 'In Stock' ELSE 'Out of Stock' END AS 'Stock Status'
FROM
    catalog_category_product_index_store${STORE_ID} AS ccpi
JOIN catalog_product_entity AS cpe ON ccpi.product_id = cpe.entity_id
LEFT JOIN catalog_product_entity_varchar AS cpev ON cpe.entity_id = cpev.entity_id
    AND cpev.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = 4)
LEFT JOIN catalog_product_entity_int AS cpi ON cpe.entity_id = cpi.entity_id
    AND cpi.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'visibility' AND entity_type_id = 4)
LEFT JOIN catalog_product_entity_int AS cps ON cpe.entity_id = cps.entity_id
    AND cps.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'status' AND entity_type_id = 4)
LEFT JOIN cataloginventory_stock_item AS cisi ON cpe.entity_id = cisi.product_id
WHERE
    ccpi.category_id = ${CATEGORY_ID}
ORDER BY
    cpe.sku;"

# Execute the query
mysql -u${DB_USER} -p${DB_PASS} ${DB_NAME} -e "${QUERY}"

echo ""
echo "🏁 Done! Check the results above: SKU, Product Name, Visibility, Status, and Stock Status."