Drizzle Team•12mo ago

Chaining/combining $dynamic query functions

Hi everyone - I'm trying to take advantage of the new dynamic query building feature to simplify the way I query my db for displaying data in a table. I've already implemented a nice helper function to add ORDER BY to a query, e.g:

const result = await withOrderBy(qb, {
  orderBy: 'name',
  order: 'desc'
})

And now I'd like to do the same for pagination, just like in Drizzle's docs:

const result = await withPagination(qb, {
  page: 2,
  pageSize: 10
})

What's the cleanest way to use both of these helpers? Nesting them does technically work:

const result = await withPagination(
  withOrderBy(qb, {
    ...args
  }),
  {page: 2, pageSize: 10}
)

But that seems pretty hard to read and work with. Has anyone figured out a better way? Thank you!

7 Replies

Angelelz•12mo ago

I think this is pretty good

felixv•8mo ago

@terryball curious was the nesting what you used in the end? 🤔

terryball•8mo ago

Yep, this is what I stuck with

warflash•8mo ago

Just stumbled across this, would you mind sharing your withOrderBy function and what/how you pass the base query in? Tried achieving the same but failed miserably 🙏

terryball•8mo ago

Sure - I can't promise that this is the best way to do it but here you go:

export function withOrderBy<T extends PgSelect>(
  query: T,
  {
    order = 'asc',
    orderBy,
    sortMap,
    nullsLast = true,
  }: {
    order?: 'asc' | 'desc'
    orderBy?: string
    sortMap: Record<string, PgColumn | SQL | SQL.Aliased> &
      Record<'default', PgColumn | SQL>
    nullsLast?: boolean
  }
) {
  const sortOrder = order === 'asc' ? asc : desc

  const orderBySql = orderBy
    ? sortMap[orderBy] || sortMap.default
    : sortMap.default

  return query.orderBy(
    sortOrder(orderBySql).append(
      sql.raw(

nulls ${nullsLast ? 'last' : 'first'}

)
    )
  )
}

And here's how I use it

  withTableFeatures(qb, {
    order: 'desc',
    orderBy: params.orderBy,
    sortMap: {
      firstName: users.firstName,
      timestamp: sessions.timestamp,
      vendorName: vendors.name,
      default: sessions.timestamp,
    },
  })

Where qb is any drizzle query ending with .$dynamic() In this line: const sortOrder = order === 'asc' ? asc : desc asc and desc are the drizzle-provided sort functions

warflash•8mo ago

Thank you! This is all pretty close to what I already have actually, the only difference is the sortMap. Does that mean you can't actually simply do asc(sql'${mySortByParamFromApi}')? Creating a manual map for every query seems very cumbersome 🤔 Okay yeah that seems to actually be the case. That's kind of crazy. Like how would one go about doing

select
*,
sum(ratingTable.rating) as rating
from myTable
left join ratingTable (logic here)
group by myTable.id
order by rating desc

select
*,
sum(ratingTable.rating) as rating
from myTable
left join ratingTable (logic here)
group by myTable.id
order by rating desc

then, where the selected field is not part of any table definition :confus:

felixv•8mo ago

@terryball super useful, thanks for sharing this 🙏 Do you happen to also have a filterBy or filter column logic function 😄 been looking through discord but couldn't find anything. I found this awesome repo https://github.com/sadmann7/shadcn-table and now trying to optimize all the table functions 🤓

GitHub

GitHub - sadmann7/shadcn-table: shadcn table component with server-...

shadcn table component with server-side sorting, filtering, and pagination. - sadmann7/shadcn-table

Gaming

Programming

Chaining/combining $dynamic query functions