균어 Comments - Answer Overflow

균어

Explore posts from servers

•Created by Perny on 3/12/2024 in #help

How can I get nested data to be inside the parent object

when it was possibly to use alias in left join i won't be 3 query but i searched in drizzle types i cannot find alias props in left join method or you can use sql``

6 replies

DTDrizzle Team

•Created by Perny on 3/12/2024 in #help

How can I get nested data to be inside the parent object

i think you can use like this

    const homeTeam = this.db.$with('home_team').as(
      this.db.select().from(teamTable).where(eq(
        teamTable.id,
        gameTable.homeTeamId
      ))
    )

    const awayTeam = this.db.$with('away_team').as(
      this.db.select().from(teamTable).where(eq(
        teamTable.id,
        gameTable.awayTeamId
      ))
    )

    const result = await this.db.with(homeTeam, awayTeam).select({
      id: gameTable.id,
      homeTeam: this.db.select().from(homeTeam),
      awayTeam: this.db.select().from(awayTeam),
    }).from(gameTable)

    const homeTeam = this.db.$with('home_team').as(
      this.db.select().from(teamTable).where(eq(
        teamTable.id,
        gameTable.homeTeamId
      ))
    )

    const awayTeam = this.db.$with('away_team').as(
      this.db.select().from(teamTable).where(eq(
        teamTable.id,
        gameTable.awayTeamId
      ))
    )

    const result = await this.db.with(homeTeam, awayTeam).select({
      id: gameTable.id,
      homeTeam: this.db.select().from(homeTeam),
      awayTeam: this.db.select().from(awayTeam),
    }).from(gameTable)

i have ways using sub query

6 replies

DTDrizzle Team

•Created by 균어 on 3/5/2024 in #help

How to get findMany with count?

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

9 replies

DTDrizzle Team

•Created by 균어 on 3/5/2024 in #help

How to get findMany with count?

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),
    });

9 replies

DTDrizzle Team

•Created by 균어 on 3/5/2024 in #help

How to get findMany with count?

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

9 replies

DTDrizzle Team

•Created by 균어 on 3/5/2024 in #help

How to get findMany with count?

i want to use findMany not select

9 replies

DTDrizzle Team

•Created by 균어 on 2/9/2024 in #help

PgTable type how to get at least have id field?

i need to check type is have id field

2 replies

DTDrizzle Team

•Created by jonpage on 12/1/2023 in #help

Update From

you can use just

.set({deletedAt: new Date() OR new Date().toString()})

.set({deletedAt: new Date() OR new Date().toString()})

5 replies

DTDrizzle Team

•Created by Zamiel on 11/27/2023 in #help

What are the Drizzle conventions for giving names to Drizzle return types?

you can use where in query

    const rows = await this.db.query.users.findFirst({
      columns: {
        id: usersTable.id,
        username: usersTable.username,
        passwordHash: usersTable.passwordHash,
      },
      where: eq(usersTable.username, username)
    })

    const rows = await this.db.query.users.findFirst({
      columns: {
        id: usersTable.id,
        username: usersTable.username,
        passwordHash: usersTable.passwordHash,
      },
      where: eq(usersTable.username, username)
    })

40 replies

DTDrizzle Team

•Created by Zamiel on 11/27/2023 in #help

What are the Drizzle conventions for giving names to Drizzle return types?

you can use

const rows = await db.query.usersTable.findFirst()

const rows = await db.query.usersTable.findFirst()

40 replies

DTDrizzle Team

•Created by 균어 on 11/24/2023 in #help

Postgresql Join select columns with case

  async findByServiceOrderId(serviceOrderId: number | number[]) {
    const base = await this.db
      .select({
        ...getTableColumns(serviceOrderItems),
        serviceType: serviceTypes,
      })
      .from(serviceOrderItems)
      .where(
        typeof serviceOrderId === 'number'
          ? eq(serviceOrderItems.serviceOrderId, serviceOrderId)
          : inArray(serviceOrderItems.serviceOrderId, serviceOrderId),
      )
      .leftJoin(
        serviceTypes,
        eq(serviceTypes.id, serviceOrderItems.serviceTypeId),
      );

    const inventoryItems =
      await this.inventoryItemsService.findByServiceOrderIds(
        base.map((i) => i.id),
      );

    return base.map((i) => ({
      ...i,
      inventoryItem: inventoryItems.find((ii) => ii.id === i.inventoryItemId),
    }));
  }

  async findByServiceOrderId(serviceOrderId: number | number[]) {
    const base = await this.db
      .select({
        ...getTableColumns(serviceOrderItems),
        serviceType: serviceTypes,
      })
      .from(serviceOrderItems)
      .where(
        typeof serviceOrderId === 'number'
          ? eq(serviceOrderItems.serviceOrderId, serviceOrderId)
          : inArray(serviceOrderItems.serviceOrderId, serviceOrderId),
      )
      .leftJoin(
        serviceTypes,
        eq(serviceTypes.id, serviceOrderItems.serviceTypeId),
      );

    const inventoryItems =
      await this.inventoryItemsService.findByServiceOrderIds(
        base.map((i) => i.id),
      );

    return base.map((i) => ({
      ...i,
      inventoryItem: inventoryItems.find((ii) => ii.id === i.inventoryItemId),
    }));
  }

  async findByServiceOrderIds(serviceOrderIds: number[]) {
    const sq = this.db
      .select({
        id: serviceOrderItems.inventoryItemId,
      })
      .from(serviceOrderItems)
      .where(inArray(serviceOrderItems.id, serviceOrderIds));

    return this.db
      .select(this.selectBase)
      .from(inventoryItems)
      .leftJoin(products, eq(products.id, inventoryItems.productId))
      .leftJoin(
        serviceOrderItems,
        eq(serviceOrderItems.inventoryItemId, inventoryItems.id),
      )
      .where(inArray(inventoryItems.id, sq));
  }

  async findByServiceOrderIds(serviceOrderIds: number[]) {
    const sq = this.db
      .select({
        id: serviceOrderItems.inventoryItemId,
      })
      .from(serviceOrderItems)
      .where(inArray(serviceOrderItems.id, serviceOrderIds));

    return this.db
      .select(this.selectBase)
      .from(inventoryItems)
      .leftJoin(products, eq(products.id, inventoryItems.productId))
      .leftJoin(
        serviceOrderItems,
        eq(serviceOrderItems.inventoryItemId, inventoryItems.id),
      )
      .where(inArray(inventoryItems.id, sq));
  }

