Drizzle TeamDT
Drizzle Team•10mo ago
tcurdt

Re-using a CTE and typing hell

What I really want:

filteredSet = db.selectDistinct().from(table).where(...)

// both queries should just re-use the filteredSet
query1 = db.select().from(table).where(..).innerJoin(filteredSet)
query2 = db.select().from(table).where(..).innerJoin(filteredSet)


IIUC this only possible with temporary tables - that drizzle does not support yet 😕

So I thought I give it a shot and at least re-use via a CTE. But I just cannot get the types right.

const responses = db.$with('responses').as(
    db.selectDistinct({
        responseId: tables.tableActions.responseId
    })
    .from(tables.tableActions)
    // where filters
);


const context: Context = {
    responses
}

export type Context = {
  responses: WithSubqueryWithoutSelection<"responses">; // or whatever type this should be
};


const query = await db.with(context.responses)
.select({
    answers: subqueryActionsPerResponse.answers,
    responses: count()
})
.from(subqueryActionsPerResponse)
.innerJoin(context.responses, eq(context.responses.responseId, tables.tableActions.responseId))
.groupBy(subqueryActionsPerResponse.answers)
.orderBy(subqueryActionsPerResponse.answers);


Another option could be to just share a subquery for the joins?

Any pointers appreciated - I am a little lost here.
Was this page helpful?