Implementing Search Filters in ORMs

In this lesson, you will learn how to transform arbitrary user input into dynamic ORM queries to filter database records effectively.

When building applications, users often want to refine results—for example, searching for products by name, price range, or category. Instead of writing separate functions for every possible combination (like findByName, findByPrice, or findByNameAndPrice), you can construct dynamic query builders.

The core idea is to treat your query object as a mutable variable that you build up conditionally based on the presence of user-provided filters.

Building Dynamic Queries

Imagine a request comes in from a frontend search form. You receive an object containing optional parameters. You start with a base query (the full model) and add .where() clauses only if the specific filter exists in the request.

Here is how you might implement this in a standard Node.js ORM environment:

async function getFilteredProducts(filters) {
  // 1. Start with the base query
  let query = Product.query();

  // 2. Conditionally append filters
  if (filters.name) {
    query = query.where('name', 'like', `%${filters.name}%`);
  }

  if (filters.minPrice) {
    query = query.where('price', '>=', filters.minPrice);
  }

  if (filters.category) {
    query = query.where('category_id', filters.category);
  }

  // 3. Execute the final query
  return await query.execute();
}

Why this works

By checking if (filters.name) before calling .where(), you ensure that you don't add empty or null constraints to your SQL statement. If the user provides no filters, the query variable remains the base model, returning all records. If they provide all three, the ORM chains the clauses together into a single, efficient SQL statement behind the scenes.

This approach keeps your code clean and extensible. If you need to add a "stock status" filter later, you simply add one more if block without breaking your existing logic.

Key Takeaway: Dynamic filtering relies on conditionally chaining query methods to a base query object, ensuring that only active user parameters influence the final database statement.