DT
Drizzle Team•14mo ago
Pistonmaster

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.
No description
No description
13 Replies
Pistonmaster
PistonmasterOP•14mo ago
it seems to just override the last where instead of merging it What is the proper way to merge querybuilder/$dynamic wheres?
Hocus
Hocus•14mo 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))
Pistonmaster
PistonmasterOP•14mo ago
that does look interesting, thanks will try it!
Angelelz
Angelelz•14mo ago
You can't call the same method more than once. Instead of merging, what you're doing is overriding
Pistonmaster
PistonmasterOP•14mo ago
Why does the page even give that example then? lol
Angelelz
Angelelz•14mo 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
Pistonmaster
PistonmasterOP•14mo 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
Angelelz•14mo ago
Yeah, we might need to explain that a little bit better
piedra
piedra•13mo 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
Angelelz•13mo 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
piedra•13mo ago
sure, that would be in the docs repo, right?
Angelelz
Angelelz•13mo ago
sure
piedra
piedra•13mo ago

Did you find this page helpful?