过滤器和条件运算符

传递给过滤器运算符和 sql 函数的所有值都会自动进行参数化。例如，这个查询：

await db.select().from(users).where(eq(users.id, 42));

将会被翻译为：

select "id", "name", "age" from "users" where "users"."id" = $1; -- 参数: [42]

我们原生支持所有方言特定的过滤器和条件运算符。

您可以从 drizzle-orm 导入所有过滤器和条件：

import { eq, ne, gt, gte, ... } from "drizzle-orm";

eq

值等于 n

import { eq } from "drizzle-orm";

db.select().from(table).where(eq(table.column, 5));

SELECT * FROM "table" WHERE "table"."column" = 5

import { eq } from "drizzle-orm";

db.select().from(table).where(eq(table.column1, table.column2));

SELECT * FROM "table" WHERE "table"."column1" = "table"."column2"

ne

值不等于 n

import { ne } from "drizzle-orm";

db.select().from(table).where(ne(table.column, 5));

SELECT * FROM "table" WHERE "table"."column" <> 5

import { ne } from "drizzle-orm";

db.select().from(table).where(ne(table.column1, table.column2));

SELECT * FROM "table" WHERE "table"."column1" <> "table"."column2"

---

gt

值大于 n

import { gt } from "drizzle-orm";

db.select().from(table).where(gt(table.column, 5));

SELECT * FROM "table" WHERE "table"."column" > 5

import { gt } from "drizzle-orm";

db.select().from(table).where(gt(table.column1, table.column2));

SELECT * FROM "table" WHERE "table"."column1" > "table"."column2"

gte

值大于或等于 n

import { gte } from "drizzle-orm";

db.select().from(table).where(gte(table.column, 5));

SELECT * FROM "table" WHERE "table"."column" >= 5

import { gte } from "drizzle-orm";

db.select().from(table).where(gte(table.column1, table.column2));

SELECT * FROM "table" WHERE "table"."column1" >= "table"."column2"

lt

值小于 n

import { lt } from "drizzle-orm";

db.select().from(table).where(lt(table.column, 5));

SELECT * FROM "table" WHERE "table"."column" < 5

import { lt } from "drizzle-orm";

db.select().from(table).where(lt(table.column1, table.column2));

SELECT * FROM "table" WHERE "table"."column1" < "table"."column2"

lte

值小于或等于 n。

import { lte } from "drizzle-orm";

db.select().from(table).where(lte(table.column, 5));

SELECT * FROM "table" WHERE "table"."column" <= 5

import { lte } from "drizzle-orm";

db.select().from(table).where(lte(table.column1, table.column2));

SELECT * FROM "table" WHERE "table"."column1" <= "table"."column2"

---

exists

值存在

import { exists } from "drizzle-orm";

const query = db.select().from(table2)
db.select().from(table).where(exists(query));

SELECT * FROM "table" WHERE EXISTS (SELECT * FROM "table2")

notExists

import { notExists } from "drizzle-orm";

const query = db.select().from(table2)
db.select().from(table).where(notExists(query));

SELECT * FROM "table" WHERE NOT EXISTS (SELECT * FROM "table2")

isNull

值为 null

import { isNull } from "drizzle-orm";

db.select().from(table).where(isNull(table.column));

SELECT * FROM "table" WHERE ("table"."column" IS NULL)

isNotNull

值不为 null

import { isNotNull } from "drizzle-orm";

db.select().from(table).where(isNotNull(table.column));

SELECT * FROM "table" WHERE ("table"."column" IS NOT NULL)

---

inArray

值位于值数组中

import { inArray } from "drizzle-orm";

db.select().from(table).where(inArray(table.column, [1, 2, 3, 4]));

SELECT * FROM "table" WHERE "table"."column" IN (1, 2, 3, 4)

import { inArray } from "drizzle-orm";

const query = db.select({ data: table2.column }).from(table2);
db.select().from(table).where(inArray(table.column, query));

SELECT * FROM "table" WHERE "table"."column" IN (SELECT "table2"."column" FROM "table2")

notInArray

值不位于值数组中

import { notInArray } from "drizzle-orm";

