Drizzle Team•2y ago

Optional filtering

I'm trying to figure out the right pattern to reproduce an SQL query where the user can optionally filter based on a column value. For example, assuming a chunks table with a documentId column, you could write the query

SELECT * FROM chunks WHERE ($1 IS NULL OR chunks.documentId = $1);

SELECT * FROM chunks WHERE ($1 IS NULL OR chunks.documentId = $1);

The corresponding pattern in Drizzle is not obvious because neither isNull or and/or can accept JS values as options, only SQL columns. So the following pattern doesn't work:

export async function embedNChunks(
  args: { n: number; documentId?: string, timeoutMs?: number },
  opts: ContextOptions
): Promise<number> {
  const chunksToEmbed = await opts.db
    .select()
    .from(chunks)
    .where(
      and(
        // not embedded yet
        isNull(chunks.embedding),
        // ----------------------
        // PART THAT DOESN'T WORK
        or(
          isNull(args.documentId), // doesn't compile - not a column
          eq(chunks.documentId, args.documentId)
        ),
        // ----------------------
        // bootleg job runner system - if no embedding 15s after last attempt,
        // pick up with next job cycle
        or(
          isNull(chunks.embeddedAt),
          lt(
            chunks.embeddedAt,
            new Date(Date.now() - (args.timeoutMs ?? DEFAULT_TIMEOUT_MS))
          )
        )
      )
    )
    .limit(args.n);
...

export async function embedNChunks(
  args: { n: number; documentId?: string, timeoutMs?: number },
  opts: ContextOptions
): Promise<number> {
  const chunksToEmbed = await opts.db
    .select()
    .from(chunks)
    .where(
      and(
        // not embedded yet
        isNull(chunks.embedding),
        // ----------------------
        // PART THAT DOESN'T WORK
        or(
          isNull(args.documentId), // doesn't compile - not a column
          eq(chunks.documentId, args.documentId)
        ),
        // ----------------------
        // bootleg job runner system - if no embedding 15s after last attempt,
        // pick up with next job cycle
        or(
          isNull(chunks.embeddedAt),
          lt(
            chunks.embeddedAt,
            new Date(Date.now() - (args.timeoutMs ?? DEFAULT_TIMEOUT_MS))
          )
        )
      )
    )
    .limit(args.n);
...

Does anyone have a good way to do this?

2 Replies

ijdOP•2y ago

Dug through some earlier threads, apparently conditionally constructing the where array is the way to go! so in this case:

  const where: SQL[] = [
    // not embedded yet
    isNull(chunks.embedding),
    // bootleg job runner system - if no embedding 15s after last attempt,
    // pick up with next job cycle
    or(
      isNull(chunks.embeddedAt),
      lt(
        chunks.embeddedAt,
        new Date(Date.now() - (args.timeoutMs ?? DEFAULT_TIMEOUT_MS))
      )
    ),
  ];
  if (args.documentId) {
    where.push(eq(chunks.documentId, args.documentId));
  }

  const where: SQL[] = [
    // not embedded yet
    isNull(chunks.embedding),
    // bootleg job runner system - if no embedding 15s after last attempt,
    // pick up with next job cycle
    or(
      isNull(chunks.embeddedAt),
      lt(
        chunks.embeddedAt,
        new Date(Date.now() - (args.timeoutMs ?? DEFAULT_TIMEOUT_MS))
      )
    ),
  ];
  if (args.documentId) {
    where.push(eq(chunks.documentId, args.documentId));
  }

then just select()...where(...where)

finn•2y ago

you could probably do this as well

sql`${args.documentId} = null`

sql`${args.documentId} = null`

or something similar might need sql.raw() idk but if that works then cool you might need to transform it via json but yeah probably less ideal

Gaming

Programming

Optional filtering