Orchid ORM WHERE 条件语句

暮冬拾柒
62 阅读1分钟

where

构建 SQL 查询的 WHERE 子句：

import { sql } from './baseTable';

db.table.where({
  // 当前表的列直接匹配
  name: 'John',

  // 指定表名（支持连接表）
  'table.lastName': 'Johnsonuk',

  // 使用操作符对象（完整列表见"列操作符"部分）
  age: {
    gt: 30,  // 大于 30
    lt: 70,  // 小于 70
  },

  // 原始 SQL 表达式（需从 BaseTable 导入 sql）
  column: sql`custom_sql_expr`,
  column2: (q) => sql`${q.ref('otherColumn')} + 10`,

  // 跨列引用
  firstName: (q) => q.ref('lastName'),  // firstName = lastName
});

多个 where 条件通过 AND 连接：

db.table.where({ foo: 'foo' }).where({ bar: 'bar' });
// 生成 SQL: WHERE foo = 'foo' AND bar = 'bar'

undefined 值会被自动忽略，支持动态条件对象：

type QueryParams = {
  // 支持精确匹配或范围查询
  age?: number | { lt?: number; gt?: number };
};

const loadRecords = async (params: QueryParams) => {
  // 参数为空时查询所有记录
  return db.table.where(params);
};

支持子查询作为条件值：

db.table.where({
  // 将当前表列与子查询结果比较
  someColumn: db.otherTable.where(...).get('targetColumn'),
});

可合并其他查询条件：

const sharedCondition = db.table.where({ name: 'John' });

db.table.where({ id: 1 }, sharedCondition);
// 生成 SQL: WHERE "table"."id" = 1 AND "table"."name" = 'John'

支持原始 SQL 条件：

db.table.where(sql`custom_condition`);

通过回调函数构建复杂条件：

db.table.where((q) =>
  q
    .where({ name: 'Alice' })
    .orWhere({ id: 1 }, { id: 2 })
    .whereIn('letter', ['a', 'b', 'c'])
    .whereExists(Message, 'authorId', 'id'),
);

可接受多个参数，条件间使用 AND 连接：

db.table.where({ id: 1 }, otherQuery, sql`status = 'active'`);

where 子查询

通过子查询关联表数据进行过滤，适用于分面搜索等场景：

const requiredTags = ['typescript', 'node.js'];

const posts = await db.post.where(
  (post) =>
    post.tags // 关联标签表
      .whereIn('tagName', requiredTags) // 标签匹配
      .count() // 统计匹配数量
      .equals(requiredTags.length), // 必须完全匹配
  // 仅返回包含所有指定标签的帖子
);

生成高效 SQL 子查询：

SELECT * FROM "post"
WHERE (
  SELECT count(*) = 2
  FROM "tag" AS "tags"
  WHERE "tag"."tagName" IN ('typescript', 'node.js')
    AND EXISTS (
      SELECT 1 FROM "postTag"
      WHERE "postTag"."postId" = "post"."id"
        AND "postTag"."tagId" = "tag"."id"
    )
)

支持所有 聚合方法（如 min、max、avg），
并可搭配 操作符 使用（如 not、lt）。

where 特殊键

通过对象键定义复合条件（等价于对应方法调用）：

db.table.where({
  NOT: { key: 'value' },       // 等价于 .whereNot()
  OR: [{ name: 'a' }, { name: 'b' }],  // 等价于 .orWhere()
  IN: {
    columns: ['id', 'name'],
    values: [[1, 'a'], [2, 'b']],  // 等价于 .whereIn()
  },
});

支持数组形式的复杂条件：

db.table.where({
  // 多条件取反
  NOT: [{ id: 1 }, { id: 2 }],

  // 嵌套 OR 条件
  OR: [
    [{ id: 1 }, { id: 2 }],  // (id=1 AND id=2)
    [{ id: 3 }, { id: 4 }],  // OR (id=3 AND id=4)
  ],

  // 多列 IN 条件
  IN: [
    { columns: ['id', 'name'], values: [[1, 'a']] },
    { columns: ['status'], values: [['active']] },
  ],
});

whereSql

直接嵌入 SQL 表达式：

db.table.whereSql`price > 100 AND category = 'electronics'`;

whereOneOf

表示 "...并且满足其中一个条件 "：

db.table.where({ id: 1 }).whereOneOf({ color: 'red' }, { color: 'blue' });
// 生成 SQL: WHERE id = 1 AND (color = 'red' OR color = 'blue')

注意条件内部使用 AND 连接：

db.table.whereOneOf({ id: 1, color: 'red' }, { id: 2 });
// 生成 SQL: WHERE (id = 1 AND color = 'red') OR (id = 2)

whereNotOneOf

whereOneOf 的否定形式：

db.table.where({ id: 1 }).whereNotOneOf({ color: 'red' }, { color: 'blue' });
// 生成 SQL: WHERE id = 1 AND NOT (color = 'red' OR color = 'blue')

orWhere

表示 "...或者满足其中一个条件 "：

db.table.where({ id: 1 }).orWhere({ id: 2 });
// 等价于: db.table.orWhere({ id: 1 }, { id: 2 })

生成复合条件：

WHERE (id = 1) OR (id = 2)

whereNot

对条件取反，多个条件内部使用 AND 组合：

// 查找颜色不为红色的记录
db.table.whereNot({ color: 'red' });
// WHERE NOT color = 'red'

// 查找不同时满足两个条件的记录
db.table.whereNot({ one: 1, two: 2 });
// WHERE NOT (one = 1 AND two = 2)

whereNotSql

使用原始 SQL 表达式的 whereNot：

db.table.whereNotSql`status = 'deleted'`;

orWhereNot

对多个条件分别取反后使用 OR 连接：

