[BUG]: $dynamic queries drops the initial 'where' part of the clause

[gerhardcit]

What version of drizzle-orm are you using?

0.30.10

What version of drizzle-kit are you using?

0.21.1

Describe the Bug

Dynamic query building does not work as indicated here: https://orm.drizzle.team/docs/dynamic-query-building

AI answers suggested this code to created a dynamic query:

To construct an optional where clause in Drizzle with optional "and" fields, including a condition only if you have specific search criteria for that field, you can utilize the dynamic query building feature of Drizzle. This feature allows you to build queries dynamically, adjusting the conditions based on the input data you have.

async function queryWithOptionalConditions(tenant: string, areaMatch?: string, suburbMatch?: string) {
    let query = db.select().from(yourTable).where(eq(yourTable.tenant, tenant));

    if (areaMatch || suburbMatch) {
        query = query.$dynamic().where(or(
            areaMatch ? eq(yourTable.area, areaMatch) : undefined,
            suburbMatch ? eq(yourTable.suburb, suburbMatch) : undefined
        ));
    }
    console.log("sql", query.toSQL());
    return await query;
}

Run it as follows:

const res = await queryWithOptionalConditions("tenant1", "area53", "")

The log printed out is this:

 select "id", "tenant", "area", "suburb"  from "owners" where "owners"."name" = ?',
  params: [ 'area53' ]

Expected behavior

the expected code should be

 select "id", "tenant", "area", "suburb"  from "owners"  where "owners"."tenant" = ? 
AND  "owners"."name" = ?',
 params: [ 'tenant1", 'area53' ]

note that the "where tenant = 'xxx" has gone missing.

Environment & setup

Testing this in sveltelkit project running on cloudflare.

Referring to this page: https://orm.drizzle.team/docs/dynamic-query-building The documentation starts with this problem:

const query = db
  .select()
  .from(users)
  .where(eq(users.id, 1))
  .where(eq(users.name, 'John')); // ❌ Type error - where() can only be invoked once

but never really address this problem of adding optional where based on the input matching fields.

It goes into adding joins, and limits etc, where it should also address the optional where fields which is classic in searching sql dbs.

[jmhmd]

Also seeing this bug, same version of drizzle. Is there any other way to construct complex conditional queries?

[jmhmd]

For what it's worth, my workaround for now is to accumulate where clauses in an array, then add them all at once like so:

const queryWheres = [];
if (condition) {
   queryWheres.push(eq(...));
}
query.where(and(...quereWheres));

[gerhardcit]

Thx @jmhmd , that is really useful. going to give that a try. I'm surprised by the lack of response from the Drizzle team. Dynamic query (in my view) is such a 101 part of querying sql, I would have thought it to be something important to deal with.

[antonjlin]

+1 here, also running into this issue.

[timmygee]

+1 Also

The workaround from @jmhmd did the trick.

It seems that once you hit a certain number of dynamic where clauses, the first one fails to be executed. Looks definitely like a bug to me.