i want to simplify this code to 1 query

8 replies

DTDrizzle Team

•Created by 균어 on 11/24/2023 in #help

Postgresql Join select columns with case

is any way to simplify this code?

  async findByVehicle(vehicleId: number, query: ServiceOrderFindAllInput) {
    let base = this.db
      .select({
        ...getTableColumns(serviceOrders),
        itemsCount:
          sql<number>`count(distinct ${serviceOrderItems.id})::int`.as(
            'items_count',
          ),
        branchName: branches.name,
      })
      .from(serviceOrders)
      .leftJoin(
        serviceOrderItems,
        eq(serviceOrderItems.serviceOrderId, serviceOrders.id),
      )
      .leftJoin(branches, eq(branches.id, serviceOrders.branchId))
      .groupBy(serviceOrders.id, branches.name, serviceOrderItems.id)
      .where(eq(serviceOrders.vehicleId, vehicleId));

    if (query.haveItems) {
      base = base.having(({ itemsCount }) => gt(itemsCount, 0));
    }

    const exec = await base;

    if (exec.length === 0) {
      return exec;
    }

    const items = await this.serviceOrderItemsService.findByServiceOrderId(
      exec.map((i) => i.id),
    );

    return exec.map((b) => {
      const i = items.filter((i) => i.serviceOrderId === b.id);
      const total = i.reduce(
        (a, b) => a + (b.price * b.quantity - (b.discount || 0)),
        0,
      );

      return {
        ...b,
        items: i.map((j) => ({
          ...j,
          productName: j.inventoryItem?.name,
        })),
        total,
      };
    });
  }

  async findByVehicle(vehicleId: number, query: ServiceOrderFindAllInput) {
    let base = this.db
      .select({
        ...getTableColumns(serviceOrders),
        itemsCount:
          sql<number>`count(distinct ${serviceOrderItems.id})::int`.as(
            'items_count',
          ),
        branchName: branches.name,
      })
      .from(serviceOrders)
      .leftJoin(
        serviceOrderItems,
        eq(serviceOrderItems.serviceOrderId, serviceOrders.id),
      )
      .leftJoin(branches, eq(branches.id, serviceOrders.branchId))
      .groupBy(serviceOrders.id, branches.name, serviceOrderItems.id)
      .where(eq(serviceOrders.vehicleId, vehicleId));

    if (query.haveItems) {
      base = base.having(({ itemsCount }) => gt(itemsCount, 0));
    }

    const exec = await base;

    if (exec.length === 0) {
      return exec;
    }

    const items = await this.serviceOrderItemsService.findByServiceOrderId(
      exec.map((i) => i.id),
    );

    return exec.map((b) => {
      const i = items.filter((i) => i.serviceOrderId === b.id);
      const total = i.reduce(
        (a, b) => a + (b.price * b.quantity - (b.discount || 0)),
        0,
      );

      return {
        ...b,
        items: i.map((j) => ({
          ...j,
          productName: j.inventoryItem?.name,
        })),
        total,
      };
    });
  }

8 replies

DTDrizzle Team

•Created by 균어 on 11/24/2023 in #help

Postgresql Join select columns with case

i want to get like this

const res = await this.db.select({
...getTableColumns(table1),
items: {...getTableColumns(table2), partNumber: sql<string>`case when ${products.id} is not null then ${products.partNumber} else ${inventoryItems.partNumber} end`,
name: sql<string>`case when ${products.id} is not null then ${products.nameEn} else ${inventoryItems.name} end`}
})

const res = await this.db.select({
...getTableColumns(table1),
items: {...getTableColumns(table2), partNumber: sql<string>`case when ${products.id} is not null then ${products.partNumber} else ${inventoryItems.partNumber} end`,
name: sql<string>`case when ${products.id} is not null then ${products.nameEn} else ${inventoryItems.name} end`}
})

items must be an array but im getting only 1 object

8 replies

DTDrizzle Team

•Created by 균어 on 11/24/2023 in #help

Postgresql Join select columns with case

@Angelelz

8 replies

DTDrizzle Team

•Created by Timo Martinson on 11/24/2023 in #help

Where to get the types from?

you can use

typeof tableName.$inferSelect

typeof tableName.$inferSelect

2 replies

DTDrizzle Team

•Created by FelixNg on 11/24/2023 in #help

createInsertSchema & createSelectSchema difference

in delete you only need an id to delete update you can use createInsertSchema(table).partial()

Gaming

Programming