bknd-query-filter

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Advanced Query Filtering

高级查询过滤

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

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

运行中的Bknd项目（本地或已部署）
存在带有数据的实体
已配置SDK或已知API端点
基本了解
```
readMany
```
（参考
```
bknd-crud-read
```
）

When to Use UI Mode

何时使用UI模式

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

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

编码前测试过滤组合
探索数据模式
快速临时查询

UI操作步骤： 管理面板 > 数据 > 选择实体 > 使用过滤控件

When to Use Code Mode

何时使用代码模式

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

复杂多条件查询
动态用户驱动过滤（搜索、分面）
可复用查询构建器
API集成

Code Approach

代码实现方法

Step 1: Understand Operator Categories

步骤1：了解操作符类别

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)

Bknd支持以下过滤操作符：

类别	操作符
相等性	`$eq` , `$ne`
比较	`$gt` , `$gte` , `$lt` , `$lte`
范围	`$between`
模式匹配	`$like` , `$ilike`
数组	`$in` , `$nin` （别名： `$notin` ）
空值检查	`$isnull`
逻辑	`$or` , `$and` （隐式）

Step 2: Use Comparison Operators

步骤2：使用比较操作符

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" },
  },
});

typescript

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

// 相等性（隐式$eq）
const { data } = await api.data.readMany("products", {
  where: { status: "active" },  // 等同于 { status: { $eq: "active" } }
});

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

// 数值比较
const { data } = await api.data.readMany("products", {
  where: {
    price: { $gte: 10 },   // 价格 >= 10
    stock: { $gt: 0 },     // 库存 > 0
  },
});

// 日期比较
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)

步骤3：使用范围操作符($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"] },
  },
});

typescript

// 价格在10到100之间（包含边界）
const { data } = await api.data.readMany("products", {
  where: {
    price: { $between: [10, 100] },
  },
});

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

Step 4: Use Pattern Matching

步骤4：使用模式匹配

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%
});

typescript

// LIKE（区分大小写）- 使用%作为通配符
const { data } = await api.data.readMany("posts", {
  where: { title: { $like: "%React%" } },
});

// ILIKE（不区分大小写）- 搜索场景首选
const { data } = await api.data.readMany("posts", {
  where: { title: { $ilike: "%react%" } },
});

// 以指定内容开头
const { data } = await api.data.readMany("users", {
  where: { name: { $like: "John%" } },
});

// 以指定内容结尾
const { data } = await api.data.readMany("users", {
  where: { email: { $like: "%@gmail.com" } },
});

// 通配符替代方案：用*代替%
const { data } = await api.data.readMany("posts", {
  where: { title: { $like: "*React*" } },  // 会转换为%React%
});

Step 5: Use Array Operators

步骤5：使用数组操作符

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] } },
});

typescript

// 匹配数组中的任意值
const { data } = await api.data.readMany("posts", {
  where: { status: { $in: ["published", "featured"] } },
});

// 排除数组中的值
const { data } = await api.data.readMany("posts", {
  where: { status: { $nin: ["deleted", "archived"] } },
});

// 通过ID获取特定记录
const { data } = await api.data.readMany("products", {
  where: { id: { $in: [1, 5, 10, 15] } },
});

Step 6: Use Null Checks

步骤6：使用空值检查

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 },
  },
});

typescript

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

// 不为空
const { data } = await api.data.readMany("posts", {
  where: { published_at: { $isnull: false } },
});

// 组合：活跃记录（未删除且已发布）
const { data } = await api.data.readMany("posts", {
  where: {
    deleted_at: { $isnull: true },
    published_at: { $isnull: false },
  },
});

Step 7: Combine with AND (Implicit)

步骤7：结合AND（隐式）

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 },
  },
});

同一层级的多个字段条件默认是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

步骤8：使用OR条件

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 } },
    ],
  },
});

typescript

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

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

Step 9: Combine AND + OR

步骤9：结合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 } },
    ],
  },
});

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 } },
    ],
  },
});

// 复杂组合：(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)

步骤10：通过关联字段过滤（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 },
  },
});

使用

join

来过滤关联实体中的字段：

typescript

// 作者角色为admin的文章
const { data } = await api.data.readMany("posts", {
  join: ["author"],
  where: {
    "author.role": { $eq: "admin" },
  },
});

// 客户所在国家为US且产品分类为electronics的订单
const { data } = await api.data.readMany("orders", {
  join: ["customer", "product"],
  where: {
    "customer.country": { $eq: "US" },
    "product.category": { $eq: "electronics" },
  },
});

// 结合常规过滤
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,
});

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;
}

// 使用示例
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

