Drizzle Team•14mo 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•14mo 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•14mo ago

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

Mr VolcanoOP•14mo 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•14mo ago

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

tomri•14mo 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•14mo 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•14mo 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•14mo 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•14mo 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•14mo 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