3 replies

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.

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)],
  })

How are you supposed to do multiple filters with Drizzle?

Similar Threads

How are you supposed to do multiple filters with Drizzle?

Similar Threads

Similar Threads

Similar Threads