strangecyan Comments - Answer Overflow

strangecyan

•Created by strangecyan on 6/3/2024 in #help

Multiple subqueries with .with

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));

3 replies

DTDrizzle Team

•Created by strangecyan on 6/3/2024 in #help

Multiple subqueries with .with

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),
  );

3 replies

Gaming

Programming