db.select().from(multiple tables)
Hello, I want to do this SQL query:
the same as:
the same as:
In drizzle, so far I managed to do it this way:
but I am not proud of it, is there a better way?
thanks in advance
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 tsselect 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 tsthe 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_secondSELECT
(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_secondIn 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
