[FEATURE]: Dynamic selection without compromise

[cyrilchapon]

Feature hasn't been suggested before.

• [X] I have verified this feature I'm about to request hasn't been suggested before.

(closed one, and related ones but not that complete)

Describe the enhancement you want to request

Hey,

This is a reopening of dynamic selection topic, with a pretty decent use-case I believe.

I'm making yet another one full featured REST API, which always take the same query parameters

{
  filter: // some filtering expression
  sortBy: // some sorting expression
}

{
  items: // data array,
  pagination: {
    take: number,
    skip: number,
    total: // the total count of unpaginated items
  }
}

This inevitably and systematically leads to 2 queries which are almost the same, but not exactly :

const items = await drizzle.
  .with(/* some CTES */)
  .select() // select * from all tables
  .from(a)
  .join(b, eq(a.id, b.a_id)
  .where(where) // filtering
  .limit(query.take) // pagination
  .offset(query.skip) // pagination
  .orderBy(...sortBy) // ordering
  .execute()

const count = await drizzle.
  .with(/* some CTES */)
  .select({ count: count(a.id) }) // select the count
  .from(a)
  .join(b, eq(a.id, b.a_id)
  .where(where) // filtering
  // no pagination and ordering
  .execute()

From there we can observe all of the :

• with CTEs
• from
• join
• where are common to both queries

---

From many attempts, and readings (#948, #2954, #1817), I'm struggling at factorizing all the common parts.

More specifically :

• I managed to factorize the .with CTEs basically like so :

  const _withSaasDirectoryAggregates = (drizzle: Drizzle) =>
  drizzle.with(
  withLicensesCount,
  withExpensesCost,
  withGroupsCount,
  withAccessRequestsCount,
  withMergedLicensesSources
  )

• I factorized the where by just instanciating a SQL<bool> producing function

• I'm struggling at factorizing the from + join with different .select()s

---

I'm coming from kysely where I was basically doing :

kysely
  .with()
  .from()
  .join()
  .limit()
  .offset()
  .where()
  .select() // select at the very end

which were allowing insane modularity like so :

const commonQuery = kysely
  .with()
  .from()
  .join()
  .where()

const itemsQuery = commonQuery
  .limit()
  .offset()
  .select(complexSelect)

const countQuery = commonQuery
  .select(count)

Is there any known strategy to achieve the same thing with Drizzle, or any consideration to be able to build a dynamic select or even moving the select at the end of the statement ?

[cyrilchapon]

I found a way, which sounds more like a workaround — given the very complex syntax — but that works :

// Common CTEs
const _withs = (drizzle: Drizzle) =>
  drizzle.with(
    cte1,
    cte2,
  )

// Common joins
export const _joins = <T extends MySqlSelect>(qb: T) => {
  return qb
    .innerJoin(table2, eq(table1.id, table2.table1Id))
    .leftJoin(table3, eq(table2.id, table3.table2Id))
}

// Select builders
export const _itemsSelectBuilder = (drizzle: Drizzle) =>
  _withs(drizzle).select()

export const _countSelectBuilder = (drizzle: Drizzle) =>
  _withs(drizzle).select({
    count: count(table1.id),
  })

// Query builders
export const queryItems = (drizzle: Drizzle) =>
  _joins(
    _itemsSelectBuilder(drizzle)
      .from(table1)
      .$dynamic()
  )

export const queryExtendedSaasDirectoryCount = (drizzle: Drizzle) =>
  _joins(
    _countSelectBuilder(drizzle)
      .from(table1)
      .$dynamic()
  )