Drizzle Team•7mo ago

Multiple subqueries with .with

Hey all - thank you in advance for the help I'm trying to port a SQL query over to drizzle and am struggling with using multiple CTE in one query. I'm not getting any type errors in my project but in the studio I'm getting "this isn't a valid drizzle query" and I'm not getting any results when I run it in place. I'm more than happy to refactor the query if it helps.

1 Reply

strangecyanOP•7mo ago

Original SQL, runs successfully:

WITH
  total_jobs AS (
    SELECT
      connection_id,
      COUNT(*) AS total_job_count
    FROM
      "jobs"
    WHERE
      last_attempted >= NOW() - INTERVAL '15 minutes'
    GROUP BY
      connection_id
  ),
  failed_jobs AS (
    SELECT
      connection_id,
      COUNT(*) AS failed_job_count
    FROM
      "jobs"
    WHERE
      status = 'failed'
      AND last_attempted >= NOW() - INTERVAL '15 minutes'
    GROUP BY
      connection_id
  )
SELECT
  total_jobs.connection_id,
  total_jobs.total_job_count,
  failed_jobs.failed_job_count,
  (
    cast(failed_jobs.failed_job_count as decimal) / total_jobs.total_job_count
  ) AS failure_percentage
FROM
  total_jobs
  LEFT JOIN failed_jobs ON total_jobs.connection_id = failed_jobs.connection_id;

WITH
  total_jobs AS (
    SELECT
      connection_id,
      COUNT(*) AS total_job_count
    FROM
      "jobs"
    WHERE
      last_attempted >= NOW() - INTERVAL '15 minutes'
    GROUP BY
      connection_id
  ),
  failed_jobs AS (
    SELECT
      connection_id,
      COUNT(*) AS failed_job_count
    FROM
      "jobs"
    WHERE
      status = 'failed'
      AND last_attempted >= NOW() - INTERVAL '15 minutes'
    GROUP BY
      connection_id
  )
SELECT
  total_jobs.connection_id,
  total_jobs.total_job_count,
  failed_jobs.failed_job_count,
  (
    cast(failed_jobs.failed_job_count as decimal) / total_jobs.total_job_count
  ) AS failure_percentage
FROM
  total_jobs
  LEFT JOIN failed_jobs ON total_jobs.connection_id = failed_jobs.connection_id;

Attempted drizzle port: the two subqueries work but the final one does not.

const jobsByConnection = db.$with("jobs_by_connection").as(
  db
    .select({
      connectionId: jobs.connectionId,
      count: sql`count(*)`,
    })
    .from(jobs)
    .where(gt(jobs.lastAttempted, sql`NOW() - INTERVAL '15 minutes'`))
    .groupBy(jobs.connectionId),
);

const failedJobsByConnection = db.$with("jobs_by_connection").as(
  db
    .select({
      connectionId: jobs.connectionId,
      count: sql`count(*)`,
    })
    .from(jobs)
    .where(
      and(
        gt(jobs.lastAttempted, sql`NOW() - INTERVAL '15 minutes'`),
        eq(jobs.status, "failed"),
      ),
    )
    .groupBy(jobs.connectionId),
);

db.with(jobsByConnection, failedJobsByConnection)
  .select({
    connectionId: jobsByConnection.connectionId,
    total: jobsByConnection.count,
    failed: failedJobsByConnection.count,
    rate: sql`cast(${failedJobsByConnection.count} as decimal) / ${jobsByConnection.count}`,
  })
  .from(jobsByConnection)
  .leftJoin(
    failedJobsByConnection,
    eq(jobsByConnection.connectionId, failedJobsByConnection.connectionId),
  );

const jobsByConnection = db.$with("jobs_by_connection").as(
  db
    .select({
      connectionId: jobs.connectionId,
      count: sql`count(*)`,
    })
    .from(jobs)
    .where(gt(jobs.lastAttempted, sql`NOW() - INTERVAL '15 minutes'`))
    .groupBy(jobs.connectionId),
);

