ijd Posts - Answer Overflow

ijd

•Created by ijd on 6/23/2023 in #help

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?

10 replies

Gaming

Programming