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')`
)
)
);
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}`
);
3 Replies
Hello, @Rorsch! Could you try to invoke
getSQL() method for currentMonthRevenue and lastMonthRevenue? It should helpboth
currentMonthRevenue and lastMonthRevenue is identical. this is the return
SQL {
decoder: { mapFromDriverValue: [Function: mapFromDriverValue] },
shouldInlineParams: false,
queryChunks: [
StringChunk { value: [Array] },
undefined,
StringChunk { value: [Array] },
undefined,
StringChunk { value: [Array] },
SQL {
decoder: [Object],
shouldInlineParams: false,
queryChunks: [Array]
},
StringChunk { value: [Array] },
PgTable {
id: [PgVarchar],
date: [PgTimestamp],
debitAccountId: [PgVarchar],
creditAccountId: [PgVarchar],
amount: [PgBigInt53],
description: [PgVarchar],
createdAt: [PgTimestamp],
updatedAt: [PgTimestamp],
[Symbol(drizzle:IsAlias)]: false,
[Symbol(drizzle:ExtraConfigBuilder)]: undefined,
[Symbol(drizzle:IsDrizzleTable)]: true,
[Symbol(drizzle:OriginalName)]: 'accounting_transaction',
[Symbol(drizzle:Name)]: 'accounting_transaction',
[Symbol(drizzle:Schema)]: undefined,
[Symbol(drizzle:BaseName)]: 'accounting_transaction',
[Symbol(drizzle:PgInlineForeignKeys)]: [],
[Symbol(drizzle:Columns)]: [Object]
},
StringChunk { value: [Array] },
SQL {
decoder: [Object],
shouldInlineParams: false,
queryChunks: [Array]
},
StringChunk { value: [Array] },
SQL {
decoder: [Object],
shouldInlineParams: false,
queryChunks: [Array]
},
StringChunk { value: [Array] },
undefined,
StringChunk { value: [Array] },
undefined,
StringChunk { value: [Array] },
undefined,
StringChunk { value: [Array] },
undefined,
StringChunk { value: [Array] },
undefined,
StringChunk { value: [Array] },
SQL {
decoder: [Object],
shouldInlineParams: false,
queryChunks: []
},
StringChunk { value: [Array] }
]
}
SQL {
decoder: { mapFromDriverValue: [Function: mapFromDriverValue] },
shouldInlineParams: false,
queryChunks: [
StringChunk { value: [Array] },
undefined,
StringChunk { value: [Array] },
undefined,
StringChunk { value: [Array] },
SQL {
decoder: [Object],
shouldInlineParams: false,
queryChunks: [Array]
},
StringChunk { value: [Array] },
PgTable {
id: [PgVarchar],
date: [PgTimestamp],
debitAccountId: [PgVarchar],
creditAccountId: [PgVarchar],
amount: [PgBigInt53],
description: [PgVarchar],
createdAt: [PgTimestamp],
updatedAt: [PgTimestamp],
[Symbol(drizzle:IsAlias)]: false,
[Symbol(drizzle:ExtraConfigBuilder)]: undefined,
[Symbol(drizzle:IsDrizzleTable)]: true,
[Symbol(drizzle:OriginalName)]: 'accounting_transaction',
[Symbol(drizzle:Name)]: 'accounting_transaction',
[Symbol(drizzle:Schema)]: undefined,
[Symbol(drizzle:BaseName)]: 'accounting_transaction',
[Symbol(drizzle:PgInlineForeignKeys)]: [],
[Symbol(drizzle:Columns)]: [Object]
},
StringChunk { value: [Array] },
SQL {
decoder: [Object],
shouldInlineParams: false,
queryChunks: [Array]
},
StringChunk { value: [Array] },
SQL {
decoder: [Object],
shouldInlineParams: false,
queryChunks: [Array]
},
StringChunk { value: [Array] },
undefined,
StringChunk { value: [Array] },
undefined,
StringChunk { value: [Array] },
undefined,
StringChunk { value: [Array] },
undefined,
StringChunk { value: [Array] },
undefined,
StringChunk { value: [Array] },
SQL {
decoder: [Object],
shouldInlineParams: false,
queryChunks: []
},
StringChunk { value: [Array] }
]
}
Yes, and you pass them as before to your query