Drizzle Team•13mo ago

Appending SQL chunks to a query

Hey everyone, I'm trying to see if there is a way where I can build SQL queries conditionally (based on some search params I get)

10 Replies

Mr VolcanoOP•13mo ago

So far I've found this but I'm wondering if there's another way (ideally one that doesn't use raw sql) https://orm.drizzle.team/docs/sql#sqlappend

Magical sql operator 🪄 - DrizzleORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind

Angelelz•13mo ago

Can you share an example of what you want to do?

Mr VolcanoOP•13mo ago

yes ofc! So I have a NextJS route that gives me some query params like this: searchParams: { [key: string]: string | string[] | undefined }; Query params can be: campaignId and status . A special case is here is that can be multiple "status" params. So: "status=ACTIVE" and "Status=INACTIVE" etc I'm now wondering how I can see which params are present, what are their values, and make queries to the db based on those.

Angelelz•13mo ago

What does you query look like? Is that going to be in a where(and(...)) clause?

tomri•13mo ago

its unclear, but this what i could get: you are trying to read queries in nextjs search queries can be accessed via 'request' object from route. you can acces them like this:

import { NextRequest } from 'next/server'
 
export function GET(request: NextRequest) {
  const { campaignId, status } = request.nextUrl.searchParams
  console.log(compaignId);
  // further logic
}

import { NextRequest } from 'next/server'
 
export function GET(request: NextRequest) {
  const { campaignId, status } = request.nextUrl.searchParams
  console.log(compaignId);
  // further logic
}

Mr VolcanoOP•13mo ago

Yeap, in some cases it will have to be Angelelz Yeah, I'm at this point right now. I can get the related user that belongs to a campaign, now Im trying to see how I can get the campaign users where their status are status1 OR status2 etc

Angelelz•13mo ago

db
  .select()
  .from(yourTable)
  .where(and([
    eq(yourTable.yourColumn, yourParam),
    campainId ? eq(yourTable.yourColumn, campainId) : undefined,
    status ? eq(yourTable.yourColumn, status) : undefined
  ]))

db
  .select()
  .from(yourTable)
  .where(and([
    eq(yourTable.yourColumn, yourParam),
    campainId ? eq(yourTable.yourColumn, campainId) : undefined,
    status ? eq(yourTable.yourColumn, status) : undefined
  ]))

Mr VolcanoOP•13mo ago

Looks good. So it's pretty much the same even if I add the ArrayIn operator, right ? Because thats what I'll need to use to filter based on multiple "status" params

tomri•13mo ago

db
  .select()
  .from(table)
  .where(or(eq(user.status,status1),eq(user.status, staus2)));

db
  .select()
  .from(table)
  .where(or(eq(user.status,status1),eq(user.status, staus2)));

Mr VolcanoOP•13mo ago

Thank you guys, I will try and take it from here I really appreciate your help 😉

Gaming

Programming

Appending SQL chunks to a query