I want to alter products, sizes and colors tables to add a new column "sold" give me the…
- User
I want to alter products, sizes and colors tables to add a new column "sold" give me the postgresql query to alter, also merge it with the backend... import { Request, Response } from 'express'; import {query} from "../config/database";
export const getProducts = async (req: Request, res: Response) => { try { const page = parseInt(req.query.page as string) || 1; const limit = 10; const offset = (page - 1) * limit; const search = (req.query.search as string || '').toLowerCase(); const categoryId = req.query.c as string; const subcategoryId = req.query.sc as string; const brandId = req.query.b as string;
const conditions = []; const queryParams: any[] = []; if (search) { const searchWords = search.split(/\s+/).filter(Boolean); const searchConditions = searchWords.map((word) => { queryParams.push(`${word}%`, `%${word}%`); return `(LOWER(p.name) LIKE $${queryParams.length - 1} OR LOWER(p.name) LIKE $${queryParams.length})`; }); conditions.push(`(${searchConditions.join(' OR ')})`); } if (categoryId) { queryParams.push(categoryId); conditions.push(`(c.id = $${queryParams.length} OR EXISTS ( SELECT 1 FROM subcategories sub WHERE sub.category_id = $${queryParams.length} AND (sub.id = sc.id OR sub.id = p.brand_id) ))`); } if (subcategoryId) { queryParams.push(subcategoryId); conditions.push(`(sc.id = $${queryParams.length} OR p.brand_id = $${queryParams.length})`); } if (brandId) { queryParams.push(brandId); conditions.push(`p.brand_id = $${queryParams.length}`); } const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : ''; // Add search, limit, and offset to queryParams queryParams.push(search, limit, offset); const productQuery = ` WITH filtered_products AS ( SELECT p.*, sc.id AS subcategory_id, sc.name AS subcategory_name, c.id AS main_category_id, c.name AS main_category_name, COUNT(*) OVER() AS total_count 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 ${whereClause} ORDER BY CASE WHEN $${queryParams.length - 2} != '' THEN CASE WHEN LOWER(p.name) LIKE LOWER($${queryParams.length - 2} || '%') THEN 1 WHEN LOWER(p.name) LIKE '%' || LOWER($${queryParams.length - 2}) || '%' THEN 2 ELSE 3 END ELSE 0 END, p.created_at DESC ) SELECT fp.*, (SELECT jsonb_agg(jsonb_build_object( 'id', c.id, 'name', c.name, 'price', c.price, 'qty', c.qty, 'images', (SELECT jsonb_agg(i.url) FROM images i WHERE i.color_id = c.id), 'sizes', (SELECT jsonb_agg(jsonb_build_object( 'id', s.id, 'name', s.name, 'price', s.price, 'qty', s.qty, 'images', (SELECT jsonb_agg(i.url) FROM images i WHERE i.size_id = s.id) )) FROM sizes s WHERE s.color_id = c.id) )) FROM colors c WHERE c.product_id = fp.id) AS colors, (SELECT jsonb_agg(jsonb_build_object( 'id', s.id, 'name', s.name, 'price', s.price, 'qty', s.qty, 'color_id', s.color_id, 'images', (SELECT jsonb_agg(i.url) FROM images i WHERE i.size_id = s.id) )) FROM sizes s WHERE s.product_id = fp.id AND s.color_id IS NULL) AS sizes, (SELECT jsonb_agg(i.url) FROM images i WHERE i.product_id = fp.id AND i.color_id IS NULL AND i.size_id IS NULL) AS images FROM filtered_products fp LIMIT $${queryParams.length - 1}::integer OFFSET $${queryParams.length}::integer `; const productResult = await query(productQuery, queryParams); const totalProducts = parseInt(productResult.rows[0]?.total_count || '0'); const totalPages = Math.ceil(totalProducts / limit); const products = productResult.rows.map(product => ({ id: product.id, name: product.name, description: product.description, brand_id: product.brand_id, has_color: product.has_color, has_size: product.has_size, product_type: product.product_type, price: product.price, qty: product.qty, created_at: product.created_at, updated_at: product.updated_at, categories: [ { id: product.subcategory_id, name: product.subcategory_name, main_category_id: product.main_category_id, main_category_name: product.main_category_name } ], colors: product.colors || [], sizes: product.sizes || [], images: [ ...(product.images || []), ...(product.colors || []).flatMap((c: { images: any; }) => c.images || []), ...(product.sizes || []).flatMap((s: { images: any; }) => s.images || []) ] })); const result = { products, pagination: { currentPage: page, totalPages, totalProducts, limit } }; res.json(result); } catch (error) { console.error('Error fetching products:', error); res.status(500).json({ error: 'Internal server error' }); }};
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.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, s.id AS size_id, s.name AS size_name, s.price AS size_price, s.qty AS size_qty, 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 // @ts-ignore const 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, created_at: result.rows[0].created_at, updated_at: result.rows[0].updated_at, // @ts-ignore categories: [], // @ts-ignore colors: [], // @ts-ignore sizes: [], // @ts-ignore 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, 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, 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); } } } // Add image to the product, and to the appropriate color or size 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 { await query('BEGIN');
const productData = req.body; if (productData.price < 1000) { productData.price *= 1000; } const productResult = await query( 'INSERT INTO products (name, description, brand_id, has_color, has_size, product_type, price, qty) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING id', [productData.name, productData.description, productData.brand_id, productData.hasColor, productData.hasSize, productData.product_type, productData.price, productData.qty] ); const productId = productResult.rows[0].id; // Insert category associations for (const categoryId of productData.category_ids) { await query( 'INSERT INTO product_categories (product_id, category_id) VALUES ($1, $2)', [productId, categoryId] ); } // Insert images for (const imageUrl of productData.images) { await query( 'INSERT INTO images (product_id, url) VALUES ($1, $2)', [productId, imageUrl] ); } // Handle colors and sizes based on product type if (productData.hasColor) { for (const color of productData.colors) { // Adjust color price if less than 1000 if (color.price < 1000) { color.price *= 1000; } const colorResult = await query( 'INSERT INTO colors (product_id, name, has_size, price, qty) VALUES ($1, $2, $3, $4, $5) RETURNING id', [productId, color.name, color.hasSize, color.price, color.qty] ); const colorId = colorResult.rows[0].id; // Insert color images for (const imageUrl of color.images) { await query( 'INSERT INTO images (product_id, color_id, url) VALUES ($1, $2, $3)', [productId, colorId, imageUrl] ); } if (color.hasSize) { for (const size of color.sizes) { // Adjust size price if less than 1000 if (size.price < 1000) { size.price *= 1000; } const sizeResult = await query( 'INSERT INTO sizes (product_id, color_id, name, price, qty) VALUES ($1, $2, $3, $4, $5) RETURNING id', [productId, colorId, size.name, size.price, size.qty] ); const sizeId = sizeResult.rows[0].id; // Insert size images for (const imageUrl of size.images) { await query( 'INSERT INTO images (product_id, color_id, size_id, url) VALUES ($1, $2, $3, $4)', [productId, colorId, sizeId, imageUrl] ); } } } } } else if (productData.hasSize) { for (const size of productData.sizes) { // Adjust size price if less than 1000 if (size.price < 1000) { size.price *= 1000; } const sizeResult = await query( 'INSERT INTO sizes (product_id, name, price, qty) VALUES ($1, $2, $3, $4) RETURNING id', [productId, size.name, size.price, size.qty] ); const sizeId = sizeResult.rows[0].id; // Insert size images for (const imageUrl of size.images) { await query( 'INSERT INTO images (product_id, size_id, url) VALUES ($1, $2, $3)', [productId, sizeId, imageUrl] ); } } } await query('COMMIT'); res.status(201).json({ message: 'Product created successfully', productId }); } catch (error) { await query('ROLLBACK'); console.error('Error creating product:', error); res.status(500).json({ error: 'Internal server error' }); } finally { }};
// Update a product export const updateProduct = async (req: Request, res: Response) => { const { id } = req.params; const productData = req.body;
try { await query('BEGIN'); // Adjust main product price if less than 1000 if (productData.price < 1000) { productData.price *= 1000; } // Update main product details 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.hasColor, productData.hasSize, productData.product_type, productData.price, productData.qty, id] ); if (result.rows.length === 0) { await query('ROLLBACK'); return res.status(404).json({ error: 'Product not found' }); } // Delete existing associations and data await query('DELETE FROM product_categories WHERE product_id = $1', [id]); await query('DELETE FROM images WHERE product_id = $1', [id]); // Get existing colors and sizes const existingColors = await query('SELECT id FROM colors WHERE product_id = $1', [id]); const existingColorIds = new Set(existingColors.rows.map(row => row.id)); const existingSizes = await query('SELECT id FROM sizes WHERE product_id = $1', [id]); const existingSizeIds = new Set(existingSizes.rows.map(row => row.id)); // Determine colors and sizes to delete const updatedColorIds = new Set(productData.colors.map((color: { id: any; }) => color.id).filter((id: any) => id !== undefined)); const colorsToDelete = [...existingColorIds].filter(id => !updatedColorIds.has(id)); const updatedSizeIds = new Set([ ...productData.sizes.map((size: { id: any; }) => size.id).filter((id: any) => id !== undefined), ...productData.colors.flatMap((color: { sizes: any[]; }) => color.sizes.map(size => size.id).filter(id => id !== undefined)) ]); const sizesToDelete = [...existingSizeIds].filter(id => !updatedSizeIds.has(id)); // Handle cart conflicts by deleting cart items associated with sizes and colors to be deleted if (sizesToDelete.length > 0) { await query('DELETE FROM cart_items WHERE size_id = ANY ($1)', [sizesToDelete]); } if (colorsToDelete.length > 0) { await query('DELETE FROM cart_items WHERE color_id = ANY ($1)', [colorsToDelete]); } // Delete colors and associated sizes for (const colorId of colorsToDelete) { await query('DELETE FROM sizes WHERE color_id = $1', [colorId]); await query('DELETE FROM images WHERE color_id = $1', [colorId]); await query('DELETE FROM colors WHERE id = $1', [colorId]); } // Delete sizes for (const sizeId of sizesToDelete) { await query('DELETE FROM images WHERE size_id = $1', [sizeId]); await query('DELETE FROM sizes WHERE id = $1', [sizeId]); } // Insert category associations for (const categoryId of productData.category_ids) { await query( 'INSERT INTO product_categories (product_id, category_id) VALUES ($1, $2)', [id, categoryId] ); } // Insert images for (const imageUrl of productData.images) { await query( 'INSERT INTO images (product_id, url) VALUES ($1, $2)', [id, imageUrl] ); } // Handle colors and sizes based on product type if (productData.hasColor) { for (const color of productData.colors) { // Adjust color price if less than 1000 if (color.price < 1000) { color.price *= 1000; } let colorId; if (color.id) { // Update existing color await query( 'UPDATE colors SET name = $1, has_size = $2, price = $3, qty = $4 WHERE id = $5', [color.name, color.hasSize, color.price, color.qty, color.id] ); colorId = color.id; } else { // Insert new color const colorResult = await query( 'INSERT INTO colors (product_id, name, has_size, price, qty) VALUES ($1, $2, $3, $4, $5) RETURNING id', [id, color.name, color.hasSize, color.price, color.qty] ); colorId = colorResult.rows[0].id; } if (color.images && color.images.length > 0) { for (const imageUrl of color.images) { await query( 'INSERT INTO images (product_id, color_id, url) VALUES ($1, $2, $3)', [id, colorId, imageUrl] ); } } if (color.hasSize) { for (const size of color.sizes) { // Adjust size price if less than 1000 if (size.price < 1000) { size.price *= 1000; } let sizeId; if (size.id) { // Update existing size await query( 'UPDATE sizes SET name = $1, price = $2, qty = $3 WHERE id = $4', [size.name, size.price, size.qty, size.id] ); sizeId = size.id; } else { // Insert new size const sizeResult = await query( 'INSERT INTO sizes (product_id, color_id, name, price, qty) VALUES ($1, $2, $3, $4, $5) RETURNING id', [id, colorId, size.name, size.price, size.qty] ); sizeId = sizeResult.rows[0].id; } // Insert size images if (size.images && size.images.length > 0) { for (const imageUrl of size.images) { await query( 'INSERT INTO images (product_id, color_id, size_id, url) VALUES ($1, $2, $3, $4)', [id, colorId, sizeId, imageUrl] ); } } } } } } else if (productData.hasSize) { for (const size of productData.sizes) { // Adjust size price if less than 1000 if (size.price < 1000) { size.price *= 1000; } let sizeId; if (size.id) { // Update existing size await query( 'UPDATE sizes SET name = $1, price = $2, qty = $3 WHERE id = $4', [size.name, size.price, size.qty, size.id] ); sizeId = size.id; } else { // Insert new size const sizeResult = await query( 'INSERT INTO sizes (product_id, name, price, qty) VALUES ($1, $2, $3, $4) RETURNING id', [id, size.name, size.price, size.qty] ); sizeId = sizeResult.rows[0].id; } // Insert size images if (size.images && size.images.length > 0) { for (const imageUrl of size.images) { await query( 'INSERT INTO images (product_id, size_id, url) VALUES ($1, $2, $3)', [id, sizeId, imageUrl] ); } } } } await query('COMMIT'); res.json({ message: 'Product updated successfully', product: result.rows[0] }); } catch (error) { await query('ROLLBACK'); console.error('Error updating product:', error); res.status(500).json({ error: 'Internal server error' }); }}; export const deleteProduct = async (req: Request, res: Response) => { const { id } = req.params; try { await query('BEGIN');
// Delete cart items associated with this product await query('DELETE FROM cart_items WHERE product_id = $1', [id]); // Delete related data first await query('DELETE FROM product_categories WHERE product_id = $1', [id]); await query('DELETE FROM images WHERE product_id = $1', [id]); await query('DELETE FROM sizes WHERE product_id = $1', [id]); await query('DELETE FROM colors WHERE product_id = $1', [id]); // Delete the product const result = await query('DELETE FROM products WHERE id = $1 RETURNING *', [id]); if (result.rows.length === 0) { await query('ROLLBACK'); return res.status(404).json({ error: 'Product not found' }); } 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' }); }};
- Assistant
To add a new column
"sold"to theproducts,sizes, andcolorstables in your PostgreSQL database and integrate this change into your Express backend, follow the steps outlined below. This guide assumes that the"sold"column is aBOOLEANindicating whether an item has been sold (TRUE) or not (FALSE). If you require a different data type (e.g., an integer to count the number of times sold), you can adjust the queries accordingly.
1. Updating the Database Schema
First, you'll need to alter the existing tables to add the new
"sold"column. You can execute the following SQL commands using a PostgreSQL client likepsql,pgAdmin, or any other database management tool you prefer.SQL Queries to Alter Tables
-- Add 'sold' column to the products table ALTER TABLE products ADD COLUMN sold BOOLEAN NOT NULL DEFAULT FALSE; -- Add 'sold' column to the sizes table ALTER TABLE sizes ADD COLUMN sold BOOLEAN NOT NULL DEFAULT FALSE; -- Add 'sold' column to the colors table ALTER TABLE colors ADD COLUMN sold BOOLEAN NOT NULL DEFAULT FALSE;Explanation:
ALTER TABLE: This command modifies the structure of an existing table.ADD COLUMN: Adds a new column to the table.BOOLEAN: The data type for the new column, suitable forTRUEorFALSEvalues.NOT NULL: Ensures that the column cannot haveNULLvalues.DEFAULT FALSE: Sets the default value of the column toFALSEif not specified during insertion.
2. Integrating the New Column into the Backend
With the database schema updated, the next step is to modify your backend code to handle the
"sold"column appropriately. This involves updating your CRUD (Create, Read, Update, Delete) operations to include the new field.a. Updating the
getProductsFunctionEnsure that the
"sold"column is retrieved and included in the API response.Updated SQL Query in
getProducts:const productQuery = ` WITH filtered_products AS ( SELECT p.*, sc.id AS subcategory_id, sc.name AS subcategory_name, c.id AS main_category_id, c.name AS main_category_name, p.sold, -- Include 'sold' from products COUNT(*) OVER() AS total_count 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 ${whereClause} ORDER BY CASE WHEN $${queryParams.length - 2} != '' THEN CASE WHEN LOWER(p.name) LIKE LOWER($${queryParams.length - 2} || '%') THEN 1 WHEN LOWER(p.name) LIKE '%' || LOWER($${queryParams.length - 2}) || '%' THEN 2 ELSE 3 END ELSE 0 END, p.created_at DESC ) SELECT fp.*, -- Other JSON aggregations FROM filtered_products fp LIMIT $${queryParams.length - 1}::integer OFFSET $${queryParams.length}::integer `;Including
"sold"in the Response:const products = productResult.rows.map(product => ({ id: product.id, name: product.name, description: product.description, brand_id: product.brand_id, has_color: product.has_color, has_size: product.has_size, product_type: product.product_type, price: product.price, qty: product.qty, sold: product.sold, // Include 'sold' field created_at: product.created_at, updated_at: product.updated_at, // ... rest of your mappings }));b. Updating the
getProductByIdFunctionEnsure that the
"sold"column is retrieved for a single product, including its colors and sizes.Updated SQL Query in
getProductById: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, -- Include 'sold' from products 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, -- Include 'sold' from colors 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, -- Include 'sold' from sizes 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 `;Including
"sold"in the Response:// Initialize the product object with 'sold' const 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, // Include 'sold' created_at: result.rows[0].created_at, updated_at: result.rows[0].updated_at, categories: [], colors: [], sizes: [], images: [] }; // Populate colors with 'sold' 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, // Include 'sold' images: [], sizes: [] }); } // Populate sizes with 'sold' 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, // Include 'sold' color_id: row.color_id, images: [] }; product.sizes.push(newSize); }c. Updating the
createProductFunctionAllow setting the
"sold"field when creating new products, colors, and sizes.Updated Insert Statements:
// Insert into products with 'sold' const productResult = await query( 'INSERT INTO products (name, description, brand_id, has_color, has_size, product_type, price, qty, sold) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING id', [productData.name, productData.description, productData.brand_id, productData.hasColor, productData.hasSize, productData.product_type, productData.price, productData.qty, productData.sold || false] ); // Insert into colors with 'sold' const colorResult = await query( 'INSERT INTO colors (product_id, name, has_size, price, qty, sold) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id', [productId, color.name, color.hasSize, color.price, color.qty, color.sold || false] ); // Insert into sizes with 'sold' const sizeResult = await query( 'INSERT INTO sizes (product_id, color_id, name, price, qty, sold) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id', [productId, colorId, size.name, size.price, size.qty, size.sold || false] );Explanation:
productData.sold || false: Ensures that if the"sold"field is not provided, it defaults tofalse.
d. Updating the
updateProductFunctionAllow updating the
"sold"field when modifying existing products, colors, and sizes.Updated Update Statements:
// Update products with 'sold' 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, sold = $9, updated_at = CURRENT_TIMESTAMP WHERE id = $10 RETURNING *', [productData.name, productData.description, productData.brand_id, productData.hasColor, productData.hasSize, productData.product_type, productData.price, productData.qty, productData.sold || false, id] ); // Update existing colors with 'sold' await query( 'UPDATE colors SET name = $1, has_size = $2, price = $3, qty = $4, sold = $5 WHERE id = $6', [color.name, color.hasSize, color.price, color.qty, color.sold || false, color.id] ); // Update existing sizes with 'sold' await query( 'UPDATE sizes SET name = $1, price = $2, qty = $3, sold = $4 WHERE id = $5', [size.name, size.price, size.qty, size.sold || false, size.id] );e. Ensuring API Requests Include
soldWhen creating or updating products through your API, ensure that the request payload includes the
"sold"field where appropriate. Here's an example of what your request body might look like:Example Request Body for Creating a Product:
{ "name": "Sample Product", "description": "A detailed description of the sample product.", "brand_id": "brand123", "hasColor": true, "hasSize": true, "product_type": "Type A", "price": 1500, "qty": 100, "sold": false, "category_ids": ["cat1", "cat2"], "images": ["http://example.com/image1.jpg", "http://example.com/image2.jpg"], "colors": [ { "name": "Red", "hasSize": true, "price": 200, "qty": 50, "sold": false, "images": ["http://example.com/red1.jpg", "http://example.com/red2.jpg"], "sizes": [ { "name": "Small", "price": 50, "qty": 25, "sold": false, "images": ["http://example.com/small1.jpg"] }, // Additional sizes... ] }, // Additional colors... ] }
3. Testing the Changes
After making the above changes, it's crucial to test your application to ensure everything works as expected.
a. Database Migration
-
Backup Your Database: Before making any schema changes, it's good practice to back up your current database.
-
Run the SQL Scripts: Execute the provided
ALTER TABLEcommands to add the new"sold"columns to the respective tables. -
Verify Schema Changes: Use a tool like
pgAdminorpsqlto confirm that the new columns have been added successfully.
b. Backend Validation
-
Restart Your Server: After updating the backend code, restart your Node.js server to apply the changes.
-
API Testing:
- Create Product: Send a POST request to create a new product, including the
"sold"field. Verify that the product is created with the correct"sold"status. - Retrieve Products: Send a GET request to retrieve products and ensure that the
"sold"field is present and accurate in the response. - Update Product: Send a PUT/PATCH request to update a product's
"sold"status and verify the change. - Edge Cases: Test scenarios where the
"sold"field is omitted to ensure it defaults tofalse.
- Create Product: Send a POST request to create a new product, including the
-
Error Handling: Ensure that your API gracefully handles invalid data related to the
"sold"field, such as non-boolean values.
c. Client-Side Adjustments
If your frontend or any client consuming this API interacts with the
"sold"field, ensure that it can handle the new data accordingly. Update UI elements, forms, and validations as necessary.
4. Additional Considerations
a. Business Logic for
"sold"StatusDepending on your application's requirements, you might want to implement additional business logic related to the
"sold"status. For example:-
Automatic Updates: Automatically set
"sold"totruewhen theqtyreaches0. -
Sales Tracking: If you plan to track the number of times a product, color, or size is sold, consider using an
INTEGERtype instead ofBOOLEAN.
b. Indexing the
"sold"ColumnIf you frequently query products based on their
"sold"status, consider adding indexes to optimize these queries.Example:
-- Create index on products.sold CREATE INDEX idx_products_sold ON products(sold); -- Create index on sizes.sold CREATE INDEX idx_sizes_sold ON sizes(sold); -- Create index on colors.sold CREATE INDEX idx_colors_sold ON colors(sold);c. Documentation
Update your API documentation to include information about the new
"sold"field, specifying its data type, default value, and how it should be used.
5. Comprehensive Example: Updated Backend Functions
Below is a comprehensive example showcasing how to integrate the
"sold"column into your existing backend functions. This includes modifications tocreateProduct,getProducts,getProductById, andupdateProduct.a.
createProductFunction// Create a new product export const createProduct = async (req: Request, res: Response) => { try { await query('BEGIN'); const productData = req.body; if (productData.price < 1000) { productData.price *= 1000; } const productResult = await query( `INSERT INTO products (name, description, brand_id, has_color, has_size, product_type, price, qty, sold) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING id`, [ productData.name, productData.description, productData.brand_id, productData.hasColor, productData.hasSize, productData.product_type, productData.price, productData.qty, productData.sold || false // Set 'sold' field ] ); const productId = productResult.rows[0].id; // Insert category associations for (const categoryId of productData.category_ids) { await query( `INSERT INTO product_categories (product_id, category_id) VALUES ($1, $2)`, [productId, categoryId] ); } // Insert product images for (const imageUrl of productData.images) { await query( `INSERT INTO images (product_id, url) VALUES ($1, $2)`, [productId, imageUrl] ); } // Handle colors and sizes if (productData.hasColor) { for (const color of productData.colors) { // Adjust color price if less than 1000 if (color.price < 1000) { color.price *= 1000; } const colorResult = await query( `INSERT INTO colors (product_id, name, has_size, price, qty, sold) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id`, [ productId, color.name, color.hasSize, color.price, color.qty, color.sold || false // Set 'sold' field ] ); const colorId = colorResult.rows[0].id; // Insert color images for (const imageUrl of color.images) { await query( `INSERT INTO images (product_id, color_id, url) VALUES ($1, $2, $3)`, [productId, colorId, imageUrl] ); } // Handle sizes within colors if (color.hasSize) { for (const size of color.sizes) { // Adjust size price if less than 1000 if (size.price < 1000) { size.price *= 1000; } const sizeResult = await query( `INSERT INTO sizes (product_id, color_id, name, price, qty, sold) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id`, [ productId, colorId, size.name, size.price, size.qty, size.sold || false // Set 'sold' field ] ); const sizeId = sizeResult.rows[0].id; // Insert size images for (const imageUrl of size.images) { await query( `INSERT INTO images (product_id, color_id, size_id, url) VALUES ($1, $2, $3, $4)`, [productId, colorId, sizeId, imageUrl] ); } } } } } else if (productData.hasSize) { for (const size of productData.sizes) { // Adjust size price if less than 1000 if (size.price < 1000) { size.price *= 1000; } const sizeResult = await query( `INSERT INTO sizes (product_id, name, price, qty, sold) VALUES ($1, $2, $3, $4, $5) RETURNING id`, [ productId, size.name, size.price, size.qty, size.sold || false // Set 'sold' field ] ); const sizeId = sizeResult.rows[0].id; // Insert size images for (const imageUrl of size.images) { await query( `INSERT INTO images (product_id, size_id, url) VALUES ($1, $2, $3)`, [productId, sizeId, imageUrl] ); } } } await query('COMMIT'); res.status(201).json({ message: 'Product created successfully', productId }); } catch (error) { await query('ROLLBACK'); console.error('Error creating product:', error); res.status(500).json({ error: 'Internal server error' }); } };b.
updateProductFunction// Update a product export const updateProduct = async (req: Request, res: Response) => { const { id } = req.params; const productData = req.body; try { await query('BEGIN'); // Adjust main product price if less than 1000 if (productData.price < 1000) { productData.price *= 1000; } // Update main product details including 'sold' 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, sold = $9, updated_at = CURRENT_TIMESTAMP WHERE id = $10 RETURNING *`, [ productData.name, productData.description, productData.brand_id, productData.hasColor, productData.hasSize, productData.product_type, productData.price, productData.qty, productData.sold || false, // Update 'sold' field id ] ); if (result.rows.length === 0) { await query('ROLLBACK'); return res.status(404).json({ error: 'Product not found' }); } // Delete existing associations and data await query('DELETE FROM product_categories WHERE product_id = $1', [id]); await query('DELETE FROM images WHERE product_id = $1', [id]); // Handle colors and sizes similarly to createProduct // ... (The rest of your update logic remains similar, ensuring 'sold' is handled) // Example for updating colors: if (productData.hasColor) { for (const color of productData.colors) { // Adjust color price if less than 1000 if (color.price < 1000) { color.price *= 1000; } let colorId; if (color.id) { // Update existing color with 'sold' await query( `UPDATE colors SET name = $1, has_size = $2, price = $3, qty = $4, sold = $5 WHERE id = $6`, [color.name, color.hasSize, color.price, color.qty, color.sold || false, color.id] ); colorId = color.id; } else { // Insert new color with 'sold' const colorResult = await query( `INSERT INTO colors (product_id, name, has_size, price, qty, sold) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id`, [id, color.name, color.hasSize, color.price, color.qty, color.sold || false] ); colorId = colorResult.rows[0].id; } // Insert color images for (const imageUrl of color.images) { await query( `INSERT INTO images (product_id, color_id, url) VALUES ($1, $2, $3)`, [id, colorId, imageUrl] ); } // Handle sizes within colors if (color.hasSize) { for (const size of color.sizes) { // Adjust size price if less than 1000 if (size.price < 1000) { size.price *= 1000; } let sizeId; if (size.id) { // Update existing size with 'sold' await query( `UPDATE sizes SET name = $1, price = $2, qty = $3, sold = $4 WHERE id = $5`, [size.name, size.price, size.qty, size.sold || false, size.id] ); sizeId = size.id; } else { // Insert new size with 'sold' const sizeResult = await query( `INSERT INTO sizes (product_id, color_id, name, price, qty, sold) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id`, [id, colorId, size.name, size.price, size.qty, size.sold || false] ); sizeId = sizeResult.rows[0].id; } // Insert size images for (const imageUrl of size.images) { await query( `INSERT INTO images (product_id, color_id, size_id, url) VALUES ($1, $2, $3, $4)`, [id, colorId, sizeId, imageUrl] ); } } } } } // Handle sizes if only sizes are present // ... (Similar logic for sizes as shown above) await query('COMMIT'); res.json({ message: 'Product updated successfully', product: result.rows[0] }); } catch (error) { await query('ROLLBACK'); console.error('Error updating product:', error); res.status(500).json({ error: 'Internal server error' }); } };
6. Summary
By following the steps outlined above, you've successfully:
-
Updated the Database Schema: Added a new
"sold"column to theproducts,sizes, andcolorstables with a default value ofFALSE. -
Integrated the New Column into the Backend:
- Created Products: Enabled setting the
"sold"status when creating new products, colors, and sizes. - Retrieved Products: Modified retrieval functions to include the
"sold"status in API responses. - Updated Products: Allowed updating the
"sold"status for existing products, colors, and sizes.
- Created Products: Enabled setting the
-
Tested the Changes: Emphasized the importance of thorough testing to ensure the new functionality works as intended.
-
Considered Additional Enhancements:
- Indexing: For improved query performance based on the
"sold"status. - Business Logic: Potential automatic updates to the
"sold"status based on inventory levels or sales.
- Indexing: For improved query performance based on the
By implementing these changes, your application will now be able to track the sold status of products, colors, and sizes effectively, enhancing both your data management and business insights.
If you encounter any issues or need further assistance with specific parts of the implementation, feel free to ask!