Optimize the performance of getProducts with making sure it returns the same result...…
- User
Optimize the performance of getProducts with making sure it returns the same result... also find if there is any mistake with filtering... import { Request, Response } from 'express'; import {query} from "../config/database"; import {brandsCache, productCache} from "../cache"; import { Brand, BrandsCacheData, CacheData, Color, CreateProductInput, Image, ImageInput, Product, Size, UpdateProductInput } from "product"; import {addProductToCache, deleteProductFromCache, updateProductInCache} from "../cacheHelper";
const fetchCategoryDetails = async (subcategoryIds: number[]) => { const categoriesResult = await query(
SELECT sc.id AS subcategory_id, sc.name AS subcategory_name, c.id AS main_category_id, c.name AS main_category_name FROM subcategories sc JOIN categories c ON sc.category_id = c.id WHERE sc.id = ANY($1::int[]), [subcategoryIds] );return categoriesResult.rows.map((row: any) => ({ id: row.subcategory_id, name: row.subcategory_name, main_category_id: row.main_category_id, main_category_name: row.main_category_name }));}; export const loadAndCacheAllProducts = async (): Promise => { const q =
SELECT p.id, p.name, p.description, p.brand_id, p.has_color, p.has_size, p.product_type, p.price, p.qty, p.sold, p.created_at, p.updated_at, sc.id AS subcategory_id, sc.name AS subcategory_name, c.id AS main_category_id, c.name AS main_category_name, s.id AS size_id, s.name AS size_name, s.price AS size_price, s.qty AS size_qty, s.sold AS size_sold, cl.id AS color_id, cl.name AS color_name, cl.price AS color_price, cl.qty AS color_qty, cl.sold AS color_sold, i.id AS image_id, i.url AS image_url, i.color_id AS image_color_id, i.size_id AS image_size_id FROM products p LEFT JOIN product_categories pc ON p.id = pc.product_id LEFT JOIN subcategories sc ON pc.category_id = sc.id LEFT JOIN categories c ON sc.category_id = c.id LEFT JOIN colors cl ON p.id = cl.product_id LEFT JOIN sizes s ON p.id = s.product_id LEFT JOIN images i ON (p.id = i.product_id AND (cl.id = i.color_id OR i.color_id IS NULL) AND (s.id = i.size_id OR i.size_id IS NULL));try { const result = await query(q, []); const productsMap: Record<number, Product> = {}; // Temporary map to hold sizes that need to be assigned to colors const sizesMap: Record<number, Size> = {}; // First pass: Initialize products and collect sizes result.rows.forEach((row: any) => { const productId = Number(row.id); // Initialize the product in the map if it doesn't exist if (!productsMap[productId]) { productsMap[productId] = { id: productId, name: row.name, description: row.description, brand_id: Number(row.brand_id), has_color: Boolean(row.has_color), has_size: Boolean(row.has_size), product_type: row.product_type, price: Number(row.price), qty: Number(row.qty), sold: Number(row.sold), created_at: new Date(row.created_at).toISOString(), updated_at: new Date(row.updated_at).toISOString(), categories: [], colors: [], sizes: [], // Will be populated only if has_color is false images: [] }; } const product = productsMap[productId]; // Add category if present and not already added if ( row.subcategory_id && row.subcategory_name && row.main_category_id && row.main_category_name ) { const categoryId = Number(row.subcategory_id); const mainCategoryId = Number(row.main_category_id); const existingCategory = product.categories.find(cat => cat.id === categoryId); if (!existingCategory) { product.categories.push({ id: categoryId, name: row.subcategory_name, main_category_id: mainCategoryId, main_category_name: row.main_category_name, }); } } // Add color if present and not already added if (row.color_id && row.color_name && row.has_size !== undefined) { const colorId = Number(row.color_id); let color = product.colors.find(color => color.id === colorId); if (!color) { color = { id: colorId, name: row.color_name, price: Number(row.color_price), qty: Number(row.color_qty), sold: Number(row.color_sold), has_size: Boolean(row.has_size), sizes: [], // Will be populated if has_size is true images: [] }; product.colors.push(color); } } // Collect sizes to assign later based on color association if (row.size_id && row.size_name) { const sizeId = Number(row.size_id); const colorId = row.color_id ? Number(row.color_id) : null; // If the size is associated with a color, store it for later assignment if (colorId && product.has_color) { sizesMap[sizeId] = { id: sizeId, name: row.size_name, price: Number(row.size_price), qty: Number(row.size_qty), sold: Number(row.size_sold), color_id: colorId, images: [] }; } else if (!product.has_color && product.has_size) { // If the product has sizes but no colors, assign size directly if (!product.sizes.find(s => s.id === sizeId)) { product.sizes.push({ id: sizeId, name: row.size_name, price: Number(row.size_price), qty: Number(row.size_qty), sold: Number(row.size_sold), color_id: null, images: [] }); } } } // Collect images to assign if (row.image_id && row.image_url) { const imageId = Number(row.image_id); // Avoid duplicate images if (!product.images.find(img => img.id === imageId)) { product.images.push({ id: imageId, url: row.image_url, color_id: row.image_color_id ? Number(row.image_color_id) : null, size_id: row.image_size_id ? Number(row.image_size_id) : null }); } // Associate image with color if applicable if (row.image_color_id) { const colorId = Number(row.image_color_id); const color = product.colors.find(c => c.id === colorId); if (color && !color.images.includes(row.image_url)) { color.images.push(row.image_url); } } // Associate image with size if applicable and size is within a color if (row.image_size_id) { const sizeId = Number(row.image_size_id); const size = sizesMap[sizeId]; if (size) { if (!size.images.includes(row.image_url)) { size.images.push(row.image_url); } } else { // If the product does not have colors, associate with product's sizes const standaloneSize = product.sizes.find(s => s.id === Number(row.image_size_id)); if (standaloneSize && !standaloneSize.images.includes(row.image_url)) { standaloneSize.images.push(row.image_url); } } } } }); // Second pass: Assign sizes to their respective colors Object.values(productsMap).forEach(product => { if (product.has_color) { // Initialize all product.sizes as empty product.sizes = []; // Iterate over collected sizes and assign to colors Object.values(sizesMap).forEach(size => { if (size.color_id === null) return; // Skip sizes without color_id const color = product.colors.find(c => c.id === size.color_id); if (color) { color.sizes.push({ id: size.id, name: size.name, price: size.price, qty: size.qty, sold: size.sold, color_id: size.color_id, images: size.images }); } }); // Update has_size for each color based on whether it has sizes product.colors.forEach(color => { color.has_size = color.sizes.length > 0; }); } // If has_color is false, sizes are already assigned directly to product.sizes }); const allProducts = Object.values(productsMap); // Calculate min and max prices across all products let minPrice = Infinity; let maxPrice = -Infinity; allProducts.forEach(product => { // Product price if (product.price < minPrice) minPrice = product.price; if (product.price > maxPrice) maxPrice = product.price; // Prices from colors and their sizes product.colors.forEach(color => { if (color.price < minPrice) minPrice = color.price; if (color.price > maxPrice) maxPrice = color.price; color.sizes.forEach(size => { if (size.price < minPrice) minPrice = size.price; if (size.price > maxPrice) maxPrice = size.price; }); }); // Prices from standalone sizes product.sizes.forEach(size => { if (size.price < minPrice) minPrice = size.price; if (size.price > maxPrice) maxPrice = size.price; }); }); // Fallback in case no prices are present if (minPrice === Infinity) minPrice = 0; if (maxPrice === -Infinity) maxPrice = 0; // Cache the data with a consistent key const cacheData: CacheData = { allProducts, minPrice, maxPrice }; productCache.set('allProducts', cacheData); console.log(`Cached ${allProducts.length} products with minPrice: ${minPrice} and maxPrice: ${maxPrice}.`); return cacheData; } catch (error) { console.error('Error loading products from DB:', error); throw error; // Propagate error to be handled by caller }};
/**
-
Fetches all brands from the database and caches them.
-
@returns {Promise} A promise that resolves to the cached brand data. */ const loadAndCacheAllBrands = async (): Promise => { const q =
SELECT s.*, c.name as category_name FROM subcategories s JOIN categories c ON s.category_id = c.id AND c.is_brand ORDER BY c.name, s.name;try { const result = await query(q, []); const allBrands: Brand[] = result.rows.map((row: any) => ({ id: Number(row.id), name: row.name, category_id: Number(row.category_id), category_name: row.category_name, }));
// Cache the brands const brandsCacheData: BrandsCacheData = { allBrands }; brandsCache.set('allBrands', brandsCacheData); console.log(`Cached ${allBrands.length} brands successfully.`); return brandsCacheData;} catch (error) { console.error('Error loading brands from DB:', error); throw error; // Propagate error to be handled by caller } };
/**
-
Controller to handle fetching and responding with filtered products.
-
Caches all products and brands on first request and performs in-memory filtering thereafter.
-
@param {Request} req - Express request object containing query parameters.
-
@param {Response} res - Express response object to send JSON data. */ export const getProducts = async (req: Request, res: Response): Promise => { try { // Retrieve cached products let cacheData: CacheData | undefined = productCache.get('cacheData');
if (!cacheData) { // Cache miss: Load from DB and cache cacheData = await loadAndCacheAllProducts(); console.log('Loaded products from database and cached them.'); } else { console.log('Loaded products from cache.'); } const { allProducts, minPrice, maxPrice } = cacheData; // Log total products before any filtering console.log(`Total products before filtering: ${allProducts.length}`); // Extract and sanitize query parameters let { search = '', c, sc, b, minPriceA = '0', maxPriceA = '', availability = 'all', sortBy = '', page = '1', itemsPerPage = '15', } = req.query; // Parse numerical values const pageNum = parseInt(page as string, 10) || 1; const itemsPerPageNum = parseInt(itemsPerPage as string, 10) || 15; const minPriceANum = parseFloat(minPriceA as string) || 0; let maxPriceANum = maxPriceA ? parseFloat(maxPriceA as string) : Infinity; // If maxPriceA is less than minPriceA, reset to Infinity if (!isNaN(maxPriceANum) && maxPriceANum < minPriceANum) { console.warn('Provided maxPriceA is less than minPriceA. Resetting maxPriceANum to Infinity.'); maxPriceANum = Infinity; } else if (isNaN(maxPriceANum)) { maxPriceANum = Infinity; } // Parse c, sc, b as numbers if present const cNum = c ? parseInt(c as string, 10) : undefined; const scNum = sc ? parseInt(sc as string, 10) : undefined; const bNum = b ? parseInt(b as string, 10) : undefined; // Initialize brand-related variables if 'b' is provided let allBrands: Brand[] = []; if (bNum !== undefined && !isNaN(bNum)) { // Retrieve cached brands let brandsCacheData: BrandsCacheData | undefined = brandsCache.get('allBrands'); if (!brandsCacheData) { // Cache miss: Load from DB and cache brandsCacheData = await loadAndCacheAllBrands(); console.log('Loaded brands from database and cached them.'); } else { console.log('Loaded brands from cache.'); } console.log(brandsCacheData) allBrands = brandsCacheData.allBrands; // Check if 'bNum' is a valid brand ID const isValidBrand = allBrands.some(brand => brand.category_id === bNum); if (!isValidBrand) { console.warn(`Brand ID ${bNum} is invalid.`); // Return zero products since 'b' is invalid res.json({ products: [], total: 0, page: pageNum, itemsPerPage: itemsPerPageNum, totalPages: 0, hasMore: false, minPrice, maxPrice, }); return; } } // Start with all products let filteredProducts = [...allProducts]; // Apply Search Filter if (search && typeof search === 'string') { const searchLower = search.toLowerCase(); const searchWords = searchLower.split(/\s+/).filter(Boolean); filteredProducts = filteredProducts.filter(product => searchWords.every(word => product.name.toLowerCase().includes(word)) ); } // Apply Category Filter (c) if (cNum !== undefined && !isNaN(cNum)) { filteredProducts = filteredProducts.filter(product => product.categories.some(cat => cat.main_category_id === cNum) ); } // Apply Subcategory Filter (sc) if (scNum !== undefined && !isNaN(scNum)) { filteredProducts = filteredProducts.filter(product => product.brand_id === scNum || product.categories.some(cat => cat.id === scNum) ); } // Apply Brand Filter (b) if (bNum !== undefined && !isNaN(bNum)) { console.log(`Applying Brand Filter: brand_id === ${bNum}`); const preFilterCount = filteredProducts.length; allBrands = allBrands.filter(brand => brand.category_id === bNum) filteredProducts = filteredProducts.filter(product => allBrands.some(brand => brand.id === product.brand_id) ); console.log(`Filtered from ${preFilterCount} to ${filteredProducts.length} products after applying brand filter.`); } // Apply Price Range Filter if (minPriceANum > 0 || maxPriceANum < Infinity) { filteredProducts = filteredProducts.filter(product => { const productPrices = product.sizes.map(size => size.price); if (productPrices.length === 0) return false; const productMinPrice = Math.min(...productPrices); const productMaxPrice = Math.max(...productPrices); return productMinPrice >= minPriceANum && productMaxPrice <= maxPriceANum; }); } // Apply Availability Filter if (availability === 'in_stock') { filteredProducts = filteredProducts.filter(product => product.sizes.some(size => size.qty > 0) ); } else if (availability === 'out_of_stock') { filteredProducts = filteredProducts.filter(product => product.sizes.every(size => size.qty === 0) ); } // Log total after filtering console.log(`Total products after filtering: ${filteredProducts.length}`); // Apply Sorting if (sortBy && typeof sortBy === 'string') { switch (sortBy) { case 'Price: Low to High': filteredProducts.sort((a, b) => { const aMin = Math.min(...a.sizes.map(s => s.price)); const bMin = Math.min(...b.sizes.map(s => s.price)); return aMin - bMin; }); break; case 'Price: High to Low': filteredProducts.sort((a, b) => { const aMax = Math.max(...a.sizes.map(s => s.price)); const bMax = Math.max(...b.sizes.map(s => s.price)); return bMax - aMax; }); break; case 'Name: A to Z': filteredProducts.sort((a, b) => a.name.localeCompare(b.name)); break; case 'Name: Z to A': filteredProducts.sort((a, b) => b.name.localeCompare(a.name)); break; case 'Best Selling': filteredProducts.sort((a, b) => b.sold - a.sold); break; case 'Newest Arrivals': filteredProducts.sort((a, b) => new Date(b.created_at).getTime() - new Date(a.created_at).getTime()); break; default: // If sortBy parameter doesn't match any case, do not sort break; } } // Apply Pagination const totalFilteredProducts = filteredProducts.length; const totalPages = Math.ceil(totalFilteredProducts / itemsPerPageNum); const startIdx = (pageNum - 1) * itemsPerPageNum; const endIdx = startIdx + itemsPerPageNum; const paginatedProducts = filteredProducts.slice(startIdx, endIdx); // Construct Response const response = { products: paginatedProducts, total: totalFilteredProducts, page: pageNum, itemsPerPage: itemsPerPageNum, totalPages, hasMore: endIdx < totalFilteredProducts, minPrice, // Global minPrice across all products maxPrice, // Global maxPrice across all products }; // Send Response res.json(response);} catch (error) { console.error('Error fetching products:', error); res.status(500).json({ error: 'Internal server error' }); } finally { // Optional: Any cleanup operations can be performed here // For example, monitoring memory usage or logging request completion } };
export const getProductById = async (req: Request, res: Response) => { const { id } = req.params; try { const productQuery =
SELECT p.id AS product_id, p.name AS product_name, p.description, p.brand_id, p.has_color, p.has_size, p.product_type, p.price AS product_price, p.qty AS product_qty, p.sold AS product_sold, p.created_at, p.updated_at, pc.category_id, sc.name AS category_name, cl.id AS color_id, cl.name AS color_name, cl.price AS color_price, cl.qty AS color_qty, cl.sold AS color_sold, s.id AS size_id, s.name AS size_name, s.price AS size_price, s.qty AS size_qty, s.sold AS size_sold, i.id AS image_id, i.url AS image_url FROM products p LEFT JOIN product_categories pc ON p.id = pc.product_id LEFT JOIN subcategories sc ON pc.category_id = sc.id LEFT JOIN colors cl ON p.id = cl.product_id LEFT JOIN sizes s ON (p.id = s.product_id AND (cl.id = s.color_id OR s.color_id IS NULL)) LEFT JOIN images i ON (p.id = i.product_id AND (cl.id = i.color_id OR i.color_id IS NULL) AND (s.id = i.size_id OR i.size_id IS NULL)) WHERE p.id = $1 ORDER BY pc.category_id, cl.id, s.id, i.id;const result = await query(productQuery, [id]); if (result.rows.length === 0) { return res.status(404).json({ error: 'Product not found' }); } // Process the result to group related data let product: { sold: any; has_size: any; images: any[]; description: any; created_at: any; colors: any[]; brand_id: any; has_color: any; product_type: any; updated_at: any; sizes: any[]; price: any; qty: any; name: any; id: number; categories: any[] }; product = { id: result.rows[0].product_id, name: result.rows[0].product_name, description: result.rows[0].description, brand_id: result.rows[0].brand_id, has_color: result.rows[0].has_color, has_size: result.rows[0].has_size, product_type: result.rows[0].product_type, price: result.rows[0].product_price, qty: result.rows[0].product_qty, sold: result.rows[0].product_sold, created_at: result.rows[0].created_at, updated_at: result.rows[0].updated_at, categories: [], colors: [], sizes: [], images: [] }; for (const row of result.rows) { if (row.category_id && !product.categories.some(c => c.id === row.category_id)) { product.categories.push({ id: row.category_id, name: row.category_name }); } // Add color if not already added if (row.color_id && !product.colors.some(c => c.id === row.color_id)) { product.colors.push({ id: row.color_id, name: row.color_name, price: row.color_price, qty: row.color_qty, sold: row.color_sold, images: [], sizes: [] }); } // Add size if not already added if (row.size_id && !product.sizes.some(s => s.id === row.size_id)) { const newSize = { id: row.size_id, name: row.size_name, price: row.size_price, qty: row.size_qty, sold: row.size_sold, color_id: row.color_id, // @ts-ignore images: [] }; product.sizes.push(newSize); // Add size to the corresponding color if (row.color_id) { const color = product.colors.find(c => c.id === row.color_id); if (color && !color.sizes.some((s: { id: any; }) => s.id === row.size_id)) { color.sizes.push(newSize); } } } if (row.image_id && !product.images.some(i => i.id === row.image_id)) { const newImage = { id: row.image_id, url: row.image_url, color_id: row.color_id, size_id: row.size_id }; product.images.push(newImage); if (row.color_id) { const color = product.colors.find(c => c.id === row.color_id); if (color && !color.images.includes(row.image_url)) { color.images.push(row.image_url); } } if (row.size_id) { const size = product.sizes.find(s => s.id === row.size_id); if (size && !size.images.includes(row.image_url)) { size.images.push(row.image_url); } } } } res.json({ product }); } catch (error) { console.error('Error fetching product by ID:', error); res.status(500).json({ error: 'Internal server error' }); }}; // Create a new product export const createProduct = async (req: Request, res: Response) => { try { console.log("Received product data:", req.body); // Log incoming data await query('BEGIN');
const productData: CreateProductInput = req.body; // Adjust price if necessary let adjustedPrice = productData.price; if (productData.price < 1000) { adjustedPrice = parseFloat((productData.price * 1000).toFixed(2)); } console.log("Inserting product with adjusted price:", adjustedPrice); // Insert product const productResult = await query( `INSERT INTO products (name, description, brand_id, has_color, has_size, product_type, price, qty, sold, created_at, updated_at) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) RETURNING id, created_at, updated_at`, [ productData.name, productData.description, productData.brand_id, Boolean(productData.has_color), Boolean(productData.has_size), productData.product_type, adjustedPrice, productData.qty ] ); const productId = productResult.rows[0].id; console.log(`Inserted product ID: ${productId}`); // Insert category associations const categoryInsertPromises = productData.category_ids.map((categoryId: number) => { console.log(`Associating category ID ${categoryId} with product ID ${productId}`); return query( `INSERT INTO product_categories (product_id, category_id) VALUES ($1, $2)`, [productId, categoryId] ); }); await Promise.all(categoryInsertPromises); console.log('Inserted all category associations.'); // Insert images const imagesInsertPromises = productData.images .filter((image: ImageInput) => image.url) // Ensure URL exists .map((image: ImageInput) => { console.log(`Inserting product image: ${image.url}`); return query( `INSERT INTO images (product_id, url, color_id, size_id) VALUES ($1, $2, $3, $4) RETURNING id, color_id, size_id, url`, [productId, image.url, image.color_id || null, image.size_id || null] ); }); const insertedImagesResults = await Promise.all(imagesInsertPromises); const insertedImages = insertedImagesResults.map((imgResult: any) => ({ id: imgResult.rows[0].id, url: imgResult.rows[0].url, color_id: imgResult.rows[0].color_id, size_id: imgResult.rows[0].size_id })); console.log(`Inserted ${insertedImages.length} product images.`); // Handle colors and sizes based on product type const insertedColors: number[] = []; const insertedSizes: number[] = []; if (productData.has_color && productData.colors) { for (const color of productData.colors) { // Adjust color price if necessary let adjustedColorPrice = color.price; if (color.price < 1000) { adjustedColorPrice = parseFloat((color.price * 1000).toFixed(2)); } console.log(`Inserting color: ${color.name} with has_size: ${color.has_size}`); // Insert color with explicit boolean casting const colorResult = await query( `INSERT INTO colors (product_id, name, price, qty, sold, has_size) VALUES ($1, $2, $3, $4, 0, $5) RETURNING id, has_size`, [productId, color.name, adjustedColorPrice, color.qty, Boolean(color.has_size)] ); const colorId = colorResult.rows[0].id; const colorHasSize = colorResult.rows[0].has_size; console.log(`Inserted color ID=${colorId}, has_size=${colorHasSize}`); insertedColors.push(colorId); // Insert color images const colorImagesInsertPromises = color.images.map((image: ImageInput) => { console.log(`Inserting color image: ${image.url}`); return query( `INSERT INTO images (product_id, color_id, url, size_id) VALUES ($1, $2, $3, $4) RETURNING id, color_id, size_id, url`, [productId, colorId, image.url, image.size_id || null] ); }); const insertedColorImagesResults = await Promise.all(colorImagesInsertPromises); console.log(`Inserted ${insertedColorImagesResults.length} images for color ID=${colorId}`); if (color.has_size && color.sizes) { for (const size of color.sizes) { // Adjust size price if necessary let adjustedSizePrice = size.price; if (size.price < 1000) { adjustedSizePrice = parseFloat((size.price * 1000).toFixed(2)); } console.log(`Inserting size: ${size.name} with price: ${adjustedSizePrice}`); // Insert size const sizeResult = await query( `INSERT INTO sizes (product_id, color_id, name, price, qty, sold) VALUES ($1, $2, $3, $4, $5, 0) RETURNING id`, [productId, colorId, size.name, adjustedSizePrice, size.qty] ); const sizeId = sizeResult.rows[0].id; console.log(`Inserted size ID=${sizeId} for color ID=${colorId}`); insertedSizes.push(sizeId); // Insert size images const sizeImagesInsertPromises = size.images.map((image: ImageInput) => { console.log(`Inserting size image: ${image.url}`); return query( `INSERT INTO images (product_id, color_id, size_id, url) VALUES ($1, $2, $3, $4) RETURNING id, color_id, size_id, url`, [productId, colorId, sizeId, image.url] ); }); const insertedSizeImagesResults = await Promise.all(sizeImagesInsertPromises); console.log(`Inserted ${insertedSizeImagesResults.length} images for size ID=${sizeId}`); } } } } else if (productData.has_size && productData.sizes) { for (const size of productData.sizes) { // Adjust size price if necessary let adjustedSizePrice = size.price; if (size.price < 1000) { adjustedSizePrice = parseFloat((size.price * 1000).toFixed(2)); } console.log(`Inserting standalone size: ${size.name} with price: ${adjustedSizePrice}`); // Insert size const sizeResult = await query( `INSERT INTO sizes (product_id, name, price, qty, sold) VALUES ($1, $2, $3, $4, 0) RETURNING id`, [productId, size.name, adjustedSizePrice, size.qty] ); const sizeId = sizeResult.rows[0].id; console.log(`Inserted standalone size ID=${sizeId}`); insertedSizes.push(sizeId); // Insert size images const sizeImagesInsertPromises = size.images.map((image: ImageInput) => { console.log(`Inserting standalone size image: ${image.url}`); return query( `INSERT INTO images (product_id, size_id, url) VALUES ($1, $2, $3) RETURNING id, color_id, size_id, url`, [productId, sizeId, image.url] ); }); const insertedSizeImagesResults = await Promise.all(sizeImagesInsertPromises); console.log(`Inserted ${insertedSizeImagesResults.length} images for standalone size ID=${sizeId}`); } } // Construct the new product object as it would appear in the cache const newProduct: Product = { id: productId, name: productData.name, description: productData.description, brand_id: productData.brand_id, has_color: productData.has_color, has_size: productData.has_size, product_type: productData.product_type, price: adjustedPrice, qty: productData.qty, sold: 0, created_at: productResult.rows[0].created_at.toISOString(), updated_at: productResult.rows[0].updated_at.toISOString(), categories: productData.category_ids.map((cid: number) => ({ id: cid, name: "", // Optional: Fetch and populate category names main_category_id: 0, // Optional: Set appropriately main_category_name: "" // Optional: Set appropriately })), colors: productData.has_color && productData.colors ? productData.colors.map((color, index) => ({ id: insertedColors[index], name: color.name, price: color.price || 0, qty: color.qty, sold: 0, has_size: Boolean(color.has_size), sizes: Boolean(color.has_size) && color.sizes ? color.sizes.map((size, sIndex) => ({ id: insertedSizes[sIndex], name: size.name, price: size.price, qty: size.qty, sold: 0, color_id: null, // Optional: Set if needed images: size.images.map(img => img.url) })) : [], images: color.images.map(img => img.url) })) : [], sizes: productData.has_size && !productData.has_color && productData.sizes ? productData.sizes.map((size, index) => ({ id: insertedSizes[index], name: size.name, price: size.price, qty: size.qty, sold: 0, color_id: null, images: size.images.map(img => img.url) })) : [], images: insertedImages }; console.log("New product to cache:", newProduct); // Log new product // Update the cache addProductToCache(newProduct); await query('COMMIT'); res.status(201).json({ message: 'Product added successfully!', productId }); } catch (error) { await query('ROLLBACK'); console.error('Error creating product:', error); res.status(500).json({ error: 'Internal server error' }); }};
export const updateProduct = async (req: Request, res: Response) => { const { id } = req.params; const productId = parseInt(id, 10); const productData: UpdateProductInput = req.body;
if (isNaN(productId)) { return res.status(400).json({ error: 'Invalid product ID' }); } try { await query('BEGIN'); let adjustedPrice = productData.price; if (productData.price < 1000) { adjustedPrice = parseFloat((productData.price * 1000).toFixed(2)); } const existingProductResult = await query('SELECT sold FROM products WHERE id = $1', [productId]); const existingSold = existingProductResult.rows[0]?.sold || 0; const result = await query( `UPDATE products SET name = $1, description = $2, brand_id = $3, has_color = $4, has_size = $5, product_type = $6, price = $7, qty = $8, updated_at = CURRENT_TIMESTAMP WHERE id = $9 RETURNING *`, [ productData.name, productData.description, productData.brand_id, productData.has_color, productData.has_size, productData.product_type, adjustedPrice, productData.qty, productId ] ); if (result.rows.length === 0) { await query('ROLLBACK'); return res.status(404).json({ error: 'Product not found' }); } const updatedProduct = result.rows[0]; await query('DELETE FROM product_categories WHERE product_id = $1', [productId]); const categoryInsertPromises = productData.category_ids.map((categoryId: number) => query( `INSERT INTO product_categories (product_id, category_id) VALUES ($1, $2)`, [productId, categoryId] ) ); await Promise.all(categoryInsertPromises); if (productData.images && productData.images.length > 0) { // Only delete and re-insert images if provided await query('DELETE FROM images WHERE product_id = $1', [productId]); const imagesInsertPromises = productData.images.map((image: ImageInput) => query( `INSERT INTO images (product_id, url, color_id, size_id) VALUES ($1, $2, $3, $4)`, [productId, image.url, image.color_id || null, image.size_id || null] ) ); await Promise.all(imagesInsertPromises); } // Colors and Sizes await query('DELETE FROM colors WHERE product_id = $1', [productId]); // Delete all existing colors await query('DELETE FROM sizes WHERE product_id = $1', [productId]); // Delete all existing sizes if (productData.has_color) { for (const color of productData.colors) { let adjustedColorPrice = color.price; if (color.price < 1000) { adjustedColorPrice = parseFloat((color.price * 1000).toFixed(2)); } const colorResult = await query( `INSERT INTO colors (product_id, name, price, qty, sold, has_size) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id`, [productId, color.name, adjustedColorPrice, color.qty, color.sold || 0, color.has_size] ); const colorId = colorResult.rows[0].id; if (color.has_size) { for (const size of color.sizes) { let adjustedSizePrice = size.price; if (size.price < 1000) { adjustedSizePrice = parseFloat((size.price * 1000).toFixed(2)); } await query( `INSERT INTO sizes (product_id, color_id, name, price, qty, sold) VALUES ($1, $2, $3, $4, $5, $6)`, [productId, colorId, size.name, adjustedSizePrice, size.qty, size.sold || 0] ); } } } } else if (productData.has_size) { for (const size of productData.sizes) { let adjustedSizePrice = size.price; if (size.price < 1000) { adjustedSizePrice = parseFloat((size.price * 1000).toFixed(2)); } await query( `INSERT INTO sizes (product_id, name, price, qty, sold) VALUES ($1, $2, $3, $4, $5)`, [productId, size.name, adjustedSizePrice, size.qty, size.sold || 0] ); } } // Cache Update const productQuery = ` SELECT * FROM products WHERE id = $1 `; const productResult = await query(productQuery, [productId]); const productRow = productResult.rows[0]; const categoryDetails = await fetchCategoryDetails(productData.category_ids) const cachedProduct: Product = { id: productRow.id, name: productRow.name, description: productRow.description, brand_id: productRow.brand_id, has_color: productRow.has_color, has_size: productRow.has_size, product_type: productRow.product_type, price: productRow.price, qty: productRow.qty, sold: existingSold, created_at: productRow.created_at, updated_at: productRow.updated_at, categories: categoryDetails, colors: [], sizes: [], images: [] // Initialize as empty, will be populated below }; const imagesResult = await query('SELECT * FROM images WHERE product_id = $1', [productId]); cachedProduct.images = imagesResult.rows.map((row: any): Image => ({ // Explicitly type as Image[] id: row.id, url: row.url, color_id: row.color_id, size_id: row.size_id, })); if (cachedProduct.has_color) { const colorsResult = await query('SELECT * FROM colors WHERE product_id = $1', [productId]); cachedProduct.colors = await Promise.all(colorsResult.rows.map(async (colorRow: any) => { const color: Color = { id: colorRow.id, name: colorRow.name, price: colorRow.price, qty: colorRow.qty, sold: colorRow.sold, has_size: colorRow.has_size, images: cachedProduct.images.filter((img) => img.color_id === colorRow.id).map(img => img.url), sizes: [] }; if (color.has_size) { // Using color.has_size const sizesResult = await query('SELECT * FROM sizes WHERE color_id = $1', [colorRow.id]); color.sizes = sizesResult.rows.map((sizeRow: any): Size => ({ id: sizeRow.id, name: sizeRow.name, price: sizeRow.price, qty: sizeRow.qty, sold: sizeRow.sold, color_id: sizeRow.color_id, images: cachedProduct.images.filter((img) => img.size_id === sizeRow.id).map(img => img.url), })); } return color; })); } if (cachedProduct.has_size && !cachedProduct.has_color) { const sizesResult = await query('SELECT * FROM sizes WHERE product_id = $1 AND color_id IS NULL', [productId]); cachedProduct.sizes = sizesResult.rows.map((sizeRow: any): Size => ({ id: sizeRow.id, name: sizeRow.name, price: sizeRow.price, qty: sizeRow.qty, sold: sizeRow.sold, color_id: null, images: cachedProduct.images.filter((img) => img.size_id === sizeRow.id).map(img => img.url), })); } else { cachedProduct.sizes = []; // Important: Initialize to empty array if no standalone sizes } updateProductInCache(cachedProduct); await query('COMMIT'); res.json({ message: 'Product updated successfully', product: updatedProduct }); } catch (error) { await query('ROLLBACK'); console.error('Error updating product:', error); res.status(500).json({ error: 'Internal server error' }); }}; export const getTopSellingProducts = async (req: Request, res: Response): Promise => { try { // Retrieve cached products let cacheData: CacheData | undefined = productCache.get('allProducts');
if (!cacheData) { // Cache miss: Load from DB and cache cacheData = await loadAndCacheAllProducts(); console.log('Loaded products from database and cached them.'); } else { console.log('Loaded products from cache.'); } const { allProducts } = cacheData; // Function to compute minimum price excluding 0 const getMinPrice = (product: Product): number | null => { let prices: number[] = []; // Include product base price if >0 if (product.price > 0) prices.push(product.price); // Include colors' prices if (product.has_color) { product.colors.forEach(color => { if (color.price > 0) prices.push(color.price); // Include sizes' prices within colors if (color.has_size) { color.sizes.forEach(size => { if (size.price > 0) prices.push(size.price); }); } }); } // Include standalone sizes' prices if (product.has_size) { product.sizes.forEach(size => { if (size.price > 0) prices.push(size.price); }); } if (prices.length === 0) return null; return Math.min(...prices); }; // Function to get one image URL (first available) const getOneImage = (product: Product): string | null => { if (product.images && product.images.length > 0) { return product.images[0].url; // Assuming each image has a 'url' property } return null; // Or return a placeholder image URL if desired }; // Filter products that are in stock and have a valid min price const filteredProducts = allProducts .map(product => { // Determine if the product is in stock const isInStock = product.qty > 0 || (product.has_color && product.colors.some(color => color.qty > 0 || (color.has_size && color.sizes.some(size => size.qty > 0)))) || (product.has_size && product.sizes.some(size => size.qty > 0)); // Calculate min price const minPrice = getMinPrice(product); // Get one image const image = getOneImage(product); return { id: product.id, title: product.name, description: product.description, sold: Math.abs(product.sold), // Ensure sold is absolute isInStock, minPrice, image }; }) .filter(product => product.isInStock && product.minPrice !== null && product.minPrice > 0); if (filteredProducts.length === 0) { res.status(200).json({ products: [] }); } // Sort the products by sold in descending order using absolute sold values filteredProducts.sort((a, b) => b.sold - a.sold); // Select the top two products const topTwoProducts = filteredProducts.slice(0, 2).map(product => ({ id: product.id, title: product.title, description: product.description, minPrice: product.minPrice as number, image: product.image })); res.status(200).json({ products: topTwoProducts }); } catch (error) { console.error('Error fetching top selling products:', error); res.status(500).json({ error: 'Internal server error' }); }};
export const deleteProduct = async (req: Request, res: Response) => { const { id } = req.params; const productId: number = parseInt(id, 10);
if (isNaN(productId)) { return res.status(400).json({ error: 'Invalid product ID' }); } try { await query('BEGIN'); // Delete related data first await query('DELETE FROM product_categories WHERE product_id = $1', [productId]); await query('DELETE FROM images WHERE product_id = $1', [productId]); await query('DELETE FROM sizes WHERE product_id = $1', [productId]); await query('DELETE FROM colors WHERE product_id = $1', [productId]); // Delete the product const result = await query('DELETE FROM products WHERE id = $1 RETURNING *', [productId]); if (result.rows.length === 0) { await query('ROLLBACK'); return res.status(404).json({ error: 'Product not found' }); } // Delete the product from the cache deleteProductFromCache(productId); await query('COMMIT'); res.json({ message: 'Product and related data deleted successfully' }); } catch (error) { await query('ROLLBACK'); console.error('Error deleting product:', error); res.status(500).json({ error: 'Internal server error' }); }}; product.ts: // src/types/productInput.ts
export interface ImageInput { url: string; color_id?: number | null; size_id?: number | null; }
export interface SizeInput { id?: number; // Optional for creation name: string; price: number; // Changed from string to number qty: number; sold?: number; color_id?: number | null; images: ImageInput[]; }
export interface ColorInput { id?: number; // Optional for creation name: string; price: number; // Changed from string to number qty: number; sold?: number; has_size: boolean; sizes: SizeInput[]; images: ImageInput[]; }
export interface CreateProductInput { name: string; description: string; brand_id: number; has_color: boolean; has_size: boolean; product_type: string; price: number; // Changed from string to number qty: number; category_ids: number[]; images: ImageInput[]; colors?: ColorInput[]; // Optional based on has_color sizes?: SizeInput[]; // Optional based on has_size and has_color }
export interface UpdateProductInput extends CreateProductInput {}
export interface Category { id: number; name: string; main_category_id: number; main_category_name: string; }
export interface Image { id: number; url: string; color_id: number | null; size_id: number | null; }
export interface Size { id: number; name: string; price: number; // Changed from string to number for arithmetic operations qty: number; sold: number; color_id: number | null; images: string[]; }
export interface Color { id: number; name: string; price: number; // Changed from string to number qty: number; sold: number; has_size: boolean; // Mandatory property sizes: Size[]; images: string[]; }
export interface Product { id: number; name: string; description: string; brand_id: number; // Also a subcategory_id for brands has_color: boolean; has_size: boolean; product_type: string; price: number; // Changed from string to number qty: number; sold: number; created_at: string; updated_at: string; categories: Category[]; colors: Color[]; sizes: Size[]; images: Image[]; }
export interface Brand { id: number; name: string; category_id: number; category_name: string; }
export interface CacheData { allProducts: Product[]; minPrice: number; maxPrice: number; }
export interface BrandsCacheData { allBrands: Brand[]; }
cacheHelper.ts: // src/cacheHelpers.ts
import { productCache } from "./cache"; import { Product, CacheData, Color, Size } from "product"; // Adjust the import path as necessary
/**
-
Adds a new product to the cache. */ export const addProductToCache = (newProduct: Product) => { try { const cacheData: CacheData | undefined = productCache.get('allProducts'); if (cacheData) { cacheData.allProducts.push(newProduct);
// Extract all prices const productPrices: number[] = []; productPrices.push(newProduct.price); newProduct.colors.forEach((color: Color) => { productPrices.push(color.price); color.sizes.forEach((size: Size) => productPrices.push(size.price)); }); newProduct.sizes.forEach((size: Size) => productPrices.push(size.price)); // Update minPrice and maxPrice if (productPrices.length > 0) { const productMinPrice = Math.min(...productPrices, cacheData.minPrice); const productMaxPrice = Math.max(...productPrices, cacheData.maxPrice); cacheData.minPrice = Math.min(cacheData.minPrice, productMinPrice); cacheData.maxPrice = Math.max(cacheData.maxPrice, productMaxPrice); } productCache.set('allProducts', cacheData); console.log('Added new product to cache.'); } else { // Flush cache to force reload on next access productCache.del('allProducts'); console.log('Cache unavailable. Flushed product cache.'); }} catch (error) { console.error('Error adding product to cache:', error); productCache.del('allProducts'); } };
/**
-
Updates an existing product in the cache. */ export const updateProductInCache = (updatedProduct: Product) => { try { const cacheData: CacheData | undefined = productCache.get('allProducts'); if (cacheData) { const index = cacheData.allProducts.findIndex(p => p.id === updatedProduct.id); if (index !== -1) { cacheData.allProducts[index] = updatedProduct;
// Recalculate minPrice and maxPrice const allPrices: number[] = []; cacheData.allProducts.forEach(product => { allPrices.push(product.price); product.colors.forEach(color => { allPrices.push(color.price); color.sizes.forEach(size => allPrices.push(size.price)); }); product.sizes.forEach(size => allPrices.push(size.price)); }); if (allPrices.length > 0) { cacheData.minPrice = Math.min(...allPrices); cacheData.maxPrice = Math.max(...allPrices); } else { cacheData.minPrice = 0; cacheData.maxPrice = 0; } productCache.set('allProducts', cacheData); console.log('Updated product in cache.'); } else { // Product not found; flush cache productCache.del('allProducts'); console.log('Product not found in cache. Flushed product cache.'); } } else { // Flush cache to force reload productCache.del('allProducts'); console.log('Cache unavailable. Flushed product cache.'); }} catch (error) { console.error('Error updating product in cache:', error); productCache.del('allProducts'); } };
/**
-
Deletes a product from the cache. */ export const deleteProductFromCache = (productId: number) => { try { const cacheData: CacheData | undefined = productCache.get('allProducts'); if (cacheData) { const initialLength = cacheData.allProducts.length; cacheData.allProducts = cacheData.allProducts.filter(p => p.id !== productId); if (cacheData.allProducts.length < initialLength) { // Recalculate minPrice and maxPrice const allPrices: number[] = []; cacheData.allProducts.forEach(product => { allPrices.push(product.price); product.colors.forEach(color => { allPrices.push(color.price); color.sizes.forEach(size => allPrices.push(size.price)); }); product.sizes.forEach(size => allPrices.push(size.price)); });
if (allPrices.length > 0) { cacheData.minPrice = Math.min(...allPrices); cacheData.maxPrice = Math.max(...allPrices); } else { cacheData.minPrice = 0; cacheData.maxPrice = 0; } productCache.set('allProducts', cacheData); console.log('Deleted product from cache.'); } else { // Product not found; flush cache productCache.del('allProducts'); console.log('Product not found in cache. Flushed product cache.'); } } else { // Flush cache to force reload productCache.del('allProducts'); console.log('Cache unavailable. Flushed product cache.'); }} catch (error) { console.error('Error deleting product from cache:', error); productCache.del('allProducts'); } };
/**
-
Decreases product, size, or color quantity in the cache based on identifiers.
-
@param productId - ID of the product
-
@param sizeId - ID of the size (optional)
-
@param colorId - ID of the color (optional)
-
@param quantity - Quantity to decrease */ export const decreaseProductQtyInCache = (productId: number, sizeId: number | null, colorId: number | null, quantity: number) => { try { const cacheData: CacheData | undefined = productCache.get('allProducts'); if (!cacheData) { // Cache is unavailable; nothing to update return; }
const product = cacheData.allProducts.find(p => p.id === productId); if (!product) { // Product not found in cache; nothing to update return; } if (sizeId) { // Decrease quantity in the specific size const size = product.sizes.find(s => s.id === sizeId); if (size) { size.qty = size.qty - quantity; if (size.qty < 0) size.qty = 0; // Prevent negative stock } // Also, if color_id is present, update the associated color's size if (colorId) { const color = product.colors.find(c => c.id === colorId); if (color) { const colorSize = color.sizes.find(s => s.id === sizeId); if (colorSize) { colorSize.qty = colorSize.qty - quantity; if (colorSize.qty < 0) colorSize.qty = 0; } } } } else if (colorId) { // Decrease quantity in the specific color const color = product.colors.find(c => c.id === colorId); if (color) { color.qty = color.qty - quantity; if (color.qty < 0) color.qty = 0; // Prevent negative stock } } else { // Decrease quantity in the product itself product.qty = product.qty - quantity; if (product.qty < 0) product.qty = 0; // Prevent negative stock } // Update the cache productCache.set('allProducts', cacheData); console.log('Decreased product quantity in cache.');} catch (error) { console.error('Error decreasing product quantity in cache:', error); // Decide whether to flush the cache or handle the error differently productCache.del('allProducts'); } };
/**
-
Increases the 'sold' count for a size in the cache. */ export const increaseSizeSoldInCache = (productId: number, sizeId: number, quantity: number): void => { try { const cacheData: CacheData | undefined = productCache.get('allProducts'); if (!cacheData) { // Cache is unavailable; nothing to update return; }
const product = cacheData.allProducts.find(p => p.id === productId); if (!product) { // Product not found in cache; nothing to update return; } const size = product.sizes.find(s => s.id === sizeId); if (size) { size.sold += quantity; // Ensure cache is updated productCache.set('allProducts', cacheData); console.log(`Increased sold count for size ID ${sizeId} in cache.`); }} catch (error) { console.error('Error increasing size sold in cache:', error); // Optionally, decide to flush the cache or handle the error differently productCache.del('allProducts'); } };
/**
-
Increases the 'sold' count for a color in the cache. */ export const increaseColorSoldInCache = (productId: number, colorId: number, quantity: number): void => { try { const cacheData: CacheData | undefined = productCache.get('allProducts'); if (!cacheData) { // Cache is unavailable; nothing to update return; }
const product = cacheData.allProducts.find(p => p.id === productId); if (!product) { // Product not found in cache; nothing to update return; } const color = product.colors.find(c => c.id === colorId); if (color) { color.sold += quantity; // Ensure cache is updated productCache.set('allProducts', cacheData); console.log(`Increased sold count for color ID ${colorId} in cache.`); }} catch (error) { console.error('Error increasing color sold in cache:', error); // Optionally, decide to flush the cache or handle the error differently productCache.del('allProducts'); } };
/**
-
Increases the 'sold' count for a product in the cache. */ export const increaseProductSoldInCache = (productId: number, quantity: number): void => { try { const cacheData: CacheData | undefined = productCache.get('allProducts'); if (!cacheData) { // Cache is unavailable; nothing to update return; }
const product = cacheData.allProducts.find(p => p.id === productId); if (product) { product.sold += quantity; // Ensure cache is updated productCache.set('allProducts', cacheData); console.log(`Increased sold count for product ID ${productId} in cache.`); }} catch (error) { console.error('Error increasing product sold in cache:', error); // Optionally, decide to flush the cache or handle the error differently productCache.del('allProducts'); } }; server.ts: import express from 'express'; import morgan from 'morgan'; import dotenv from 'dotenv'; import cors from 'cors'; import compression from 'compression'; import { errorHandler } from './middleware/errorHandler'; import categoryRoutes from './routes/categoryRoutes'; import subcategoryRoutes from './routes/subcategoryRoutes'; import productRoutes from "./routes/productRoutes"; import userRoutes from "./routes/userRoutes"; import addressRoutes from "./routes/addressRoutes"; import couponRoutes from "./routes/couponRoutes"; import cartRoutes from "./routes/cartRoutes"; import wishlistRoutes from "./routes/wishlistRoutes"; import nocache from 'nocache'; import counterRoutes from "./routes/counterRoutes"; import imageOptimizationRoutes from "./routes/imageOptimizationRoutes";
dotenv.config();
const app = express();
app.use(cors()); app.use(compression()); app.use(nocache()); app.use(express.json({ limit: '500kb' })); app.use(morgan('dev'));
app.set('trust proxy', 1); app.use('/user', userRoutes); app.use('/categories', categoryRoutes); app.use('/subcategories', subcategoryRoutes); app.use('/products', productRoutes); app.use('/addresses', addressRoutes); app.use('/coupons', couponRoutes); app.use('/counter', counterRoutes); app.use('/cart', cartRoutes); app.use('/wishlist', wishlistRoutes); app.use('/images', imageOptimizationRoutes); app.use(errorHandler);
const PORT = 4000; app.listen(PORT, () => { console.log(
Server running on port ${PORT}); });export default app; cache.ts: // src/cache.ts import NodeCache from 'node-cache';
// Initialize cache with a TTL (Time To Live) of 1 hour export const productCache = new NodeCache({ stdTTL: 0, checkperiod: 600 }); export const brandsCache = new NodeCache({ stdTTL: 0, checkperiod: 600 });
export const flushProductCache = () => { productCache.del('allProducts'); };
-
- Assistant
You've provided an extensive
getProductsfunction along with related modules in your Express application. After reviewing your code, I've identified key areas where performance can be optimized and potential filtering mistakes can be corrected. Below, I outline these issues and provide an optimized version of yourgetProductsfunction to ensure it operates efficiently and returns accurate results.Identified Issues
-
Cache Key Misalignment:
- Issue: In your
loadAndCacheAllProductsfunction, you're setting the cache data with the key'allProducts', but in thegetProductsfunction, you're attempting to retrieve it using the key'cacheData'. - Impact: This discrepancy causes the cache to miss every time
getProductsis called, leading to unnecessary database queries and degrading performance.
- Issue: In your
-
Incorrect Price Filtering:
- Issue: The current price range filter in
getProductsonly considers the prices of sizes (product.sizes.map(size => size.price)) and ignores the base product price (product.price). - Impact: Products whose base price falls within the desired range but have sizes outside the range may be erroneously excluded from the results.
- Issue: The current price range filter in
-
Faulty Availability Filtering:
- Issue: For products that have both colors and sizes (
has_colorandhas_sizearetrue), the availability filter only checksproduct.sizes, which might be empty if sizes are nested within colors. - Impact: Such products may be incorrectly marked as out of stock even if they have sizes within colors that are in stock.
- Issue: For products that have both colors and sizes (
-
Potential Multiple Cache Keys Usage:
- Issue: There's inconsistent usage of cache keys (
'allProducts'vs.'cacheData') across different parts of the application, includingcacheHelper.ts. - Impact: This can lead to cache inconsistencies and unexpected cache misses.
- Issue: There's inconsistent usage of cache keys (
Optimizations and Corrections
Based on the issues identified, here are the steps to optimize and correct your
getProductsfunction:-
Align Cache Keys:
- Ensure that both setting and getting cache data use the same key (
'allProducts').
- Ensure that both setting and getting cache data use the same key (
-
Enhance Price Filtering:
- Include the base product price in the price range filter.
- Consider all relevant price points (product price, color price, size price) to accurately determine if a product falls within the specified price range.
-
Fix Availability Filtering:
- Modify the availability filter to account for products with nested sizes within colors.
- Ensure that for such products, the availability status is determined based on the sizes within each color.
-
Optimize Filtering Logic:
- Combine multiple filter conditions into a single pass to reduce the number of iterations over the product list.
- Apply the most restrictive filters first to minimize the dataset as early as possible.
-
Ensure Consistent Cache Usage Across Helpers:
- Update
cacheHelper.tsand other relevant modules to consistently use the'allProducts'key.
- Update
Optimized
getProductsFunctionBelow is the optimized version of your
getProductsfunction incorporating the corrections and optimizations discussed:import { Request, Response } from 'express'; import { query } from "../config/database"; import { brandsCache, productCache } from "../cache"; import { Brand, BrandsCacheData, CacheData, Color, Product, } from "product"; import { loadAndCacheAllBrands, loadAndCacheAllProducts } from "../cacheLoader"; // Assuming you separate caching logic import { addProductToCache, deleteProductFromCache, updateProductInCache } from "../cacheHelper"; export const getProducts = async (req: Request, res: Response): Promise<void> => { try { // Correct cache key usage let cacheData: CacheData | undefined = productCache.get('allProducts'); if (!cacheData) { // Cache miss: Load from DB and cache cacheData = await loadAndCacheAllProducts(); console.log('Loaded products from database and cached them.'); } else { console.log('Loaded products from cache.'); } const { allProducts } = cacheData; // Log total products before any filtering console.log(`Total products before filtering: ${allProducts.length}`); // Extract and sanitize query parameters const { search = '', c, sc, b, minPriceA = '0', maxPriceA = '', availability = 'all', sortBy = '', page = '1', itemsPerPage = '15', } = req.query; // Parse numerical values const pageNum = Math.max(parseInt(page as string, 10) || 1, 1); const itemsPerPageNum = Math.max(parseInt(itemsPerPage as string, 10) || 15, 1); const minPriceANum = parseFloat(minPriceA as string) || 0; let maxPriceANum = maxPriceA ? parseFloat(maxPriceA as string) : Infinity; // If maxPriceA is less than minPriceA, reset to Infinity if (!isNaN(maxPriceANum) && maxPriceANum < minPriceANum) { console.warn('Provided maxPriceA is less than minPriceA. Resetting maxPriceANum to Infinity.'); maxPriceANum = Infinity; } else if (isNaN(maxPriceANum)) { maxPriceANum = Infinity; } // Parse c, sc, b as numbers if present const cNum = c ? parseInt(c as string, 10) : undefined; const scNum = sc ? parseInt(sc as string, 10) : undefined; const bNum = b ? parseInt(b as string, 10) : undefined; // Initialize brand-related variables if 'b' is provided let allBrands: Brand[] = []; if (bNum !== undefined && !isNaN(bNum)) { // Retrieve cached brands let brandsCacheData: BrandsCacheData | undefined = brandsCache.get('allBrands'); if (!brandsCacheData) { // Cache miss: Load from DB and cache brandsCacheData = await loadAndCacheAllBrands(); console.log('Loaded brands from database and cached them.'); } else { console.log('Loaded brands from cache.'); } allBrands = brandsCacheData.allBrands; // Check if 'bNum' is a valid brand category ID const isValidBrandCategory = brandsCacheData.allBrands.some(brand => brand.category_id === bNum); if (!isValidBrandCategory) { console.warn(`Brand category ID ${bNum} is invalid.`); // Return zero products since 'b' is invalid res.json({ products: [], total: 0, page: pageNum, itemsPerPage: itemsPerPageNum, totalPages: 0, hasMore: false, minPrice: cacheData.minPrice, maxPrice: cacheData.maxPrice, }); return; } } // Start with all products let filteredProducts = allProducts; // Apply all filters in a single pass for performance filteredProducts = filteredProducts.filter(product => { // Flag to determine if the product passes all filters let isValid = true; // Apply Brand Filter if (bNum !== undefined && !isNaN(bNum)) { isValid = isValid && (product.brand_id === bNum); } // Apply Category Filter (c) if (cNum !== undefined && !isNaN(cNum)) { isValid = isValid && product.categories.some(cat => cat.main_category_id === cNum); } // Apply Subcategory Filter (sc) if (scNum !== undefined && !isNaN(scNum)) { isValid = isValid && (product.brand_id === scNum || product.categories.some(cat => cat.id === scNum)); } // Apply Search Filter if (search && typeof search === 'string') { const searchLower = search.toLowerCase(); const searchWords = searchLower.split(/\s+/).filter(Boolean); isValid = isValid && searchWords.every(word => product.name.toLowerCase().includes(word)); } // Apply Price Range Filter if (minPriceANum > 0 || maxPriceANum < Infinity) { // Collect all relevant prices let productPrices: number[] = [product.price]; if (product.has_color) { product.colors.forEach(color => { productPrices.push(color.price); if (color.has_size) { color.sizes.forEach(size => { productPrices.push(size.price); }); } }); } if (product.has_size && !product.has_color) { product.sizes.forEach(size => { productPrices.push(size.price); }); } const productMinPrice = Math.min(...productPrices); const productMaxPrice = Math.max(...productPrices); isValid = isValid && (productMinPrice >= minPriceANum) && (productMaxPrice <= maxPriceANum); } // Apply Availability Filter if (availability === 'in_stock') { if (product.has_color && product.has_size) { // Check any size within any color is in stock const inStock = product.colors.some(color => color.qty > 0 || (color.has_size && color.sizes.some(size => size.qty > 0)) ); isValid = isValid && inStock; } else if (product.has_size) { // Check any standalone size is in stock const inStock = product.sizes.some(size => size.qty > 0); isValid = isValid && inStock; } else { // Check product's own quantity isValid = isValid && (product.qty > 0); } } else if (availability === 'out_of_stock') { if (product.has_color && product.has_size) { // All sizes within all colors must be out of stock const outOfStock = product.colors.every(color => color.qty === 0 && (!color.has_size || color.sizes.every(size => size.qty === 0)) ); isValid = isValid && outOfStock; } else if (product.has_size) { // All standalone sizes must be out of stock const outOfStock = product.sizes.every(size => size.qty === 0); isValid = isValid && outOfStock; } else { // Check product's own quantity isValid = isValid && (product.qty === 0); } } return isValid; }); // Log total after filtering console.log(`Total products after filtering: ${filteredProducts.length}`); // Apply Sorting if (sortBy && typeof sortBy === 'string') { switch (sortBy) { case 'Price: Low to High': filteredProducts.sort((a, b) => { const aPrices = [a.price]; const bPrices = [b.price]; a.colors.forEach(color => { aPrices.push(color.price); if (color.has_size) { color.sizes.forEach(size => aPrices.push(size.price)); } }); if (a.has_size && !a.has_color) { a.sizes.forEach(size => aPrices.push(size.price)); } if (b.has_color) { b.colors.forEach(color => { bPrices.push(color.price); if (color.has_size) { color.sizes.forEach(size => bPrices.push(size.price)); } }); } if (b.has_size && !b.has_color) { b.sizes.forEach(size => bPrices.push(size.price)); } const aMin = Math.min(...aPrices); const bMin = Math.min(...bPrices); return aMin - bMin; }); break; case 'Price: High to Low': filteredProducts.sort((a, b) => { const aPrices = [a.price]; const bPrices = [b.price]; a.colors.forEach(color => { aPrices.push(color.price); if (color.has_size) { color.sizes.forEach(size => aPrices.push(size.price)); } }); if (a.has_size && !a.has_color) { a.sizes.forEach(size => aPrices.push(size.price)); } if (b.has_color) { b.colors.forEach(color => { bPrices.push(color.price); if (color.has_size) { bPrices.push(size.price); } }); } if (b.has_size && !b.has_color) { b.sizes.forEach(size => bPrices.push(size.price)); } const aMax = Math.max(...aPrices); const bMax = Math.max(...bPrices); return bMax - aMax; }); break; case 'Name: A to Z': filteredProducts.sort((a, b) => a.name.localeCompare(b.name)); break; case 'Name: Z to A': filteredProducts.sort((a, b) => b.name.localeCompare(a.name)); break; case 'Best Selling': filteredProducts.sort((a, b) => b.sold - a.sold); break; case 'Newest Arrivals': filteredProducts.sort((a, b) => new Date(b.created_at).getTime() - new Date(a.created_at).getTime()); break; default: // If sortBy parameter doesn't match any case, do not sort break; } } // Apply Pagination const totalFilteredProducts = filteredProducts.length; const totalPages = Math.ceil(totalFilteredProducts / itemsPerPageNum); const startIdx = (pageNum - 1) * itemsPerPageNum; const endIdx = startIdx + itemsPerPageNum; const paginatedProducts = filteredProducts.slice(startIdx, endIdx); // Construct Response const response = { products: paginatedProducts, total: totalFilteredProducts, page: pageNum, itemsPerPage: itemsPerPageNum, totalPages, hasMore: endIdx < totalFilteredProducts, minPrice: cacheData.minPrice, // Global minPrice across all products maxPrice: cacheData.maxPrice, // Global maxPrice across all products }; // Send Response res.json(response); } catch (error) { console.error('Error fetching products:', error); res.status(500).json({ error: 'Internal server error' }); } finally { // Optional: Any cleanup operations can be performed here // For example, monitoring memory usage or logging request completion } };Explanation of Optimizations and Corrections
-
Aligned Cache Keys:
- Changed
productCache.get('cacheData')toproductCache.get('allProducts')to match the cache key used inloadAndCacheAllProducts. - Before:
let cacheData: CacheData | undefined = productCache.get('cacheData'); - After:
let cacheData: CacheData | undefined = productCache.get('allProducts');
- Changed
-
Enhanced Price Filtering:
- Included
product.pricealong withcolor.priceandsize.priceto determine the minimum and maximum prices for each product. - Before:
const productPrices = product.sizes.map(size => size.price); - After:
let productPrices: number[] = [product.price]; if (product.has_color) { product.colors.forEach(color => { productPrices.push(color.price); if (color.has_size) { color.sizes.forEach(size => { productPrices.push(size.price); }); } }); } if (product.has_size && !product.has_color) { product.sizes.forEach(size => { productPrices.push(size.price); }); } const productMinPrice = Math.min(...productPrices); const productMaxPrice = Math.max(...productPrices);
- Included
-
Fixed Availability Filtering:
- Revised the availability filter to correctly handle products with both colors and sizes.
- Before:
if (availability === 'in_stock') { filteredProducts = filteredProducts.filter(product => product.sizes.some(size => size.qty > 0) ); } else if (availability === 'out_of_stock') { filteredProducts = filteredProducts.filter(product => product.sizes.every(size => size.qty === 0) ); } - After:
if (availability === 'in_stock') { if (product.has_color && product.has_size) { // Check any size within any color is in stock const inStock = product.colors.some(color => color.qty > 0 || (color.has_size && color.sizes.some(size => size.qty > 0)) ); isValid = isValid && inStock; } else if (product.has_size) { // Check any standalone size is in stock const inStock = product.sizes.some(size => size.qty > 0); isValid = isValid && inStock; } else { // Check product's own quantity isValid = isValid && (product.qty > 0); } } else if (availability === 'out_of_stock') { if (product.has_color && product.has_size) { // All sizes within all colors must be out of stock const outOfStock = product.colors.every(color => color.qty === 0 && (!color.has_size || color.sizes.every(size => size.qty === 0)) ); isValid = isValid && outOfStock; } else if (product.has_size) { // All standalone sizes must be out of stock const outOfStock = product.sizes.every(size => size.qty === 0); isValid = isValid && outOfStock; } else { // Check product's own quantity isValid = isValid && (product.qty === 0); } }
-
Combined Filtering for Performance:
- Applied all filter conditions within a single
.filteriteration to reduce the number of passes over the product list.
- Applied all filter conditions within a single
-
Input Validation Enhancements:
- Ensured
pageNumanditemsPerPageNumare at least1to prevent negative or zero pagination parameters.
- Ensured
-
Sorting Enhancements:
- Optimized sorting by pre-collecting all relevant prices for accurate comparison.
- Note: Ensure that
size.priceis correctly referenced within color loops.
-
Consistent Handling of Catalog Filters:
- Confirmed that category (
c) and subcategory (sc) filters are applied correctly based on their IDs.
- Confirmed that category (
Additional Recommendations
-
Separate Caching Logic:
- Consider separating caching logic into dedicated modules (
cacheLoader.ts) to keep controller functions clean and focused.
- Consider separating caching logic into dedicated modules (
-
Limit Cache Size:
- If the number of products is large, consider implementing pagination at the database level or using more efficient caching strategies to avoid memory bloat.
-
Use Indexes in Database:
- Ensure that your database has appropriate indexes on frequently filtered fields (
brand_id,category_id,price, etc.) to speed up data retrieval during caching.
- Ensure that your database has appropriate indexes on frequently filtered fields (
-
Implement Cache Invalidation Strategy:
- Ensure that any updates, deletions, or creations of products correctly invalidate or update the cache to prevent stale data.
-
Error Handling Enhancements:
- Provide more granular error messages or statuses based on the type of failure to aid in debugging and user feedback.
-
Logging Enhancements:
- Utilize more descriptive logging (possibly with structured logs) to better trace and monitor application behavior in production environments.
Corrected Cache Helper Function
Ensure that your
cacheHelper.tsconsistently uses the'allProducts'key. Here's how you can adjust theaddProductToCachefunction:// src/cacheHelper.ts import { productCache } from "./cache"; import { Product, CacheData } from "product"; /** * Adds a new product to the cache. */ export const addProductToCache = (newProduct: Product) => { try { const cacheData: CacheData | undefined = productCache.get('allProducts'); if (cacheData) { cacheData.allProducts.push(newProduct); // Extract all prices const productPrices: number[] = []; productPrices.push(newProduct.price); newProduct.colors.forEach((color) => { productPrices.push(color.price); color.sizes.forEach((size) => productPrices.push(size.price)); }); newProduct.sizes.forEach((size) => productPrices.push(size.price)); // Update minPrice and maxPrice if (productPrices.length > 0) { const productMinPrice = Math.min(...productPrices, cacheData.minPrice); const productMaxPrice = Math.max(...productPrices, cacheData.maxPrice); cacheData.minPrice = Math.min(cacheData.minPrice, productMinPrice); cacheData.maxPrice = Math.max(cacheData.maxPrice, productMaxPrice); } productCache.set('allProducts', cacheData); console.log('Added new product to cache.'); } else { // Flush cache to force reload on next access productCache.del('allProducts'); console.log('Cache unavailable. Flushed product cache.'); } } catch (error) { console.error('Error adding product to cache:', error); productCache.del('allProducts'); } };Ensure that all cache interactions related to products use the
'allProducts'key.Conclusion
By addressing the cache key misalignment, enhancing the price and availability filters, and optimizing the filtering logic, the
getProductsfunction should perform more efficiently and return accurate results. Additionally, maintaining consistency in cache management across your application will prevent future performance bottlenecks and ensure data integrity.Feel free to implement these changes and monitor the performance improvements. If your product catalog scales significantly, consider moving the filtering logic to the database or adopting more advanced caching strategies to maintain optimal performance.
-