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()

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()

RANDOM()

RANDOM()

bit

danfasciaOP•5/22/24, 8:53 PM

I think I worked it out, but would appreciate confirmation.

I think it needs ternaries with undefined undefined as the null option

Sillvva•5/22/24, 9:17 PM

Don't use JS &&&&, because that does not return the whole condition. What that would do is:

If any of the conditions are
```
undefined
```
```
undefined
```
, it would return
```
undefined
```
```
undefined
```
to where. 1 && undefined && 3 === undefined1 && undefined && 3 === undefined
If all of the conditions are valid, it would only return the last condition (uid != notUid) 1 && 2 && 3 === 31 && 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•5/22/24, 9:35 PM

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()

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

Similar Threads

Similar Threads

Similar Threads