[FEATURE]: Allow adding filters based on conditions

[saurabh7481]

Describe want to want

In real world, it is very rare to just fetch stuff from database in one go using pre defined filters and send it to client. There are many complex conditions which needs to be checked and based on them filters need to be added. I stumbled across this situation and I am not sure how to tackle it.

For reference, this is my base query:

let dataQuery = connection.db
            .select({
                account_id: paymentCustomers.accountId,
                charge_id: paymentCharges.chargeId
            })
            .from(paymentCharges)
            .innerJoin(
                paymentCustomers,
                eq(paymentCharges.customer, paymentCustomers.customerId)
            )
            .orderBy(paymentCharges.created)
            .groupBy(paymentCharges.chargeId)
            .where(
                and(
                    gt(paymentCharges.id, 0),
                    like(paymentCharges.onBehalfOf, user.accountId)
                )
            )
            .limit(Number(pageSize))
            .offset(skip)
            .prepare();

There are two filters applied in this query, however many more filters need to be applied here based on filters sent by client. I am used to using kysely where you would just chain WHERE statements one after another and it would all add up. Here, if i try doing so it removes previous WHERE filter. This is a very basic requirement for a query builders, that you are able to chain up conditions one after another. I have looked into documentation but could not find any.

Doing so in kysely was simple as I would just create a helper function which would go through all filters and add them to WHERE blocks in the query. I want to leave number of filters to client team and this was a decent solution for that. However, prepared statements in drizzle dont seem to have that capability.

Am i missing anything or is there any approach to handle this condition?

Thanks

[dankochetov]

You can just store the condition in a variable and update it according to your logic, then pass to .where(). Its type would be SQL.

[saurabh7481]

You can just store the condition in a variable and update it according to your logic, then pass to .where(). Its type would be SQL.

Yes, but template string is something I would like to use as a last resort. It's a very basic requirement to have flexibilty to add building blocks to a query and adding multiple WHERE based on conditions. Currently, I had to parse all my filters in a SQL template string and use 'sql' inside WHERE to do the work but it would be better if I could just update the query itself by adding WHERE blocks with appropriate conditions given by drizzle just like how kysely does this.

Just a suggestion which would drastically improve the DX, at least for me.

[AndriiSherman]

you can do this as well Don't look at the logic. This is just an example of how to build a where statement and then use it. No need to parse or do sql template

import { and, eq, lt, or, 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().from(schema.users).where(and(...where))

Instead of id > 3 you can make any assumption based on the input by the user. Like if(name)/if(search)/etc.

[saurabh7481]

Perfect. I was already using SQLChunks but I didn't know that you can use conditional operators inside also. Though, I am going to stick with sql.raw for now because of some reason but I can definitely see this being useful in other scenarios.

Thanks all for making drizzle.

[psiho]

If I can also suggest to put example like this in documentation. It's a very common scenario, in example, when user has search form with several optional criteria. I'm pretty sure issue #226 was the same thing but the author gave up. Took me an hour to figure this out because ternary operators within with() were not working.

[adicco]

Would be very helpful to have this in the docs. Thanks for the example @AndriiSherman

[arily]

this pattern won't work in prepared statements.

for a prepared stmt I have to apply one of the following pattern:

-- supports MySQL5
WHERE CASE WHEN :cond <> null AND :cond <> "undefined" THEN column = :cond ELSE 1 END

-- able to use index starts from mysql8
WHERE COALESCE(column = :cond, :cond IS NULL)

do we have better patterns for prepare statements?

[net]

This can also be done using undefined in and() and or() calls, as undefined is removed by Drizzle when compiling the query:

db
  .select()
  .from(users)
  .where(
    and(
      subscribedOnly ? users.subscribed : undefined,
      companyID ? eq(users.companyID, companyID) : undefined,
      userID ? eq(users.id, userID) : undefined,
    )
  )

See also https://github.com/drizzle-team/drizzle-orm/issues/1644#issuecomment-1877442097 for stacking multiple .where() calls.

[janvorwerk]

you can do this as well Don't look at the logic. This is just an example of how to build a where statement and then use it. No need to parse or do sql template

import { and, eq, lt, or, 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().from(schema.users).where(and(...where))

Instead of id > 3 you can make any assumption based on the input by the user. Like if(name)/if(search)/etc.

❤️🔥🚀 I'd say that adding an example on the doc page related to dynamic query building would suffice to close this issue https://orm.drizzle.team/docs/dynamic-query-building

[Bahnasawy]

This can also be done using undefined in and() and or() calls, as undefined is removed by Drizzle when compiling the query:

db
  .select()
  .from(users)
  .where(
    and(
      subscribedOnly ? users.subscribed : undefined,
      companyID ? eq(users.companyID, companyID) : undefined,
      userID ? eq(users.id, userID) : undefined,
    )
  )

See also #1644 (comment) for stacking multiple .where() calls.

Wouldn't it be more efficient to do

db
  .select()
  .from(users)
  .where(
    and(
      subscribedOnly && users.subscribed,
      companyID && eq(users.companyID, companyID),
      userID && eq(users.id, userID),
    )
  )

Where it would just resolve the expression and just ignore the falsy values?

[orlovol]

Wouldn't it be more efficient to do

db
  .select()
  .from(users)
  .where(
    and(
      subscribedOnly && users.subscribed,
      companyID && eq(users.companyID, companyID),
      userID && eq(users.id, userID),
    )
  )

Where it would just resolve the expression and just ignore the falsy values?

Nope, since falsy values would still go into filter: where '', where false, which would make query return nothing. While undefined values would be excluded from the filters array.