Advanced Query Filtering

Build complex queries with multiple conditions, logical operators, and dynamic filters in Bknd.

Prerequisites

• Bknd project running (local or deployed)
• Entity exists with data
• SDK configured or API endpoint known
• Basic understanding of readMany (see bknd-crud-read)

When to Use UI Mode

• Testing filter combinations before coding
• Exploring data patterns
• Quick ad-hoc queries

UI steps: Admin Panel > Data > Select Entity > Use filter controls

When to Use Code Mode

• Complex multi-condition queries
• Dynamic user-driven filters (search, facets)
• Reusable query builders
• API integrations

Code Approach

Step 1: Understand Operator Categories

Bknd supports these filter operators:

Category	Operators
Equality	$eq, $ne
Comparison	$gt, $gte, $lt, $lte
Range	$between
Pattern	$like, $ilike
Array	$in, $nin (alias: $notin)
Null	$isnull
Logical	$or, $and (implicit)

Step 2: Use Comparison Operators

import { Api } from "bknd";
const api = new Api({ host: "http://localhost:7654" });

// Equality (implicit $eq)
const { data } = await api.data.readMany("products", {
  where: { status: "active" },  // Same as { status: { $eq: "active" } }
});

// Not equal
const { data } = await api.data.readMany("products", {
  where: { status: { $ne: "deleted" } },
});

// Numeric comparisons
const { data } = await api.data.readMany("products", {
  where: {
    price: { $gte: 10 },   // price >= 10
    stock: { $gt: 0 },     // stock > 0
  },
});

// Date comparisons
const { data } = await api.data.readMany("orders", {
  where: {
    created_at: { $gte: "2024-01-01" },
    created_at: { $lt: "2024-02-01" },
  },
});

Step 3: Use Range Operator ($between)

// Price between 10 and 100 (inclusive)
const { data } = await api.data.readMany("products", {
  where: {
    price: { $between: [10, 100] },
  },
});

// Date range
const { data } = await api.data.readMany("orders", {
  where: {
    created_at: { $between: ["2024-01-01", "2024-12-31"] },
  },
});

Step 4: Use Pattern Matching

// LIKE (case-sensitive) - use % as wildcard
const { data } = await api.data.readMany("posts", {
  where: { title: { $like: "%React%" } },
});

// ILIKE (case-insensitive) - preferred for search
const { data } = await api.data.readMany("posts", {
  where: { title: { $ilike: "%react%" } },
});

// Starts with
const { data } = await api.data.readMany("users", {
  where: { name: { $like: "John%" } },
});

// Ends with
const { data } = await api.data.readMany("users", {
  where: { email: { $like: "%@gmail.com" } },
});

// Wildcard alternative: use * instead of %
const { data } = await api.data.readMany("posts", {
  where: { title: { $like: "*React*" } },  // Converted to %React%
});

Step 5: Use Array Operators

// In array - match any value
const { data } = await api.data.readMany("posts", {
  where: { status: { $in: ["published", "featured"] } },
});

// Not in array - exclude values
const { data } = await api.data.readMany("posts", {
  where: { status: { $nin: ["deleted", "archived"] } },
});

// Get specific records by IDs
const { data } = await api.data.readMany("products", {
  where: { id: { $in: [1, 5, 10, 15] } },
});

Step 6: Use Null Checks

// Is NULL
const { data } = await api.data.readMany("posts", {
  where: { deleted_at: { $isnull: true } },
});

// Is NOT NULL
const { data } = await api.data.readMany("posts", {
  where: { published_at: { $isnull: false } },
});

// Combine: active records (not deleted, has been published)
const { data } = await api.data.readMany("posts", {
  where: {
    deleted_at: { $isnull: true },
    published_at: { $isnull: false },
  },
});

Step 7: Combine with AND (Implicit)

Multiple fields at same level = AND:

// status = "published" AND category = "news" AND views > 100
const { data } = await api.data.readMany("posts", {
  where: {
    status: { $eq: "published" },
    category: { $eq: "news" },
    views: { $gt: 100 },
  },
});

Step 8: Use OR Conditions

// status = "published" OR featured = true
const { data } = await api.data.readMany("posts", {
  where: {
    $or: [
      { status: { $eq: "published" } },
      { featured: { $eq: true } },
    ],
  },
});

// Multiple OR conditions
const { data } = await api.data.readMany("users", {
  where: {
    $or: [
      { role: { $eq: "admin" } },
      { role: { $eq: "moderator" } },
      { is_verified: { $eq: true } },
    ],
  },
});

Step 9: Combine AND + OR

// category = "news" AND (status = "published" OR author_id = currentUser)
const { data } = await api.data.readMany("posts", {
  where: {
    category: { $eq: "news" },
    $or: [
      { status: { $eq: "published" } },
      { author_id: { $eq: currentUserId } },
    ],
  },
});

