Sub query with sql magic operator (for common table expression)

Hi friends, I have the following CTE that I can express using the magic sql operator:

const statement = sql`
    WITH RECURSIVE location_tree AS
      (SELECT *
      FROM locations
      WHERE id = 1
      UNION ALL SELECT l.*
      FROM locations l
      INNER JOIN location_tree lt ON l.parent_id = lt.id)
    SELECT DISTINCT package.*
    FROM PACKAGE
    INNER JOIN packages_locations ON package.id = packages_locations.package_id
    INNER JOIN location_tree ON packages_locations.location_id = location_tree.id;`;

const statement = sql`
    WITH RECURSIVE location_tree AS
      (SELECT *
      FROM locations
      WHERE id = 1
      UNION ALL SELECT l.*
      FROM locations l
      INNER JOIN location_tree lt ON l.parent_id = lt.id)
    SELECT DISTINCT package.*
    FROM PACKAGE
    INNER JOIN packages_locations ON package.id = packages_locations.package_id
    INNER JOIN location_tree ON packages_locations.location_id = location_tree.id;`;

This statement should return the package table. Now, I'd like to include this expression as a table, in order to be able to join it, something like this:

    const result= await db
      .select()
      .from(packages)
      .innerJoin("subQuery (above)", eq("subQuery (above)".packageId, packages.id));

    const result= await db
      .select()
      .from(packages)
      .innerJoin("subQuery (above)", eq("subQuery (above)".packageId, packages.id));

Are we able to do it? Maybe with some alias?! Thank you

1 Reply

crawl•8mo ago

Just ran into the same issue With recursive still being an open resolved PR it makes it kinda hard to write queries like this and combine them, you just have to end up writing the whole query manually

Gaming

Programming

Sub query with sql magic operator (for common table expression)