Selecting from 2 subqueries without using magic `sql()`
Is it possible to select 2 subqueries together using drizzle-orm? in my case, i want to return current month revenue and previous month revenue.
below is a working code
I'm just wondering if i can do it without using the magic sql
const currentMonthRevenue = db
.select({ currentMonthRevenue: sum(accountingTransactions.amount) })
.from(accountingTransactions)
.innerJoin(
chartOfAccounts,
eq(accountingTransactions.creditAccountId, chartOfAccounts.id)
)
.where(
and(
eq(chartOfAccounts.type, "REVENUE"),
eq(
sql`DATE_TRUNC('month', ${accountingTransactions}.date)`,
sql`DATE_TRUNC('month', CURRENT_DATE)`
)
)
);
const lastMonthRevenue = db
.select({ currentMonthRevenue: sum(accountingTransactions.amount) })
.from(accountingTransactions)
.innerJoin(
chartOfAccounts,
eq(accountingTransactions.creditAccountId, chartOfAccounts.id)
)
.where(
and(
eq(chartOfAccounts.type, "REVENUE"),
eq(
sql`DATE_TRUNC('month', ${accountingTransactions}.date)`,
sql`DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')`
)
)
); const currentMonthRevenue = db
.select({ currentMonthRevenue: sum(accountingTransactions.amount) })
.from(accountingTransactions)
.innerJoin(
chartOfAccounts,
eq(accountingTransactions.creditAccountId, chartOfAccounts.id)
)
.where(
and(
eq(chartOfAccounts.type, "REVENUE"),
eq(
sql`DATE_TRUNC('month', ${accountingTransactions}.date)`,
sql`DATE_TRUNC('month', CURRENT_DATE)`
)
)
);
const lastMonthRevenue = db
.select({ currentMonthRevenue: sum(accountingTransactions.amount) })
.from(accountingTransactions)
.innerJoin(
chartOfAccounts,
eq(accountingTransactions.creditAccountId, chartOfAccounts.id)
)
.where(
and(
eq(chartOfAccounts.type, "REVENUE"),
eq(
sql`DATE_TRUNC('month', ${accountingTransactions}.date)`,
sql`DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')`
)
)
);below is a working code
const data = await db.execute(
sql`SELECT current_month_revenue, last_month_revenue FROM ${currentMonthRevenue}, ${lastMonthRevenue}`
); const data = await db.execute(
sql`SELECT current_month_revenue, last_month_revenue FROM ${currentMonthRevenue}, ${lastMonthRevenue}`
);I'm just wondering if i can do it without using the magic sql
