Mukund Comments - Answer Overflow

Mukund

Posts Comments

DTDrizzle Team

•Created by Kuba on 9/25/2024 in #help

referencedTable error when querying

5 replies

DTDrizzle Team

•Created by nu on 2/13/2025 in #help

How to handle multiple unique constraints with onConflictDoUpdate?

I think target can take an array of values too Something like this (not sure tho)

const data = db.insert(schema.users).values({
  name: "test",
  imageUrl: "test",
}).onConflictDoUpdate({
  target: [schema.users.name, schema.users.id],
  set: {
    name: "New Value",
    id: "123"
  }
})

const data = db.insert(schema.users).values({
  name: "test",
  imageUrl: "test",
}).onConflictDoUpdate({
  target: [schema.users.name, schema.users.id],
  set: {
    name: "New Value",
    id: "123"
  }
})

5 replies

DTDrizzle Team

•Created by marpe on 2/13/2025 in #help

Cant figure out how to converting a sql query to drizzle syntax

Yeah, calude is much better in that sense.

11 replies

DTDrizzle Team

•Created by Arthur on 2/13/2025 in #help

Drizzle zod infer wrong type

8 replies

DTDrizzle Team

•Created by Arthur on 2/13/2025 in #help

Drizzle zod infer wrong type

Hi, which version of drizzle orm are you using?

8 replies

DTDrizzle Team

•Created by marpe on 2/13/2025 in #help

Cant figure out how to converting a sql query to drizzle syntax

I didn't knew that either

11 replies

DTDrizzle Team

•Created by marpe on 2/13/2025 in #help

Cant figure out how to converting a sql query to drizzle syntax

Type of result:-

const result: {
    consecutiveDays: number;
    streakOngoing: boolean;
    totalReviews: number;
    totalReviewDays: number;
    daysSinceLastReview: number;
}[]

const result: {
    consecutiveDays: number;
    streakOngoing: boolean;
    totalReviews: number;
    totalReviewDays: number;
    daysSinceLastReview: number;
}[]

SQL Query (console logged using logging feature of drizzle orm):-

with "review_dates" as (
  select DISTINCT DATE("created_at") as "review_date"
   from "review_log"
   ),
    "streaks" as (
      select "review_date",
      "review_date" - CAST(ROW_NUMBER() OVER (ORDER BY "review_date") AS integer) as "grp"
      from "review_dates"
      ),
      "current_streak" as (
        select COUNT(*)::integer as "consecutive_days"
        from "streaks"
        where "grp" = (SELECT grp FROM "streaks" ORDER BY review_date DESC LIMIT 1)
        )
        select "consecutive_days",
        CASE
          WHEN (SELECT MAX("review_date") FROM "review_dates") = CURRENT_DATE THEN TRUE
          ELSE FALSE
        END as "streak_ongoing",
        (SELECT COUNT(*)::integer FROM "review_log") as "total_reviews",
        (SELECT COUNT(*)::integer FROM "review_dates") as "total_review_days",
        (CURRENT_DATE - (SELECT MAX("review_date") FROM "review_dates"))::integer as "days_since_last_review" from "current_streak"

with "review_dates" as (
  select DISTINCT DATE("created_at") as "review_date"
   from "review_log"
   ),
    "streaks" as (
      select "review_date",
      "review_date" - CAST(ROW_NUMBER() OVER (ORDER BY "review_date") AS integer) as "grp"
      from "review_dates"
      ),
      "current_streak" as (
        select COUNT(*)::integer as "consecutive_days"
        from "streaks"
        where "grp" = (SELECT grp FROM "streaks" ORDER BY review_date DESC LIMIT 1)
        )
        select "consecutive_days",
        CASE
          WHEN (SELECT MAX("review_date") FROM "review_dates") = CURRENT_DATE THEN TRUE
          ELSE FALSE
        END as "streak_ongoing",
        (SELECT COUNT(*)::integer FROM "review_log") as "total_reviews",
        (SELECT COUNT(*)::integer FROM "review_dates") as "total_review_days",
        (CURRENT_DATE - (SELECT MAX("review_date") FROM "review_dates"))::integer as "days_since_last_review" from "current_streak"

This is my assumed schema:-

export const reviewLog = createTale("review_log", {
  id: serial("id").primaryKey(),
  createdAt: timestamp("created_at").notNull(),
});

export const reviewLog = createTale("review_log", {
  id: serial("id").primaryKey(),
  createdAt: timestamp("created_at").notNull(),
});

Output of the code:-

{
  "consecutiveDays": 2,
  "streakOngoing": true,
  "totalReviews": 67,
  "totalReviewDays": 25,
  "daysSinceLastReview": 0,
}

{
  "consecutiveDays": 2,
  "streakOngoing": true,
  "totalReviews": 67,
  "totalReviewDays": 25,
  "daysSinceLastReview": 0,
}