// Complex: (price < 50 OR on_sale = true) AND in_stock = true AND category IN ["electronics", "books"]
const { data } = await api.data.readMany("products", {
  where: {
    in_stock: { $eq: true },
    category: { $in: ["electronics", "books"] },
    $or: [
      { price: { $lt: 50 } },
      { on_sale: { $eq: true } },
    ],
  },
});

Step 10: Filter by Related Fields (Join)

Use join to filter by fields in related entities:

// Posts where author.role = "admin"
const { data } = await api.data.readMany("posts", {
  join: ["author"],
  where: {
    "author.role": { $eq: "admin" },
  },
});

// Orders where customer.country = "US" AND product.category = "electronics"
const { data } = await api.data.readMany("orders", {
  join: ["customer", "product"],
  where: {
    "customer.country": { $eq: "US" },
    "product.category": { $eq: "electronics" },
  },
});

// Combine with regular filters
const { data } = await api.data.readMany("posts", {
  join: ["author"],
  where: {
    status: { $eq: "published" },
    "author.is_verified": { $eq: true },
  },
});

Dynamic Query Building

Build Queries Programmatically

type WhereClause = Record<string, any>;

function buildProductQuery(filters: {
  search?: string;
  minPrice?: number;
  maxPrice?: number;
  categories?: string[];
  inStock?: boolean;
}): WhereClause {
  const where: WhereClause = {};

  if (filters.search) {
    where.name = { $ilike: `%${filters.search}%` };
  }

  if (filters.minPrice !== undefined) {
    where.price = { ...where.price, $gte: filters.minPrice };
  }

  if (filters.maxPrice !== undefined) {
    where.price = { ...where.price, $lte: filters.maxPrice };
  }

  if (filters.categories?.length) {
    where.category = { $in: filters.categories };
  }

  if (filters.inStock !== undefined) {
    where.stock = filters.inStock ? { $gt: 0 } : { $eq: 0 };
  }

  return where;
}

// Usage
const filters = { search: "laptop", minPrice: 500, categories: ["electronics"] };
const { data } = await api.data.readMany("products", {
  where: buildProductQuery(filters),
  sort: { price: "asc" },
  limit: 20,
});

Conditional OR Builder

function buildOrConditions(conditions: WhereClause[]): WhereClause {
  const validConditions = conditions.filter(c => Object.keys(c).length > 0);

  if (validConditions.length === 0) return {};
  if (validConditions.length === 1) return validConditions[0];

  return { $or: validConditions };
}

// Search across multiple fields
const searchTerm = "john";
const { data } = await api.data.readMany("users", {
  where: buildOrConditions([
    { name: { $ilike: `%${searchTerm}%` } },
    { email: { $ilike: `%${searchTerm}%` } },
    { username: { $ilike: `%${searchTerm}%` } },
  ]),
});

Faceted Search Pattern

type Facets = {
  category?: string;
  brand?: string;
  priceRange?: "budget" | "mid" | "premium";
  rating?: number;
};

const PRICE_RANGES = {
  budget: { $lt: 50 },
  mid: { $between: [50, 200] },
  premium: { $gt: 200 },
};

async function facetedSearch(query: string, facets: Facets) {
  const where: WhereClause = {};

  // Text search
  if (query) {
    where.name = { $ilike: `%${query}%` };
  }

  // Facet filters
  if (facets.category) {
    where.category = { $eq: facets.category };
  }

  if (facets.brand) {
    where.brand = { $eq: facets.brand };
  }

  if (facets.priceRange) {
    where.price = PRICE_RANGES[facets.priceRange];
  }

  if (facets.rating) {
    where.rating = { $gte: facets.rating };
  }

  return api.data.readMany("products", { where, limit: 50 });
}

React Integration

Search Filter Component

import { useState, useCallback } from "react";
import { useApp } from "bknd/react";
import useSWR from "swr";
import { useDebouncedValue } from "@mantine/hooks";

type Filters = {
  search: string;
  status: string;
  minDate: string;
};

function FilteredList() {
  const { api } = useApp();
  const [filters, setFilters] = useState<Filters>({
    search: "",
    status: "",
    minDate: "",
  });
  const [debouncedFilters] = useDebouncedValue(filters, 300);

  const buildWhere = useCallback((f: Filters) => {
    const where: Record<string, any> = {};

    if (f.search) {
      where.title = { $ilike: `%${f.search}%` };
    }
    if (f.status) {
      where.status = { $eq: f.status };
    }
    if (f.minDate) {
      where.created_at = { $gte: f.minDate };
    }

    return where;
  }, []);

  const { data: posts, isLoading } = useSWR(
    ["posts", debouncedFilters],
    () => api.data.readMany("posts", {
      where: buildWhere(debouncedFilters),
      sort: { created_at: "desc" },
      limit: 20,
    }).then(r => r.data)
  );

  return (
    <div>
      <input
        placeholder="Search..."
        value={filters.search}
        onChange={e => setFilters(f => ({ ...f, search: e.target.value }))}
      />
      <select
        value={filters.status}
        onChange={e => setFilters(f => ({ ...f, status: e.target.value }))}
      >
        <option value="">All statuses</option>
        <option value="draft">Draft</option>
        <option value="published">Published</option>
      </select>
      <input
        type="date"
        value={filters.minDate}
        onChange={e => setFilters(f => ({ ...f, minDate: e.target.value }))}
      />

      {isLoading ? <p>Loading...</p> : (
        <ul>
          {posts?.map(post => <li key={post.id}>{post.title}</li>)}
        </ul>
      )}
    </div>
  );
}

