Understanding Search Filters in SQL and Web Development

Understanding Search Filters

Think of search filters like sifting through a library. Instead of looking through every book, you might want to narrow down your search by:

Genre (Fiction, Non-fiction, etc.)
Publication Year
Author
Language

In web applications, these filters help users find exactly what they're looking for without manually scanning through all available items.

Basic Filter Implementation

Let's start with a simple e-commerce product filter system:


// models/Product.js
const Product = sequelize.define('Product', {
    name: DataTypes.STRING,
    price: DataTypes.DECIMAL,
    category: DataTypes.STRING,
    color: DataTypes.STRING,
    size: DataTypes.STRING
});

// Basic filter implementation
const filterProducts = async (filters) => {
    const whereClause = {};
    
    if (filters.category) {
        whereClause.category = filters.category;
    }
    
    if (filters.color) {
        whereClause.color = filters.color;
    }
    
    if (filters.size) {
        whereClause.size = filters.size;
    }
    
    if (filters.minPrice) {
        whereClause.price = {
            [Op.gte]: filters.minPrice
        };
    }
    
    return await Product.findAll({
        where: whereClause
    });
};

// Express route implementation
app.get('/api/products', async (req, res) => {
    try {
        const products = await filterProducts(req.query);
        res.json(products);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

Advanced Search and Filtering

Let's build a more sophisticated search system with multiple filter types:


// Advanced product search with multiple filter types
const searchProducts = async ({
    search = '',
    category = null,
    minPrice = null,
    maxPrice = null,
    colors = [],
    sizes = [],
    brands = [],
    inStock = null,
    sortBy = 'name',
    sortOrder = 'ASC',
    page = 1,
    limit = 10
}) => {
    // Build where clause
    const whereClause = {};
    
    // Text search
    if (search) {
        whereClause[Op.or] = [
            { name: { [Op.iLike]: `%${search}%` } },
            { description: { [Op.iLike]: `%${search}%` } }
        ];
    }
    
    // Category filter
    if (category) {
        whereClause.category = category;
    }
    
    // Price range
    if (minPrice || maxPrice) {
        whereClause.price = {};
        if (minPrice) whereClause.price[Op.gte] = minPrice;
        if (maxPrice) whereClause.price[Op.lte] = maxPrice;
    }
    
    // Multiple color selection
    if (colors.length > 0) {
        whereClause.color = {
            [Op.in]: colors
        };
    }
    
    // Multiple size selection
    if (sizes.length > 0) {
        whereClause.size = {
            [Op.in]: sizes
        };
    }
    
    // Brand selection
    if (brands.length > 0) {
        whereClause.brand = {
            [Op.in]: brands
        };
    }
    
    // Stock status
    if (inStock !== null) {
        whereClause.stockQuantity = {
            [Op.gt]: 0
        };
    }
    
    // Calculate pagination
    const offset = (page - 1) * limit;
    
    // Execute query
    const { rows, count } = await Product.findAndCountAll({
        where: whereClause,
        order: [[sortBy, sortOrder]],
        limit,
        offset,
        include: [
            {
                model: Category,
                attributes: ['name']
            },
            {
                model: Brand,
                attributes: ['name']
            }
        ]
    });
    
    return {
        products: rows,
        totalItems: count,
        totalPages: Math.ceil(count / limit),
        currentPage: page
    };
};

Building a Real Estate Search System

Let's create a comprehensive property search system:


// models/Property.js
const Property = sequelize.define('Property', {
    title: DataTypes.STRING,
    price: DataTypes.DECIMAL,
    bedrooms: DataTypes.INTEGER,
    bathrooms: DataTypes.DECIMAL,
    squareFeet: DataTypes.INTEGER,
    propertyType: DataTypes.STRING,
    location: DataTypes.STRING,
    yearBuilt: DataTypes.INTEGER
});

// Property search service
const PropertyService = {
    async searchProperties({
        location,
        minPrice,
        maxPrice,
        minBeds,
        maxBeds,
        minBaths,
        maxBaths,
        propertyTypes,
        minSquareFeet,
        maxSquareFeet,
        minYearBuilt,
        amenities,
        page = 1,
        limit = 12
    }) {
        const whereClause = {};
        
        // Location search (could be expanded to use geocoding)
        if (location) {
            whereClause.location = {
                [Op.iLike]: `%${location}%`
            };
        }
        
        // Price range
        if (minPrice || maxPrice) {
            whereClause.price = {};
            if (minPrice) whereClause.price[Op.gte] = minPrice;
            if (maxPrice) whereClause.price[Op.lte] = maxPrice;
        }
        
        // Bedroom range
        if (minBeds || maxBeds) {
            whereClause.bedrooms = {};
            if (minBeds) whereClause.bedrooms[Op.gte] = minBeds;
            if (maxBeds) whereClause.bedrooms[Op.lte] = maxBeds;
        }
        
        // Bathroom range
        if (minBaths || maxBaths) {
            whereClause.bathrooms = {};
            if (minBaths) whereClause.bathrooms[Op.gte] = minBaths;
            if (maxBaths) whereClause.bathrooms[Op.lte] = maxBaths;
        }
        
        // Property types
        if (propertyTypes && propertyTypes.length > 0) {
            whereClause.propertyType = {
                [Op.in]: propertyTypes
            };
        }
        
        // Square footage range
        if (minSquareFeet || maxSquareFeet) {
            whereClause.squareFeet = {};
            if (minSquareFeet) whereClause.squareFeet[Op.gte] = minSquareFeet;
            if (maxSquareFeet) whereClause.squareFeet[Op.lte] = maxSquareFeet;
        }
        
        // Year built
        if (minYearBuilt) {
            whereClause.yearBuilt = {
                [Op.gte]: minYearBuilt
            };
        }
        
        const offset = (page - 1) * limit;
        
        const { rows, count } = await Property.findAndCountAll({
            where: whereClause,
            include: [
                {
                    model: Amenity,
                    where: amenities ? { id: { [Op.in]: amenities } } : {},
                    required: amenities ? true : false
                }
            ],
            limit,
            offset,
            distinct: true
        });
        
        return {
            properties: rows,
            totalProperties: count,
            totalPages: Math.ceil(count / limit),
            currentPage: page
        };
    }
};

Best Practices for Search Filters

Validate and sanitize all user inputs
Use appropriate indexes for frequently filtered columns
Implement caching for common filter combinations
Consider performance impact of complex filter combinations
Provide meaningful error messages for invalid filters

Common Pitfalls to Avoid

Not handling special characters in search terms
Forgetting to escape user input
Over-complicating filter logic
Not considering query performance with multiple filters

Advanced Filter Techniques

Full-text search implementation
Geospatial filtering
Fuzzy matching
Filter suggestions based on available data