Drizzle Team•7mo ago

Help with conditional parameters in a .where () statement please

I've got URL searchParams for category, difficulty and not (which is a record to exclude). The parameters are optional and are used as filters in my question bank app. I'm struggling to build a drizzle query that conditionally supports the presence of these parameters and I wonder if someone can help.

question = await db.select().from(Question)
.where(
  (category && (sql`${Question.category} = ${category}`)) && 
  (difficulty && (sql`${Question.difficulty} >= ${difficulty}`)) && 
  (notUid && (sql`${Question.uid} != ${notUid}`))
)
.orderBy(sql`RANDOM()`)
.limit(1)
.get()

question = await db.select().from(Question)
.where(
  (category && (sql`${Question.category} = ${category}`)) && 
  (difficulty && (sql`${Question.difficulty} >= ${difficulty}`)) && 
  (notUid && (sql`${Question.uid} != ${notUid}`))
)
.orderBy(sql`RANDOM()`)
.limit(1)
.get()

This is what I have that doesn't work. Note thanks to @Mykhailo for the

RANDOM()

RANDOM()

bit

3 Replies

danfasciaOP•7mo ago

I think I worked it out, but would appreciate confirmation. I think it needs ternaries with undefined as the null option

Sillvva•7mo ago

Don't use JS &&, because that does not return the whole condition. What that would do is: - If any of the conditions are undefined, it would return undefined to where. 1 && undefined && 3 === undefined - If all of the conditions are valid, it would only return the last condition (uid != notUid) 1 && 2 && 3 === 3 Use the built-in Drizzle filters.

import { and, eq, gte, ne, sql } from 'drizzle-orm'

db.select().from(Question)
.where(
  and(
    category && eq(Question.category, category),
    difficulty && gte(Question.difficulty, difficulty),
    notUid && ne(Question.uid, notUid),
  )
)
.orderBy(sql`RANDOM()`)
.limit(1)
.get()

import { and, eq, gte, ne, sql } from 'drizzle-orm'

db.select().from(Question)
.where(
  and(
    category && eq(Question.category, category),
    difficulty && gte(Question.difficulty, difficulty),
    notUid && ne(Question.uid, notUid),
  )
)
.orderBy(sql`RANDOM()`)
.limit(1)
.get()

danfasciaOP•7mo ago

Thank you, I ended up with this which is remarkably similar except I use ternaries. Yours is a bit terser

question = await db.select().from(Question)
.where(
  and(
    (category ? eq(Question.category, category) : undefined),
    (difficulty ? eq(Question.difficulty, difficulty) : undefined),
    (notUid ? ne(Question.uid, notUid) : undefined)
  )
)
.orderBy(sql`RANDOM()`)
.limit(1)
.get()

question = await db.select().from(Question)
.where(
  and(
    (category ? eq(Question.category, category) : undefined),
    (difficulty ? eq(Question.difficulty, difficulty) : undefined),
    (notUid ? ne(Question.uid, notUid) : undefined)
  )
)
.orderBy(sql`RANDOM()`)
.limit(1)
.get()

Gaming

Programming

Help with conditional parameters in a .where () statement please