REST API Approach

Query String Format

# Simple filter
curl "http://localhost:7654/api/data/posts?where=%7B%22status%22%3A%22published%22%7D"

# URL-decoded: where={"status":"published"}

Complex Query via POST

For complex queries, use POST to /api/data/:entity/query:

curl -X POST http://localhost:7654/api/data/posts/query \
  -H "Content-Type: application/json" \
  -d '{
    "where": {
      "category": {"$eq": "news"},
      "$or": [
        {"status": {"$eq": "published"}},
        {"featured": {"$eq": true}}
      ]
    },
    "sort": {"created_at": "desc"},
    "limit": 20
  }'

Full Example

import { Api } from "bknd";

const api = new Api({ host: "http://localhost:7654" });

// 1. Simple equality filter
const published = await api.data.readMany("posts", {
  where: { status: "published" },
});

// 2. Numeric range
const midPriced = await api.data.readMany("products", {
  where: { price: { $between: [50, 200] } },
});

// 3. Text search (case-insensitive)
const searchResults = await api.data.readMany("products", {
  where: { name: { $ilike: "%laptop%" } },
});

// 4. Multiple values
const specificCategories = await api.data.readMany("products", {
  where: { category: { $in: ["electronics", "computers"] } },
});

// 5. Exclude soft-deleted
const activeRecords = await api.data.readMany("posts", {
  where: { deleted_at: { $isnull: true } },
});

// 6. Complex AND + OR
const complexQuery = await api.data.readMany("orders", {
  where: {
    created_at: { $gte: "2024-01-01" },
    status: { $nin: ["cancelled", "refunded"] },
    $or: [
      { total: { $gt: 100 } },
      { is_priority: { $eq: true } },
    ],
  },
  sort: { created_at: "desc" },
  limit: 50,
});

// 7. Filter by related entity
const adminPosts = await api.data.readMany("posts", {
  join: ["author"],
  where: {
    "author.role": { $eq: "admin" },
    status: { $eq: "published" },
  },
});

Common Pitfalls

Combining Same-Field Operators Wrong

Problem: Overwriting previous condition.

// Wrong - second assignment overwrites first
where: {
  price: { $gte: 10 },
  price: { $lte: 100 },  // Overwrites!
}

// Correct - use $between or spread
where: {
  price: { $between: [10, 100] },
}
// Or
where: {
  price: { $gte: 10, $lte: 100 },
}

$or at Wrong Level

Problem: $or must be at top level of where clause.

// Wrong - nested $or
where: {
  status: {
    $or: [{ $eq: "a" }, { $eq: "b" }],  // Invalid!
  },
}

// Correct - use $in for same field
where: {
  status: { $in: ["a", "b"] },
}

// Correct - $or at top level for different fields
where: {
  $or: [
    { status: { $eq: "a" } },
    { featured: { $eq: true } },
  ],
}

Missing Join for Related Filter

Problem: Filtering by related field without join.

// Wrong - won't work
where: { "author.role": { $eq: "admin" } }

// Correct - add join
{
  join: ["author"],
  where: { "author.role": { $eq: "admin" } },
}

Case-Sensitive Search

Problem: $like is case-sensitive.

// May miss results
where: { title: { $like: "%React%" } }

// Use $ilike for case-insensitive
where: { title: { $ilike: "%react%" } }

Empty Filter Objects

Problem: Empty where returns all records.

// Returns everything (no filter)
where: {}

// Always validate filters exist
const where = buildFilters(userInput);
if (Object.keys(where).length === 0) {
  // Handle: show default view or require at least one filter
}

Verification

Test filters in admin panel first:

1. Admin Panel > Data > Select Entity
2. Use filter controls to build query
3. Verify expected results
4. Translate to code

Or log the where clause:

const where = buildFilters(input);
console.log("Query:", JSON.stringify(where, null, 2));
const { data } = await api.data.readMany("posts", { where });

DOs and DON'Ts

DO:

• Use $ilike for user-facing search (case-insensitive)
• Use $in instead of multiple $or for same field
• Use $between for numeric/date ranges
• Build queries dynamically for filter UIs
• Validate/sanitize user input before building queries
• Use join when filtering by related fields

DON'T:

• Use $like for user search (case-sensitive issues)
• Nest $or inside field conditions
• Forget join for related field filters
• Trust user input directly in queries
• Build excessively complex nested conditions
• Forget that empty where = return all

Related Skills

• bknd-crud-read - Basic read operations
• bknd-pagination - Paginate filtered results
• bknd-define-relationship - Set up relations for join filters
• bknd-row-level-security - Apply automatic filters via policies