Drizzle Team•6mo ago

Is there a way to just return a specific column?

I have a DB that holds voting results and I want to grab all the weeks that have votes. I.e. if there are votes for weeks 0-5, I just want to return 0, 1, 2, 3, 4, 5 and not the entire row for each of those weeks.

6 Replies

rphlmr ⚡•6mo ago

👋 You have partial select https://orm.drizzle.team/docs/select#partial-select and for query API https://orm.drizzle.team/docs/rqb#partial-fields-select Is it what you are looking for?

Drizzle ORM - Select

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

Drizzle ORM - Query

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

jsingleton37OP•5mo ago

@Raphaël M (@rphlmr) ⚡ I ended up doing this:

export async function getWeeksThatHaveVotes({
  year,
  division,
}: {
  year: number
  division: string
}) {
  const finalRankings = await db.query.weeklyFinalRankings.findMany({
    where: (model, { eq, and }) => and(eq(model.year, year), eq(model.division, division)),
  })

  const weeksArray = finalRankings.map((finalRanking) => finalRanking.week)

  return weeksArray
}

export async function getWeeksThatHaveVotes({
  year,
  division,
}: {
  year: number
  division: string
}) {
  const finalRankings = await db.query.weeklyFinalRankings.findMany({
    where: (model, { eq, and }) => and(eq(model.year, year), eq(model.division, division)),
  })

  const weeksArray = finalRankings.map((finalRanking) => finalRanking.week)

  return weeksArray
}

Not sure if this is correct or if one of your suggestions would be cleaner/more efficient. 🤔

rphlmr ⚡•5mo ago

That’s good. You can use the columns property too to select only “week” in the query. It reduces the amount of data you grab from the database (a micro-optimization, but it is less data to pay for depending on your provider quota).

jsingleton37OP•5mo ago

Oh awesome! So I can just do something like

export async function getWeeksThatHaveVotes({
  year,
  division,
}: {
  year: number
  division: string
}) {
  const weeksWithVotes = await db.query.weeklyFinalRankings.findMany({
    columns: {
      week: true,
    },
    where: (model, { eq, and }) => and(eq(model.year, year), eq(model.division, division)),
  })

  return weeksWithVotes
}

export async function getWeeksThatHaveVotes({
  year,
  division,
}: {
  year: number
  division: string
}) {
  const weeksWithVotes = await db.query.weeklyFinalRankings.findMany({
    columns: {
      week: true,
    },
    where: (model, { eq, and }) => and(eq(model.year, year), eq(model.division, division)),
  })

  return weeksWithVotes
}

And the response just looks like [ { week: 0 } ]

rphlmr ⚡•5mo ago

Yes, and you can still use your previous code to create the weeksArray if the final shape doesn’t fit your needs. This is what I personally do: I try to ensure my function consumer is not aware of the database shape or the underlying raw fetched data shape. Of course it’s up to you

jsingleton37OP•5mo ago

I was able to just adjust the frontend code to accommodate the new structure. It was just being passed to a select component from ShadCN UI

Gaming

Programming

Is there a way to just return a specific column?