db.select().from(multiple tables)

Hello, I want to do this SQL query:

select total.total_sales, ts.tickets_per_second
from
    (
    SELECT count(*) AS total_sales
    FROM public.tickets
    ) as total,
    (
    SELECT count(*) AS tickets_per_second
    FROM public.tickets
    WHERE created_at > now() - interval '1 second' and created_at < now()
    ) as ts

select total.total_sales, ts.tickets_per_second
from
    (
    SELECT count(*) AS total_sales
    FROM public.tickets
    ) as total,
    (
    SELECT count(*) AS tickets_per_second
    FROM public.tickets
    WHERE created_at > now() - interval '1 second' and created_at < now()
    ) as ts

the same as:

WITH sales_counts AS (
SELECT
    (SELECT count(*) FROM public.tickets) AS total_sales,
    (SELECT count(*) FROM public.tickets WHERE created_at > now() - interval '1 second' AND created_at < now()) AS tickets_per_second
)
SELECT total_sales, tickets_per_second
FROM sales_counts;

WITH sales_counts AS (
SELECT
    (SELECT count(*) FROM public.tickets) AS total_sales,
    (SELECT count(*) FROM public.tickets WHERE created_at > now() - interval '1 second' AND created_at < now()) AS tickets_per_second
)
SELECT total_sales, tickets_per_second
FROM sales_counts;

the same as:

SELECT 
    (SELECT count(*) FROM tickets) as total_sales,
    (SELECT count(*) FROM public.tickets WHERE created_at > now() - interval '1 second' AND created_at < now()) AS tickets_per_second

SELECT 
    (SELECT count(*) FROM tickets) as total_sales,
    (SELECT count(*) FROM public.tickets WHERE created_at > now() - interval '1 second' AND created_at < now()) AS tickets_per_second

In drizzle, so far I managed to do it this way:

db.select({
    totalSales: sql<string>`total.total_sales`,
    ticketsPerSecond: sql<string>`ts.tickets_per_second`,
  })
    .from(sql<string>`(
      SELECT count(*) AS total_sales
      FROM public.tickets
      ) as total,
      (
      SELECT count(*) AS tickets_per_second
      FROM public.tickets
      WHERE created_at > now() - interval '1 second' and created_at < now()
      ) as ts`),

db.select({
    totalSales: sql<string>`total.total_sales`,
    ticketsPerSecond: sql<string>`ts.tickets_per_second`,
  })
    .from(sql<string>`(
      SELECT count(*) AS total_sales
      FROM public.tickets
      ) as total,
      (
      SELECT count(*) AS tickets_per_second
      FROM public.tickets
      WHERE created_at > now() - interval '1 second' and created_at < now()
      ) as ts`),

but I am not proud of it, is there a better way? thanks in advance

1 Reply

petsagouris•9mo ago

https://orm.drizzle.team/docs/select#with-clause

Drizzle ORM - Select

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Gaming

Programming

db.select().from(multiple tables)