Complex Filtering — AND, OR, and Nested Conditions

In this tutorial, you will learn about Complex Filtering. We cover key concepts, practical examples, and best practices to help you master this topic.

Complex filtering combines multiple conditions with AND/OR logic and nested groupings, enabling sophisticated queries like find products in electronics category with price between 10 and 100 OR on sale.

Query Parameter Syntax

# AND (default)
GET /api/products?category=electronics&price_min=10

# OR with pipe
GET /api/products?category=electronics|clothing

# Nested with parentheses (advanced)
GET /api/products?filter=(category=electronics,price_min=10)OR(on_sale=true)

Implementation

app.get("/api/products", async (req, res) => {
  const { category, price_min, price_max, on_sale } = req.query;
  const params = [];
  const andConditions = [];
  const orConditions = [];
  let idx = 1;

  // AND conditions
  if (category && !category.includes("|")) {
    andConditions.push(`category = $${idx++}`);
    params.push(category);
  }

  // OR conditions
  if (category && category.includes("|")) {
    const categories = category.split("|");
    const placeholders = categories.map(() => `$${idx++}`);
    orConditions.push(`category IN (${placeholders.join(",")})`);
    params.push(...categories);
  }

  if (price_min) {
    andConditions.push(`price >= $${idx++}`);
    params.push(parseFloat(price_min));
  }

  let whereClause = "";
  const allConditions = [...andConditions];

  if (orConditions.length > 0) {
    allConditions.push(`(${orConditions.join(" OR ")})`);
  }

  if (allConditions.length > 0) {
    whereClause = `WHERE ${allConditions.join(" AND ")}`;
  }

  const result = await db.query(
    `SELECT * FROM products ${whereClause}`, params
  );
  res.json({ data: result.rows });
});

Common Mistakes

1. SQL Injection — Building filter strings from user input.
2. No limit on complexity — Allowing overly complex filter expressions.
3. Ambiguous precedence — Not defining AND/OR evaluation order.
4. No validation — Accepting invalid filter field names.

Practice Questions

1. How do you implement OR filtering?
2. How do nested conditions work?
3. Why limit filter complexity?
4. How do you prevent SQL injection in complex filters?

Challenge

Implement complex filtering for a real estate API. Support AND/OR conditions for price range, property type, number of bedrooms, location, and amenities. Validate all filter fields and use parameterized queries.

What's Next

In the next lesson, you will learn pagination metadata formats.