Implementing Search Filters

Understanding the Problem

Think of a library catalog system. When you're looking for a book, you might want to search by the author's first name, last name, or perhaps filter to see only hardcover books. In our student management system, we need to create similar filtering capabilities. We need to allow users to:

Search for students by:

• First name (partial matches allowed)
• Last name (partial matches allowed)
• Handedness (left or right handed)

These filters should work together with our existing pagination and be efficient in terms of database queries.

Devising a Plan

1. Implement the firstName filter using Sequelize's LIKE operator
2. Add the lastName filter using similar logic
3. Implement the handedness filter as a boolean condition
4. Combine all filters with existing pagination
5. Update the count queries to respect filters

Implementation

Let's build this step by step, starting with our enhanced students endpoint:

Complete Implementation (routes/api/students.js)

const express = require('express');
const { Op } = require('sequelize');
const { Student } = require('../../db/models');

router.get('/', async (req, res) => {
    // Destructure all query parameters
    let { page, size, firstName, lastName, lefty } = req.query;
    
    // Initialize pagination params with defaults
    page = parseInt(page) || 1;
    size = parseInt(size) || 10;

    // Initialize the where clause for filtering
    const where = {};

    // Build firstName filter
    if (firstName) {
        where.firstName = {
            [Op.iLike]: `%${firstName}%`  // Case-insensitive pattern matching
        };
    }

    // Build lastName filter
    if (lastName) {
        where.lastName = {
            [Op.iLike]: `%${lastName}%`
        };
    }

    // Build handedness filter
    if (lefty) {
        if (lefty !== 'true' && lefty !== 'false') {
            return res.status(400).json({
                errors: [{ message: 'Lefty should be either true or false' }],
                count: 0,
                pageCount: 0
            });
        }
        where.leftHanded = lefty === 'true';
    }

    try {
        // Get filtered count for pagination
        const totalCount = await Student.count({ where });

        // Calculate pagination values
        const pageCount = Math.ceil(totalCount / size);
        const offset = (page - 1) * size;

        // Build query options
        const queryOptions = {
            where,
            order: [
                ['lastName', 'ASC'],
                ['firstName', 'ASC']
            ]
        };

        // Add pagination unless it's the special case
        if (page !== 0 || size !== 0) {
            queryOptions.limit = size;
            queryOptions.offset = offset;
        }

        // Execute the main query
        const students = await Student.findAll(queryOptions);

        // Return formatted response
        return res.json({
            rows: students,
            page: page || 1,
            pageCount,
            count: totalCount
        });

    } catch (err) {
        console.error(err);
        return res.status(500).json({
            errors: [{ message: 'Server error occurred' }],
            count: 0,
            pageCount: 0
        });
    }
});
    

Understanding Pattern Matching

Let's break down how pattern matching works with the % wildcard:

'%dexter%'   matches: "Dexter", "Dexterday", "MrDexter"
'R%y'        matches: "Ray", "Ruby", "Randy"
'ay'         matches: "Ray", "May", "Jay"
    

Real World Examples

Search filters are everywhere in modern applications:

• Email clients: Filter by sender, subject, date
• E-commerce: Filter by price range, category, brand
• Social media: Filter by date, location, hashtags
• Music apps: Filter by artist, genre, album

Important Concepts

Case Sensitivity

We use iLike instead of Like to make searches case-insensitive. This means:

"Dexter" matches: "dexter", "DEXTER", "DeXtEr"
    

Combining Filters

When multiple filters are applied, they work as AND conditions:

firstName: "Ray"
lastName: "Smith"
lefty: true

// This will find left-handed students named Ray Smith
    

Testing Strategy

Test the following scenarios:

• Single filter applications
• Multiple combined filters
• Case sensitivity
• Partial matches
• Empty result sets
• Invalid filter values

Error Handling

Common errors to handle:

• Invalid boolean values for lefty parameter
• SQL injection attempts in name parameters
• Missing or malformed parameters
• Database connection issues

Performance Considerations

To optimize filter performance:

• Create indexes on frequently filtered columns
• Use database-specific optimizations for pattern matching
• Consider caching frequently used filter results
• Monitor query execution plans

Debugging Tips

When troubleshooting filters:

• Log the constructed where clause
• Test filters individually first
• Verify case sensitivity behavior
• Check for proper parameter sanitization

Further Enhancements

Consider these potential improvements:

• Add fuzzy search capabilities
• Implement filter combinations (OR conditions)
• Add range-based filters
• Support multiple values per filter