Cmelvard Posts - Answer Overflow

Cmelvard

•Created by Cmelvard on 3/15/2024 in #help

Optimize query question/help

I have the current query that gets the total average of coffee consumption per day, there can be multiple logs with the same date. Wondering if using a subquery is the best way to do it? are there any helpers that can reduce the query size?

// schema
export const log = pgTable(
  "log",
  {
    id: serial("id").primaryKey(),
    date: date("date").notNull(),
    coffeeId: integer("coffeeId")
      .notNull()
      .references(() => coffee.id),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updatedAt"),
  },
  (columns) => ({
    dateIdx: index("date_idx").on(columns.date),
  }),
);

// schema
export const log = pgTable(
  "log",
  {
    id: serial("id").primaryKey(),
    date: date("date").notNull(),
    coffeeId: integer("coffeeId")
      .notNull()
      .references(() => coffee.id),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updatedAt"),
  },
  (columns) => ({
    dateIdx: index("date_idx").on(columns.date),
  }),
);

// query
 getAlltimeCoffeeAvg: publicProcedure.query(async ({ ctx }) => {
    const subquery = ctx.db
      .select({
        count: count(log.id).as("coffee_count"),
      })
      .from(log)
      .groupBy(log.date)
      .as("subquery");

    const avgTotal = await ctx.db
      .select({
        avg: avg(subquery.count),
      })
      .from(subquery);
    
    return avgTotal; // [{"avg":"1.5000000000000000"}]
  }),

// query
 getAlltimeCoffeeAvg: publicProcedure.query(async ({ ctx }) => {
    const subquery = ctx.db
      .select({
        count: count(log.id).as("coffee_count"),
      })
      .from(log)
      .groupBy(log.date)
      .as("subquery");

    const avgTotal = await ctx.db
      .select({
        avg: avg(subquery.count),
      })
      .from(subquery);
    
    return avgTotal; // [{"avg":"1.5000000000000000"}]
  }),

11 replies

Gaming

Programming