Drizzle Team•2w ago

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

I've been trying to figure out how to convert this query for almost two hours, I don't understand how these multiple with statements translates into the drizzle syntax, anyone knows?

WITH review_dates AS (
      SELECT DISTINCT DATE(${ReviewLog.createdAt}) AS review_date
      FROM ${ReviewLog}
    ),
    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
      (SELECT consecutive_days FROM current_streak) AS 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 ${ReviewLog}) 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

WITH review_dates AS (
      SELECT DISTINCT DATE(${ReviewLog.createdAt}) AS review_date
      FROM ${ReviewLog}
    ),
    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
      (SELECT consecutive_days FROM current_streak) AS 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 ${ReviewLog}) 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

5 Replies

Mukund•2w ago

Did you figure how to convert this query? @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];
};

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

marpeOP•2w ago

oh damn, nice! I didn't realize the .with() function was variadic :///// must have tried everything BUT that haha

Mukund•2w ago

I didn't knew that either

marpeOP•2w ago

yeah this seems to work as expected I tried using chatgpt but it just kept making up imaginary APIs, nothing it produced worked maybe should switch over

Mukund•2w ago

Yeah, calude is much better in that sense.

Gaming

Programming

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

Did you find this page helpful?