AH Posts - Answer Overflow

Explore posts from servers

•Created by AH on 5/22/2024 in #help

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));

4 replies

Gaming

Programming