DT
Drizzle Team•2w 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)
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);
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.
0 Replies
No replies yetBe the first to reply to this messageJoin

Did you find this page helpful?