How to get findMany with count?

I have following query

    const result = await this.db.query.serviceOrders.findMany({
      where: and(...wheres),
      with: {
        vehicle: {
          with: {
            make: true,
            model: true,
          },
        },
        customer: true,
        branch: {
          columns: {
            name: true,
          },
        },
      },
      orderBy: [desc(serviceOrders.startedDate)],
    });

    const result = await this.db.query.serviceOrders.findMany({
      where: and(...wheres),
      with: {
        vehicle: {
          with: {
            make: true,
            model: true,
          },
        },
        customer: true,
        branch: {
          columns: {
            name: true,
          },
        },
      },
      orderBy: [desc(serviceOrders.startedDate)],
    });

and have following relation

export const serviceOrdersRelation = relations(
  serviceOrders,
  ({ one, many }) => ({
    vehicle: one(vehicles, {
      fields: [serviceOrders.vehicleId],
      references: [vehicles.id],
    }),
    customer: one(customers, {
      fields: [serviceOrders.customerId],
      references: [customers.id],
    }),
    branch: one(branches, {
      fields: [serviceOrders.branchId],
      references: [branches.id],
    }),
    serviceOrderItems: many(serviceOrderItems),
    payments: many(serviceOrderPayments),
  }),
);

export const serviceOrdersRelation = relations(
  serviceOrders,
  ({ one, many }) => ({
    vehicle: one(vehicles, {
      fields: [serviceOrders.vehicleId],
      references: [vehicles.id],
    }),
    customer: one(customers, {
      fields: [serviceOrders.customerId],
      references: [customers.id],
    }),
    branch: one(branches, {
      fields: [serviceOrders.branchId],
      references: [branches.id],
    }),
    serviceOrderItems: many(serviceOrderItems),
    payments: many(serviceOrderPayments),
  }),
);

how to get serviceOrders with serviceOrderItems count?

6 Replies

salomon_.•10mo ago

Hey, did you find a solution? Have same question

Sabbir Hossain Shuvo•10mo ago

here is an example that I already did.

const [lengths, profiles] = await Promise.all([
    db.select({ count: sql<number>`count(*)` }).from(users),
    searchParams
      ? db
          .select()
          .from(users)
          .where(
            or(
              ilike(users.name, `%${searchParams}%`),
              ilike(users.location, `%${searchParams}%`),
            ),
          )
          .limit(itemPerPage)
          .offset(offset)
      : db
          .select()
          .from(users)
          .orderBy(sql.raw("RANDOM()"))
          .limit(itemPerPage)
          .offset(offset),
  ]);
  const count = lengths[0].count; // this will b e

const [lengths, profiles] = await Promise.all([
    db.select({ count: sql<number>`count(*)` }).from(users),
    searchParams
      ? db
          .select()
          .from(users)
          .where(
            or(
              ilike(users.name, `%${searchParams}%`),
              ilike(users.location, `%${searchParams}%`),
            ),
          )
          .limit(itemPerPage)
          .offset(offset)
      : db
          .select()
          .from(users)
          .orderBy(sql.raw("RANDOM()"))
          .limit(itemPerPage)
          .offset(offset),
  ]);
  const count = lengths[0].count; // this will b e

Sabbir Hossain Shuvo•10mo ago

Also if you want you can check out this website to get live demo. https://finddevs.vercel.app/

Find Devs - Discover All Developers From The Programming World

Find Dev is kind of Developers Fair. An open source platform to find all the developers in the world.

균어OP•10mo ago

i want to use findMany not select if i use select() function i need to manually manage calling relations

Mykhailo•10mo ago

Hello guys, as for now, Drizzle does not support aggregations with relational queries, so now you can only do this using .select()

균어OP•10mo ago

i found solution using sub queries

    const deleteAbleQuery = this.db
      .select({
        count: count(),
      })
      .from(serviceOrderItems)
      .where(eq(serviceOrderItems.serviceOrderId, sql`"serviceOrders"."id"`));

    const result = await this.db.query.serviceOrders.findMany({
      where: and(...wheres),
      extras: {
        ...(input.showIsDeleteAble
          ? {
              isDeleteAble: sql<boolean>`not ${deleteAbleQuery} > 0`.as(
                'is_delete_able',
              ),
            }
          : {}),
        count: sql`count(*) over()`.mapWith(Number).as('count'),
      },
      with: {
        vehicle: {
          with: {
            make: true,
            model: true,
          },
        },
        customer: true,
        branch: {
          columns: {
            name: true,
          },
        },
      },
      orderBy: [desc(serviceOrders.startedDate)],
      limit: input.size,
      offset: input.size * (input.page - 1),
    });

    const deleteAbleQuery = this.db
      .select({
        count: count(),
      })
      .from(serviceOrderItems)
      .where(eq(serviceOrderItems.serviceOrderId, sql`"serviceOrders"."id"`));

    const result = await this.db.query.serviceOrders.findMany({
      where: and(...wheres),
      extras: {
        ...(input.showIsDeleteAble
          ? {
              isDeleteAble: sql<boolean>`not ${deleteAbleQuery} > 0`.as(
                'is_delete_able',
              ),
            }
          : {}),
        count: sql`count(*) over()`.mapWith(Number).as('count'),
      },
      with: {
        vehicle: {
          with: {
            make: true,
            model: true,
          },
        },
        customer: true,
        branch: {
          columns: {
            name: true,
          },
        },
      },
      orderBy: [desc(serviceOrders.startedDate)],
      limit: input.size,
      offset: input.size * (input.page - 1),
    });

i think this is not the best solution but using .select() it's hard to control relation between tables

Gaming

Programming

How to get findMany with count?