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_.•9mo ago

Hey, did you find a solution? Have same question

Sabbir Hossain Shuvo•9mo 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•9mo 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•9mo ago

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

Mykhailo•9mo ago

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

균어OP•9mo 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?