Implementing Search Filters in Express and Sequelize

Understanding Search Filters

Imagine you're in a vast library looking for a specific book. Instead of checking every single book, you might want to narrow down your search using certain criteria: perhaps you know the author's last name starts with 'A', or you're specifically looking for hardcover books. In our digital world, search filters serve the same purpose - they help users quickly find exactly what they're looking for within a large dataset.

In our school supply management system, we need to help administrators find specific students efficiently. They might need to find all students with a particular first name, last name, or those who are left-handed (important for ordering appropriate supplies). Let's build these search capabilities step by step.

Building the Filter System

Let's start by implementing a filter system that allows searching by first name, last name, and handedness preference. Think of this as creating a series of sieves that can be used individually or combined to filter our data.

const { Op } = require('sequelize');

const buildStudentFilters = (queryParams) => {
    // Initialize an empty filter object
    const whereClause = {};
    
    // Add filters based on provided parameters
    if (queryParams.firstName) {
        whereClause.firstName = {
            [Op.like]: `%${queryParams.firstName}%`
        };
    }
    
    if (queryParams.lastName) {
        whereClause.lastName = {
            [Op.like]: `%${queryParams.lastName}%`
        };
    }
    
    if (queryParams.lefty) {
        // Convert string 'true'/'false' to boolean
        whereClause.leftHanded = queryParams.lefty === 'true';
    }
    
    return whereClause;
};

Implementing Name-Based Filtering

When implementing name-based searches, we need to consider various ways people might search for names. Just as you might look up "Rob" and want to find "Robert," "Robbie," and "Robinson," our search should be flexible and user-friendly.

const getStudents = async (req, res) => {
    try {
        const { firstName, lastName, lefty, page = 1, size = 10 } = req.query;
        
        // Build our search filters
        const whereClause = buildStudentFilters(req.query);
        
        // Get total count of filtered results
        const filteredCount = await Student.count({
            where: whereClause
        });
        
        // Calculate pagination parameters
        const limit = size;
        const offset = (page - 1) * size;
        
        // Fetch filtered and paginated results
        const students = await Student.findAll({
            where: whereClause,
            limit,
            offset,
            order: [
                ['lastName', 'ASC'],
                ['firstName', 'ASC']
            ]
        });
        
        // Prepare our response
        res.json({
            rows: students,
            page: Number(page),
            pageSize: Number(size),
            totalFiltered: filteredCount,
            totalPages: Math.ceil(filteredCount / size)
        });
    } catch (error) {
        console.error('Error in student search:', error);
        res.status(500).json({
            error: 'Failed to search students'
        });
    }
};

Handling Handedness Preferences

When dealing with boolean filters like handedness, we need to be especially careful with data validation. Think of this like a form where users can check a box - we need to handle both checked and unchecked states properly.

const validateAndParseLeftyParam = (leftyParam) => {
    if (!leftyParam) return null;
    
    if (leftyParam !== 'true' && leftyParam !== 'false') {
        throw new Error('Lefty should be either true or false');
    }
    
    return leftyParam === 'true';
};

const getStudentsWithHandedness = async (req, res) => {
    try {
        const { lefty } = req.query;
        
        // Validate the lefty parameter
        try {
            const isLeftHanded = validateAndParseLeftyParam(lefty);
            if (isLeftHanded !== null) {
                whereClause.leftHanded = isLeftHanded;
            }
        } catch (error) {
            return res.status(400).json({
                errors: [{ message: error.message }],
                count: 0,
                pageCount: 0
            });
        }
        
        // ... rest of the search logic
    } catch (error) {
        console.error('Error in student search:', error);
        res.status(500).json({
            error: 'Failed to search students'
        });
    }
};

Combining Filters with Pagination and Aggregation

When we combine filters with our previous work on pagination and aggregation, we create a powerful search system. Think of this like a smart library catalog that can tell you not only which books match your search but also how many matches were found and how they're distributed across pages.

const searchStudents = async (req, res) => {
    try {
        const { firstName, lastName, lefty, page = 1, size = 10 } = req.query;
        
        // Validate pagination parameters
        if (page < 1 || size < 1 || size > 200) {
            return res.status(400).json({
                errors: [{ message: 'Invalid pagination parameters' }],
                count: await Student.count(),
                pageCount: 0
            });
        }
        
        // Build and validate filters
        const whereClause = {};
        let validationError = null;
        
        try {
            Object.assign(whereClause, buildStudentFilters(req.query));
        } catch (error) {
            validationError = error;
        }
        
        if (validationError) {
            return res.status(400).json({
                errors: [{ message: validationError.message }],
                count: await Student.count(),
                pageCount: 0
            });
        }
        
        // Get counts and filtered data
        const totalCount = await Student.count();
        const filteredCount = await Student.count({ where: whereClause });
        
        const students = await Student.findAll({
            where: whereClause,
            limit: size,
            offset: (page - 1) * size,
            order: [['lastName', 'ASC'], ['firstName', 'ASC']]
        });
        
        // Return comprehensive response
        res.json({
            rows: students,
            page: Number(page),
            pageSize: Number(size),
            totalCount,
            filteredCount,
            totalPages: Math.ceil(filteredCount / size),
            searchCriteria: {
                firstName: firstName || null,
                lastName: lastName || null,
                lefty: lefty || null
            }
        });
    } catch (error) {
        console.error('Error in student search:', error);
        res.status(500).json({
            error: 'Failed to search students'
        });
    }
};

Real-World Applications and Best Practices

In real-world applications, search filters are crucial for user experience. Consider these practical applications:

Understanding the Results

Let's look at some example searches and their results to better understand how our filtering system works:

{
    "rows": [...14 student records...],
    "page": 1,
    "pageSize": 10,
    "totalCount": 267,
    "filteredCount": 14,
    "totalPages": 2,
    "searchCriteria": {
        "lastName": "Costa",
        "firstName": null,
        "lefty": null
    }
}