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
The official Discord for all Drizzle related projects, such as Drizzle ORM, Drizzle Kit, Drizzle Studio and more!
11,879Members
Resources
Similar Threads
Was this page helpful?
