Drizzle Team•12mo ago

Modified select() on an existing query example

Hey, I would like to reuse first query with some left joins etc. to obtain results count with second query I found some posts, and it seems its possible, but cant get it working. I just need to alternate select() to other fields, more precisely to a simple sql count expression.

// random query with left join
    const query = db
      .select({
        id: documentLine.id,
        documentId: documentLine.documentId,
        productVariant: {
          id: productVariant.id,
          fullName: productVariant.fullName,
        },
      })
      .from(documentLine)
      .leftJoin(
        productVariant,
        eq(productVariant.id, documentLine.productVariantId)
      )
// it applies fileters, ordering, pagination
    let paginatedResults = await getPaginatedResponse(query, filters, ordering, limit, offset)

// need to copy query with all filters and more important with joins
    let countQuery = query.$dynamic().where(and(...filters))

// sample attempt to alternate select
    let count = countQuery._.selectedFields({ count: sql<string>`count(*)` })

// random query with left join
    const query = db
      .select({
        id: documentLine.id,
        documentId: documentLine.documentId,
        productVariant: {
          id: productVariant.id,
          fullName: productVariant.fullName,
        },
      })
      .from(documentLine)
      .leftJoin(
        productVariant,
        eq(productVariant.id, documentLine.productVariantId)
      )
// it applies fileters, ordering, pagination
    let paginatedResults = await getPaginatedResponse(query, filters, ordering, limit, offset)

// need to copy query with all filters and more important with joins
    let countQuery = query.$dynamic().where(and(...filters))

// sample attempt to alternate select
    let count = countQuery._.selectedFields({ count: sql<string>`count(*)` })

https://discord.com/channels/1043890932593987624/1104858040936706140 https://github.com/drizzle-team/drizzle-orm/issues/561

GitHub

Expose query builder config · Issue #561 · drizzle-team/drizzle-orm

Based on Discord thread https://discord.com/channels/1043890932593987624/1104858040936706140

1 Reply

tzezarOP•12mo ago

all this to make helper function and avoid manually creating second query like this:

    const countQuery = await db
      .select({
        count: sql<string>`count(*)`,
      })
      .from(documentLine)
      .leftJoin(
        productVariant,
        eq(productVariant.id, documentLine.productVariantId)
      ).where(and(...filters))
    let count = +countQuery[0].count

    const countQuery = await db
      .select({
        count: sql<string>`count(*)`,
      })
      .from(documentLine)
      .leftJoin(
        productVariant,
        eq(productVariant.id, documentLine.productVariantId)
      ).where(and(...filters))
    let count = +countQuery[0].count

Gaming

Programming

Modified select() on an existing query example