danfascia Posts - Answer Overflow

danfascia

•Created by danfascia on 5/22/2024 in #help

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

4 replies

DTDrizzle Team

•Created by danfascia on 5/21/2024 in #help

Selecting a random record (libSQL SQLite)

I have a table of questions and I would like to select a single random record to create a quiz. In raw SQL I would use

SELECT * FROM questions ORDER BY RANDOM() LIMIT(1)

SELECT * FROM questions ORDER BY RANDOM() LIMIT(1)

I have no idea how to model this into a drizzle query as there only seem to be asc and desc methods on orderBy - I am using libSQL Thanks

4 replies

Gaming

Programming