Drizzle Team•7mo ago

Self-Join on a CTE

Is it possible to perform a self-join on a CTE? The main problem is that I cannot alias the CTE for the self-join operation. This prevents me from referencing the previous year's data within the same query. The documentation example demonstrates how to alias and self-join a regular table, but I am unsure how to apply this to a CTE. Below is a minimal code snippet demonstrating the issue:

const cte = ctx.db.main.$with("cte").as(
  ctx.db.main
    .select({
      year: MainTable.Year,
    })
    .from(MainTable)
);

// can't do this
const cteAlias = alias(cte, "parent");

return ctx.db.main
  .with(cte)
  .select({
    prevYear: sql<number>`CAST(SUM(${cteAlias.year} - 1) AS numeric)`,
  })
  .from(cte)
  .leftJoin(cteAlias, eq(cteAlias.id, cte.id));

const cte = ctx.db.main.$with("cte").as(
  ctx.db.main
    .select({
      year: MainTable.Year,
    })
    .from(MainTable)
);

// can't do this
const cteAlias = alias(cte, "parent");

return ctx.db.main
  .with(cte)
  .select({
    prevYear: sql<number>`CAST(SUM(${cteAlias.year} - 1) AS numeric)`,
  })
  .from(cte)
  .leftJoin(cteAlias, eq(cteAlias.id, cte.id));

3 Replies

Isitfato•4mo ago

@AH You ever find a solution to this?

AHOP•3mo ago

no, ended up changing the query

rphlmr ⚡•3mo ago

we can 'try' something, depending on the use case: https://drizzle.run/k1f7jokqsuspovfwah3d9ep6

Drizzle Run

Raw recursive query - Drizzle Run

Gaming

Programming

Self-Join on a CTE