bknd-query-filter

What this skill does

# 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

```typescript
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)

```typescript
// 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

```typescript
// 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

```typescript
// 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

```typescript
// 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:

```typescript
// 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

```typescript
// 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

```typescript
// 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:

```typescript
// 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

```typescript
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

```typescript
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

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

const PRICE_RANGES = {
  budget: { $lt: 50