Drizzle Team•15mo ago

How are you supposed to do multiple filters with Drizzle?

I've been using Drizzle for my project for a few weeks now. But I have run into a nightmare trying to use multiple filters with Drizzle. I have a table "events" I need to filter based on: - Event name (if a search query is provided) - Dates - Other field based filters And the only way I got it to work is this horrible SQL mess:

const events = await db.query.events.findMany({
    where: (allEvents, { sql, and }) => {
      if (!query && fromDate.getTime() !== fromDate.getTime()) return

      // This works but it's not scalable :(

      // if there's only a query, search by title
      if (query && fromDate.getTime() !== fromDate.getTime()) {
        return sql`${allEvents.title} ilike ${`%${query}%`}`
        // if there's only a date, search by date
      } else if (!query && fromDate.getTime() === fromDate.getTime()) {
        return sql`${allEvents.dateTimeStart} >= ${formattedFromDate} AND ${allEvents.dateTimeStart} <= ${formattedToDate}`
      } else {
        // if there's both, filter by both
        return and(
          sql`${allEvents.title} ilike ${`%${query}%`}`,
          sql`${allEvents.dateTimeStart} >= ${formattedFromDate} AND ${allEvents.dateTimeStart} <= ${formattedToDate}`
        )
      }
    },
    orderBy: [asc(schema.events.dateTimeStart)],
  })

const events = await db.query.events.findMany({
    where: (allEvents, { sql, and }) => {
      if (!query && fromDate.getTime() !== fromDate.getTime()) return

      // This works but it's not scalable :(

      // if there's only a query, search by title
      if (query && fromDate.getTime() !== fromDate.getTime()) {
        return sql`${allEvents.title} ilike ${`%${query}%`}`
        // if there's only a date, search by date
      } else if (!query && fromDate.getTime() === fromDate.getTime()) {
        return sql`${allEvents.dateTimeStart} >= ${formattedFromDate} AND ${allEvents.dateTimeStart} <= ${formattedToDate}`
      } else {
        // if there's both, filter by both
        return and(
          sql`${allEvents.title} ilike ${`%${query}%`}`,
          sql`${allEvents.dateTimeStart} >= ${formattedFromDate} AND ${allEvents.dateTimeStart} <= ${formattedToDate}`
        )
      }
    },
    orderBy: [asc(schema.events.dateTimeStart)],
  })

This only works for up to 2 filters, it was horrible to write and horrible to read. Surely there must be a better way to handle multiple optional filters in Drizzle? I couldn't find anything useful in the documentation, Please help.

2 Replies

Luxaritas•15mo ago

The drizzle and() function will omit any argument that’s undefined, so you could conditionally pass it either a filter or undefined Or you could build up an array of filters that you pass to and with a spread

Mark LOP•15mo ago

@Luxaritas oh I didn't know "and" could take more than 2 statements. That'll help a lot 🙏

Gaming

Programming

How are you supposed to do multiple filters with Drizzle?