条件OR构建器

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}%` } },
  ]),
});

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 };
}

// 跨多字段搜索
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 },
  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 });
}

typescript

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 = {};

  // 文本搜索
  if (query) {
    where.name = { $ilike: `%${query}%` };
  }

  // 分面过滤
  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

React集成

Search Filter Component

搜索过滤组件

tsx

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>
  );
}

tsx

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="搜索..."
        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="">所有状态</option>
        <option value="draft">草稿</option>
        <option value="published">已发布</option>
      </select>
      <input
        type="date"
        value={filters.minDate}
        onChange={e => setFilters(f => ({ ...f, minDate: e.target.value }))}
      />

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

REST API Approach

REST API方式

Query String Format

查询字符串格式

bash

undefined

bash

undefined

Simple filter

简单过滤

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

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

URL解码后：where={"status":"published"}

undefined

undefined

Complex Query via POST

通过POST实现复杂查询

For complex queries, use POST to

/api/data/:entity/query

bash

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
  }'

对于复杂查询，使用POST请求到

/api/data/:entity/query

：

bash

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

完整示例

typescript

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" },
  },
});

typescript

import { Api } from "bknd";

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

// 1. 简单相等过滤
const published = await api.data.readMany("posts", {
  where: { status: "published" },
});

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

// 3. 文本搜索（不区分大小写）
const searchResults = await api.data.readMany("products", {
  where: { name: { $ilike: "%laptop%" } },
});

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

// 5. 排除软删除记录
const activeRecords = await api.data.readMany("posts", {
  where: { deleted_at: { $isnull: true } },
});

// 6. 复杂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. 通过关联实体过滤
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.

typescript

// 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 },
}

问题： 覆盖之前的条件。

typescript

// 错误 - 第二个赋值会覆盖第一个
where: {
  price: { $gte: 10 },
  price: { $lte: 100 },  // 被覆盖！
}

// 正确 - 使用$between或展开语法
where: {
  price: { $between: [10, 100] },
}
// 或者
where: {
  price: { $gte: 10, $lte: 100 },
}

$or at Wrong Level

$or层级错误

Problem:

$or

must be at top level of where clause.

typescript

// 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 } },
  ],
}

问题：

$or

必须位于where子句的顶层。

typescript

// 错误 - 嵌套$or
where: {
  status: {
    $or: [{ $eq: "a" }, { $eq: "b" }],  // 无效！
  },
}

// 正确 - 同字段使用$in
where: {
  status: { $in: ["a", "b"] },
}

// 正确 - 不同字段的$or放在顶层
where: {
  $or: [
    { status: { $eq: "a" } },
    { featured: { $eq: true } },
  ],
}

Missing Join for Related Filter

关联过滤缺少Join

Problem: Filtering by related field without

join

typescript

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

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

问题： 过滤关联字段但未使用

join

。

typescript

// 错误 - 无法生效
where: { "author.role": { $eq: "admin" } }

// 正确 - 添加join
{
  join: ["author"],
  where: { "author.role": { $eq: "admin" } },
}

Case-Sensitive Search

区分大小写的搜索

Problem:

$like

is case-sensitive.

typescript

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

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

问题：

$like

是区分大小写的。

typescript

// 可能会遗漏结果
where: { title: { $like: "%React%" } }

// 使用$ilike实现不区分大小写
where: { title: { $ilike: "%react%" } }

Empty Filter Objects

空过滤对象

Problem: Empty where returns all records.

typescript

// 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
}

问题： 空where子句会返回所有记录。

typescript

// 返回所有数据（无过滤）
where: {}

// 始终验证过滤条件是否存在
const where = buildFilters(userInput);
if (Object.keys(where).length === 0) {
  // 处理逻辑：显示默认视图或要求至少一个过滤条件
}

Verification

验证方法

Test filters in admin panel first:

Admin Panel > Data > Select Entity
Use filter controls to build query
Verify expected results
Translate to code

Or log the where clause:

typescript

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

先在管理面板测试过滤条件：

管理面板 > 数据 > 选择实体
使用过滤控件构建查询
验证结果符合预期
转换为代码实现

或者打印where子句：

typescript

const where = buildFilters(input);
console.log("查询条件:", 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

推荐做法：

用户搜索场景使用
```
$ilike
```
（不区分大小写）
同字段多值匹配使用
```
$in
```
替代多个
```
$or
```
数值/日期范围使用
```
$between
```
为过滤UI动态构建查询
构建查询前验证/清洗用户输入
过滤关联字段时使用
```
join
```

避免做法：

用户搜索使用
```
$like
```
（存在区分大小写问题）
在字段条件内部嵌套
```
$or
```
过滤关联字段时忘记
```
join
```
直接信任用户输入构建查询
构建过于复杂的嵌套条件
忽略空where子句会返回所有数据的情况

bknd-query-filter

Original

Translation

Advanced Query Filtering

高级查询过滤

Prerequisites

前提条件

When to Use UI Mode

何时使用UI模式

When to Use Code Mode

何时使用代码模式

Code Approach

代码实现方法

Step 1: Understand Operator Categories

步骤1：了解操作符类别

Step 2: Use Comparison Operators

步骤2：使用比较操作符

Step 3: Use Range Operator ($between)

步骤3：使用范围操作符($between)

Step 4: Use Pattern Matching

步骤4：使用模式匹配

Step 5: Use Array Operators

步骤5：使用数组操作符

Step 6: Use Null Checks

步骤6：使用空值检查

Step 7: Combine with AND (Implicit)

步骤7：结合AND（隐式）

Step 8: Use OR Conditions

步骤8：使用OR条件

Step 9: Combine AND + OR

步骤9：结合AND + OR

Step 10: Filter by Related Fields (Join)

步骤10：通过关联字段过滤（Join）

Dynamic Query Building

动态查询构建

Build Queries Programmatically

以编程方式构建查询

Conditional OR Builder

条件OR构建器

Faceted Search Pattern

分面搜索模式

React Integration

React集成

Search Filter Component

搜索过滤组件

REST API Approach

REST API方式

Query String Format

查询字符串格式

Simple filter

简单过滤

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

URL解码后：where={"status":"published"}

Complex Query via POST

通过POST实现复杂查询

Full Example

完整示例

Common Pitfalls

常见陷阱

Combining Same-Field Operators Wrong

错误组合同字段操作符

$or at Wrong Level

$or层级错误

Missing Join for Related Filter

关联过滤缺少Join

Case-Sensitive Search

区分大小写的搜索

Empty Filter Objects

空过滤对象

Verification

验证方法

DOs and DON'Ts

注意事项

Related Skills

相关技能