const failedJobsByConnection = db.$with("jobs_by_connection").as(
  db
    .select({
      connectionId: jobs.connectionId,
      count: sql`count(*)`,
    })
    .from(jobs)
    .where(
      and(
        gt(jobs.lastAttempted, sql`NOW() - INTERVAL '15 minutes'`),
        eq(jobs.status, "failed"),
      ),
    )
    .groupBy(jobs.connectionId),
);

db.with(jobsByConnection, failedJobsByConnection)
  .select({
    connectionId: jobsByConnection.connectionId,
    total: jobsByConnection.count,
    failed: failedJobsByConnection.count,
    rate: sql`cast(${failedJobsByConnection.count} as decimal) / ${jobsByConnection.count}`,
  })
  .from(jobsByConnection)
  .leftJoin(
    failedJobsByConnection,
    eq(jobsByConnection.connectionId, failedJobsByConnection.connectionId),
  );

I got it - mostly silly mistakes:

const jobsByConnection = db.$with('jobs_by_connection').as(
    db
      .select({
        connectionId: jobs.connectionId,
        // This required an alias
        count: sql`count(*)`.as('count')
      })
      .from(jobs)
      .where(gt(jobs.lastAttempted, sql`NOW() - INTERVAL '15 minutes'`))
      .groupBy(jobs.connectionId)
  );
  // This had the same name!
  const failedJobsByConnection = db.$with('failed_jobs_by_connection').as(
    db
      .select({
        connectionId: jobs.connectionId,
        // This was ambiguous and required an alias
        failedCount: sql`count(*)`.as('failed_count')
      })
      .from(jobs)
      .where(and(gt(jobs.lastAttempted, sql`NOW() - INTERVAL '15 minutes'`), eq(jobs.status, 'failed')))
      .groupBy(jobs.connectionId)
  );

  const results = await db
    .with(jobsByConnection, failedJobsByConnection)
    .select({
      connectionId: jobsByConnection.connectionId,
      total: jobsByConnection.count,
      failed: failedJobsByConnection.failedCount,
      rate: sql`cast(${failedJobsByConnection.failedCount} as decimal) / ${jobsByConnection.count}`.as('rate')
    })
    .from(jobsByConnection)
    .leftJoin(failedJobsByConnection, eq(jobsByConnection.connectionId, failedJobsByConnection.connectionId))
    .where(gt(failedJobsByConnection.failedCount, 0));

const jobsByConnection = db.$with('jobs_by_connection').as(
    db
      .select({
        connectionId: jobs.connectionId,
        // This required an alias
        count: sql`count(*)`.as('count')
      })
      .from(jobs)
      .where(gt(jobs.lastAttempted, sql`NOW() - INTERVAL '15 minutes'`))
      .groupBy(jobs.connectionId)
  );
  // This had the same name!
  const failedJobsByConnection = db.$with('failed_jobs_by_connection').as(
    db
      .select({
        connectionId: jobs.connectionId,
        // This was ambiguous and required an alias
        failedCount: sql`count(*)`.as('failed_count')
      })
      .from(jobs)
      .where(and(gt(jobs.lastAttempted, sql`NOW() - INTERVAL '15 minutes'`), eq(jobs.status, 'failed')))
      .groupBy(jobs.connectionId)
  );

  const results = await db
    .with(jobsByConnection, failedJobsByConnection)
    .select({
      connectionId: jobsByConnection.connectionId,
      total: jobsByConnection.count,
      failed: failedJobsByConnection.failedCount,
      rate: sql`cast(${failedJobsByConnection.failedCount} as decimal) / ${jobsByConnection.count}`.as('rate')
    })
    .from(jobsByConnection)
    .leftJoin(failedJobsByConnection, eq(jobsByConnection.connectionId, failedJobsByConnection.connectionId))
    .where(gt(failedJobsByConnection.failedCount, 0));

Gaming

Programming

Multiple subqueries with .with