Drizzle TeamDT
Drizzle Team•2y ago
Nelson Sousa

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


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


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

Thank you
Was this page helpful?