11 replies

DTDrizzle Team

•Created by marpe on 2/13/2025 in #help

Cant figure out how to converting a sql query to drizzle syntax

@marpe I tried different things and with some help from calude I got this This is the drizzle query:-

const getReviewStats = async () => {
  const reviewDatesSubquery = db.$with("review_dates").as(
    db
      .select({
        reviewDate: sql<Date>`DISTINCT DATE(${reviewLog.createdAt})`.as(
          "review_date",
        ),
      })
      .from(reviewLog),
  );

  const streaksSubquery = db.$with("streaks").as(
    db
      .select({
        reviewDate: reviewDatesSubquery.reviewDate,
        grp: sql<Date>`${reviewDatesSubquery.reviewDate} -
          CAST(ROW_NUMBER() OVER (ORDER BY ${reviewDatesSubquery.reviewDate}) AS integer)`.as(
          "grp",
        ),
      })
      .from(reviewDatesSubquery),
  );

  const currentStreakSubquery = db.$with("current_streak").as(
    db
      .select({
        consecutiveDays: sql<number>`COUNT(*)::integer`.as("consecutive_days"),
      })
      .from(streaksSubquery)
      .where(
        eq(streaksSubquery.grp, sql`(SELECT grp FROM ${streaksSubquery} ORDER BY review_date DESC LIMIT 1)`),
      ),
  );
  const result = await db
    .with(reviewDatesSubquery, streaksSubquery, currentStreakSubquery)
    .select({
      consecutiveDays: currentStreakSubquery.consecutiveDays,
      streakOngoing: sql<boolean>`
        CASE
          WHEN (SELECT MAX(${reviewDatesSubquery.reviewDate}) FROM ${reviewDatesSubquery}) = CURRENT_DATE THEN TRUE
          ELSE FALSE
        END
      `.as("streak_ongoing"),
      totalReviews: sql<number>`
        (SELECT COUNT(*)::integer FROM ${reviewLog})
      `.as("total_reviews"),
      totalReviewDays: sql<number>`
        (SELECT COUNT(*)::integer FROM ${reviewDatesSubquery})
      `.as("total_review_days"),
      daysSinceLastReview: sql<number>`
        (CURRENT_DATE - (SELECT MAX(${reviewDatesSubquery.reviewDate}) FROM ${reviewDatesSubquery}))::integer
      `.as("days_since_last_review"),
    })
    .from(currentStreakSubquery);
  return result[0];
};

const getReviewStats = async () => {
  const reviewDatesSubquery = db.$with("review_dates").as(
    db
      .select({
        reviewDate: sql<Date>`DISTINCT DATE(${reviewLog.createdAt})`.as(
          "review_date",
        ),
      })
      .from(reviewLog),
  );

  const streaksSubquery = db.$with("streaks").as(
    db
      .select({
        reviewDate: reviewDatesSubquery.reviewDate,
        grp: sql<Date>`${reviewDatesSubquery.reviewDate} -
          CAST(ROW_NUMBER() OVER (ORDER BY ${reviewDatesSubquery.reviewDate}) AS integer)`.as(
          "grp",
        ),
      })
      .from(reviewDatesSubquery),
  );

  const currentStreakSubquery = db.$with("current_streak").as(
    db
      .select({
        consecutiveDays: sql<number>`COUNT(*)::integer`.as("consecutive_days"),
      })
      .from(streaksSubquery)
      .where(
        eq(streaksSubquery.grp, sql`(SELECT grp FROM ${streaksSubquery} ORDER BY review_date DESC LIMIT 1)`),
      ),
  );
  const result = await db
    .with(reviewDatesSubquery, streaksSubquery, currentStreakSubquery)
    .select({
      consecutiveDays: currentStreakSubquery.consecutiveDays,
      streakOngoing: sql<boolean>`
        CASE
          WHEN (SELECT MAX(${reviewDatesSubquery.reviewDate}) FROM ${reviewDatesSubquery}) = CURRENT_DATE THEN TRUE
          ELSE FALSE
        END
      `.as("streak_ongoing"),
      totalReviews: sql<number>`
        (SELECT COUNT(*)::integer FROM ${reviewLog})
      `.as("total_reviews"),
      totalReviewDays: sql<number>`
        (SELECT COUNT(*)::integer FROM ${reviewDatesSubquery})
      `.as("total_review_days"),
      daysSinceLastReview: sql<number>`
        (CURRENT_DATE - (SELECT MAX(${reviewDatesSubquery.reviewDate}) FROM ${reviewDatesSubquery}))::integer
      `.as("days_since_last_review"),
    })
    .from(currentStreakSubquery);
  return result[0];
};

11 replies

DTDrizzle Team

•Created by marpe on 2/13/2025 in #help

Cant figure out how to converting a sql query to drizzle syntax

Did you figure how to convert this query?

11 replies

Gaming

Programming