db.select().from(table).where(notInArray(table.column, [1, 2, 3, 4]));

SELECT * FROM "table" WHERE "table"."column" NOT IN (1, 2, 3, 4)

import { notInArray } from "drizzle-orm";

const query = db.select({ data: table2.column }).from(table2);
db.select().from(table).where(notInArray(table.column, query));

SELECT * FROM "table" WHERE "table"."column" NOT IN (SELECT "table2"."column" FROM "table2")

---

between

值在两个值之间

import { between } from "drizzle-orm";

db.select().from(table).where(between(table.column, 2, 7));

SELECT * FROM "table" WHERE "table"."column" BETWEEN 2 AND 7

notBetween

值不在两个值之间

import { notBetween } from "drizzle-orm";

db.select().from(table).where(notBetween(table.column, 2, 7));

SELECT * FROM "table" WHERE "table"."column" NOT BETWEEN 2 AND 7

---

like

值类似于其他值，区分大小写

import { like } from "drizzle-orm";

db.select().from(table).where(like(table.column, "%llo wor%"));

SELECT * FROM "table" WHERE "table"."column" LIKE '%llo wor%'

notLike

值不类似于其他值，区分大小写

import { notLike } from "drizzle-orm";

db.select().from(table).where(notLike(table.column, "%llo wor%"));

SELECT * FROM "table" WHERE "table"."column" NOT LIKE '%llo wor%'

ilike

值与某个其他值相似，不区分大小写

import { ilike } from "drizzle-orm";

db.select().from(table).where(ilike(table.column, "%llo wor%"));

SELECT * FROM "table" WHERE "table"."column" ILIKE '%llo wor%'

notIlike

值与某个其他值不相似，不区分大小写

import { notIlike } from "drizzle-orm";

db.select().from(table).where(notIlike(table.column, "%llo wor%"));

SELECT * FROM "table" WHERE "table"."column" NOT ILIKE '%llo wor%'

---

not

所有条件必须返回 false。

import { eq, not } from "drizzle-orm";

db.select().from(table).where(not(eq(table.column, 5)));

SELECT * FROM "table" WHERE NOT ("table"."column" = 5)

and

所有条件必须返回 true。

import { gt, lt, and } from "drizzle-orm";

db.select().from(table).where(and(gt(table.column, 5), lt(table.column, 7)));

SELECT * FROM "table" WHERE ("table"."column" > 5 AND "table"."column" < 7)

or

一个或多个条件必须返回 true。

import { gt, lt, or } from "drizzle-orm";

db.select().from(table).where(or(gt(table.column, 5), lt(table.column, 7)));

SELECT * FROM "table" WHERE ("table"."column" > 5 OR "table"."column" < 7)

---

arrayContains

测试某个列或表达式是否包含作为第二个参数传递的列表中的所有元素

import { arrayContains } from "drizzle-orm";

const contains = await db.select({ id: posts.id }).from(posts)
  .where(arrayContains(posts.tags, ['Typescript', 'ORM']));

const withSubQuery = await db.select({ id: posts.id }).from(posts)
  .where(arrayContains(
    posts.tags,
    db.select({ tags: posts.tags }).from(posts).where(eq(posts.id, 1)),
  ));

select "id" from "posts" where "posts"."tags" @> {Typescript,ORM};
select "id" from "posts" where "posts"."tags" @> (select "tags" from "posts" where "posts"."id" = 1);

arrayContained

测试作为第二个参数传递的列表是否包含某个列或表达式中的所有元素

import { arrayContained } from "drizzle-orm";

const contained = await db.select({ id: posts.id }).from(posts)
  .where(arrayContained(posts.tags, ['Typescript', 'ORM']));

select "id" from "posts" where "posts"."tags" <@ {Typescript,ORM};

arrayOverlaps

测试某个列或表达式是否包含作为第二个参数传递的列表中的任何元素。

import { arrayOverlaps } from "drizzle-orm";

const overlaps = await db.select({ id: posts.id }).from(posts)
  .where(arrayOverlaps(posts.tags, ['Typescript', 'ORM']));

select "id" from "posts" where "posts"."tags" && {Typescript,ORM}