db.table.orWhereNot({ id: 1 }, { name: 'test' });
// WHERE NOT (id = 1) OR NOT (name = 'test')

whereIn

实现 IN 操作符，支持多种格式：

// 单列 IN 条件
db.table.whereIn('id', [1, 2, 3]);
// 等价于: db.table.where({ id: [1, 2, 3] })

// 多列元组 IN 条件
db.table.whereIn(
  ['id', 'name'],
  [[1, 'Alice'], [2, 'Bob']]
);

// 子查询作为 IN 值
db.table.whereIn(['id'], OtherTable.select('id'));

// 原始 SQL 表达式
db.table.whereIn(['id'], sql`(SELECT id FROM other_table)`);

空值集将解析为特殊的 none 查询：

db.table.whereIn('id', []);  // 等价于查询无结果

orWhereIn

添加 OR 前缀的 IN 条件：

db.table.whereIn('id', [1, 2]).orWhereIn('id', [4, 5]);
// WHERE id IN (1, 2) OR id IN (4, 5)

whereNotIn

IN 条件的否定形式：

db.table.whereNotIn('color', ['red', 'green']);
// WHERE color NOT IN ('red', 'green')

orWhereNotIn

添加 OR 前缀的 NOT IN 条件：

db.table.whereNotIn('id', [1, 2]).orWhereNotIn('id', [4, 5]);
// WHERE id NOT IN (1, 2) OR id NOT IN (4, 5)

whereExists

实现 WHERE EXISTS 子查询，支持多种调用方式：

// 通过关系名查找关联记录
db.user.whereExists('account');  // 查找有账户的用户

// 带条件的子查询
db.user.whereExists((q) => 
  q.accounts.where({ balance: { gt: 0 } })
);  // 查找账户余额大于 0 的用户

// 手动指定连接条件
db.user.whereExists(db.account, 'account.userId', 'user.id');

// 通过回调配置连接条件
db.user.whereExists(db.account, (q) => 
  q.on('account.userId', '=', 'user.id')
);

orWhereExists

添加 OR 前缀的 EXISTS 条件：

db.user.whereExists('account').orWhereExists('profile');
// WHERE EXISTS (...) OR EXISTS (...)

whereNotExists

EXISTS 的否定形式：

db.user.whereNotExists('account');  // 查找没有账户的用户

orWhereNotExists

添加 OR 前缀的 NOT EXISTS 条件：

db.user.whereNotExists('account').orWhereNotExists('profile');
// WHERE NOT EXISTS (...) OR NOT EXISTS (...)

列操作符

通过对象形式定义列操作符，支持值、子查询或原始 SQL：

db.table.where({
  numericColumn: {
    // 小于操作
    lt: 100,
    
    // 子查询比较
    lt: OtherTable.select('maxValue').take(),
    
    // 原始 SQL 表达式
    lt: sql`threshold + 10`,
  },
});

操作符也可作为链式方法使用，见 聚合函数。

通用操作符

适用于所有列类型：

操作符SQL 等价示例
equals={ equals: 'value' }
not!={ not: null }
inIN{ in: [1, 2, 3] }
notInNOT IN{ notIn: subquery() }
db.table.where({
  // JSON 对象比较需用 equals
  jsonColumn: { equals: { key: 'value' } },
  
  // 多条件组合
  column: {
    in: ['a', 'b'],
    not: 'c',
  },
});

数字和日期操作符

用于数值和日期类型比较：

操作符SQL 等价示例
lt<{ lt: new Date() }
lte<={ lte: 100 }
gt>{ gt: subquery() }
gte>={ gte: '2023-01-01' }
betweenBETWEEN ...{ between: [1, 10] }
db.table.where({
  // 数值范围查询
  price: { gt: 100, lt: 200 },
  
  // 日期比较
  created: { gte: '2023-01-01' },
  
  // 带子查询的范围
  score: {
    between: [
      OtherTable.select('minScore'),
      OtherTable.select('maxScore'),
    ],
  },
});

文本操作符

适用于字符串和 JSON 列（JSONB 类型有专用操作符）：

操作符SQL 等价示例
containsLIKE '%value%'{ contains: 'search' }
containsInsensitiveILIKE '%value%'不区分大小写的包含
startsWithLIKE 'value%'前缀匹配
endsWithLIKE '%value'后缀匹配
db.table.where({
  textColumn: {
    // 包含特定字符串
    contains: 'keyword',
    
    // 不区分大小写的前缀匹配
    startsWithInsensitive: 'prefix',
  },
});

JSONB 列操作符

仅适用于 jsonb 类型列：

// 通过 JSONPath 查询
db.table.where((q) => 
  q.get('jsonbColumn').jsonPathQueryFirst('$.nested.value').equals('target')
);

// 超集/子集检查
db.table.where({
  jsonbColumn: {
    jsonSupersetOf: { a: 1 },  // 包含 { a: 1 }
    jsonSubsetOf: { a: 1, b: 2 },  // 是目标对象的子集
  },
});

数组操作符

用于数组类型列：

操作符SQL 等价示例
hasANY{ has: 1 }
hasEvery@>{ hasEvery: [1, 2] }
containedIn<@{ containedIn: [1, 3] }
hasSome&&{ hasSome: ['a', 'b'] }
length数组长度匹配{ length: { gt: 5 } }
db.table.where({
  arrayColumn: {
    // 包含特定元素
    has: 'element',
    
    // 包含所有指定元素
    hasEvery: ['a', 'b'],
    
    // 数组长度限制
    length: { between: [1, 10] },
  },
});

exists

检查是否存在匹配记录，返回布尔值：

const recordExists: boolean = await db.table.where(...).exists();

查询中的 select 语句会被忽略，仅检查记录存在性。

avatar
文章
阅读
粉丝