Drizzle Team•12mo ago

Question about dynamic query building

Hello! I read this page here: https://orm.drizzle.team/docs/dynamic-query-building And I don't quite understand how to use it to merge wheres. I have this code and log:

Dynamic query building - Drizzle ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

13 Replies

PistonmasterOP•12mo ago

it seems to just override the last where instead of merging it What is the proper way to merge querybuilder/$dynamic wheres?

Hocus•12mo ago

you can try something like this

import { and, eq, lt, or, SQL, sql } from "drizzle-orm";

const where: SQL[] = []

const id = ...
if (id > 3) {
  where.push(eq(schema.users.id, id))
}

if (id > 3) {
  where.push(or(lt(schema.users.id, 3), eq(schema.users.id, 3))!)
}

  await db.select({ count: sql<number>`count(*)`.mapWith(Number)}).from(schema.users).where(and(...where))

import { and, eq, lt, or, SQL, sql } from "drizzle-orm";

const where: SQL[] = []

const id = ...
if (id > 3) {
  where.push(eq(schema.users.id, id))
}

if (id > 3) {
  where.push(or(lt(schema.users.id, 3), eq(schema.users.id, 3))!)
}

  await db.select({ count: sql<number>`count(*)`.mapWith(Number)}).from(schema.users).where(and(...where))

PistonmasterOP•12mo ago

that does look interesting, thanks will try it!

Angelelz•12mo ago

You can't call the same method more than once. Instead of merging, what you're doing is overriding

PistonmasterOP•12mo ago

Why does the page even give that example then? lol

Angelelz•12mo ago

The types where made very strict to avoid people from using the same method twice, but that also made dynamic query building very hard You can use $dynamic to avoid the strictness but keep in mind the overriding behavior In the docs, they don't call the same method twice right? Drizzle is made with performance in mind, this solution is very performant

PistonmasterOP•12mo ago

It's overall configusing how they first show that where chaining does not work in traditional drizzle and then they show $dynamic, it makes it seem like $dynamic is the solution for chaining not working.

Angelelz•12mo ago

Yeah, we might need to explain that a little bit better

piedra•12mo ago

I'm having the same doubt as the OP, so just to be clear, doing

let query = db.select().from(table).$dynamic();

query
  .where(eq(table.col1, 'foo'))
  .where(ilike(table.col1, 'bar%'));

const results = await query;

let query = db.select().from(table).$dynamic();

query
  .where(eq(table.col1, 'foo'))
  .where(ilike(table.col1, 'bar%'));

const results = await query;

would execute

select * from "table" where "col1" ilike 'bar%';

select * from "table" where "col1" ilike 'bar%';

If that is correct, the docs indeed never mention that using a dynamic query allows multiple calls to where that are merged automatically, but it's the impression one gets after reading the docs the first time since that's the example that is given. What do you think could help bring some clarity to the docs? would an alert like the one here (https://orm.drizzle.team/docs/sql-schema-declaration) about the export keyword work? Thanks!

Angelelz•12mo ago

Yes, that's the query you'd get. Please add an issue to the GH page, so we can probably explain that better

piedra•12mo ago

sure, that would be in the docs repo, right?

Angelelz•12mo ago

sure

piedra•12mo ago

created here: https://github.com/drizzle-team/drizzle-orm-docs/issues/189 thanks!

Gaming

Programming

Question about dynamic query building