SKUZZIE Posts - Answer Overflow

SKUZZIE

•Created by SKUZZIE on 1/11/2024 in #help

Joining a subquery multiple times

Is there way to join a subquery multiple times? For example, I have this subquery:

const allLocationsData = db
      .select({
        period_start:
          sql<string>`date_trunc(${params.range}, ${brandSalesData.date})::date`
            .mapWith({
              mapFromDriverValue: (value: Date) => {
                return dayjs(value).format("YYYY-MM-DD");
              },
            })
            .as("period_start_all_locations"),
        average_net_sales:
          sql<string>`avg(${brandSalesData.net_sales})::numeric(1000,2)`.as(
            "average_net_sales_all_locations"
          ),
        average_check_count:
          sql<number>`avg(${brandSalesData.check_count})::integer`.as(
            "average_check_count_all_locations"
          ),
      })
      .from(brandSalesData)
      .groupBy(sql`period_start_all_locations`)
      .as("all_locations");

const allLocationsData = db
      .select({
        period_start:
          sql<string>`date_trunc(${params.range}, ${brandSalesData.date})::date`
            .mapWith({
              mapFromDriverValue: (value: Date) => {
                return dayjs(value).format("YYYY-MM-DD");
              },
            })
            .as("period_start_all_locations"),
        average_net_sales:
          sql<string>`avg(${brandSalesData.net_sales})::numeric(1000,2)`.as(
            "average_net_sales_all_locations"
          ),
        average_check_count:
          sql<number>`avg(${brandSalesData.check_count})::integer`.as(
            "average_check_count_all_locations"
          ),
      })
      .from(brandSalesData)
      .groupBy(sql`period_start_all_locations`)
      .as("all_locations");

I'm trying to join the query once for the "current year" data, and again for the "previous year" data based on a date range.

1 replies

DTDrizzle Team

•Created by SKUZZIE on 12/14/2023 in #help

Upsert with multi-column unique index?

What is the correct way to do an upsert with a multi-column unique index? I have this model:

export const dailyStats = pgTable(
  "daily_stats",
  {
    ad_id: text("ad_id")
      .references(() => ads.id)
      .primaryKey(),
    created_at: timestamp("created_at").notNull().defaultNow(),
    updated_at: timestamp("updated_at").notNull().defaultNow(),

    date: date("date").notNull(),

    ...statObject,
  },
  (t) => ({
    ad_and_date: uniqueIndex("ad_and_date").on(t.ad_id, t.date),
  })
);

export const dailyStats = pgTable(
  "daily_stats",
  {
    ad_id: text("ad_id")
      .references(() => ads.id)
      .primaryKey(),
    created_at: timestamp("created_at").notNull().defaultNow(),
    updated_at: timestamp("updated_at").notNull().defaultNow(),

    date: date("date").notNull(),

    ...statObject,
  },
  (t) => ({
    ad_and_date: uniqueIndex("ad_and_date").on(t.ad_id, t.date),
  })
);

And I'm trying to do an upsert like this:

await db
        .insert(dailyStats)
        .values({
          ad_id: dbAd.id,
          date: date.toISOString(),
          ...dailyStatsInsert,
        })
        .onConflictDoUpdate({
          target: [dailyStats.ad_id, dailyStats.date],
          set: dailyStatsInsert,
        });

await db
        .insert(dailyStats)
        .values({
          ad_id: dbAd.id,
          date: date.toISOString(),
          ...dailyStatsInsert,
        })
        .onConflictDoUpdate({
          target: [dailyStats.ad_id, dailyStats.date],
          set: dailyStatsInsert,
        });

But I'm getting the duplicate key value violates unique constraint error even though I have the target of the onConflictDoUpdate set to those two columns. What am I doing wrong?

28 replies

Gaming

Programming