using unions with "with" clause

I am trying to do something like the following, but cant figure out how to do this with drizzle syntax.

WITH common_subquery AS (
    SELECT user_id, column1, column2
    FROM shared_table
    WHERE condition
)
SELECT user_id, column1, column2
FROM common_subquery
JOIN table1 ...
UNION
SELECT user_id, column1, column2
FROM common_subquery
JOIN table2 ...

WITH common_subquery AS (
    SELECT user_id, column1, column2
    FROM shared_table
    WHERE condition
)
SELECT user_id, column1, column2
FROM common_subquery
JOIN table1 ...
UNION
SELECT user_id, column1, column2
FROM common_subquery
JOIN table2 ...

11 Replies

Dan ClaroniOP•7mo ago

i'd like to do something like await db.with(usersSubQuery).union(prefsSubQuery, recurringPrefsSubQuery);

Dan ClaroniOP•7mo ago

i opened a GH issue with it, since I dont think this is possible https://github.com/drizzle-team/drizzle-orm/issues/2230

GitHub

[FEATURE]: Add ability to use CTEs with UNION queries · Issue #2230...

Describe what you want When peforming a union, I would like to be able to use the "with" clause to define a CTE. For example: const usersSubQuery = db .$with('sq') .as(getUserSubs...

Sillvva•7mo ago

Use the builder pattern

const result = await db
  .with(usersSubQuery)
  .select()
  ... // your prefsSubQuery composed here
  .union(recurringPrefsSubQuery);

const result = await db
  .with(usersSubQuery)
  .select()
  ... // your prefsSubQuery composed here
  .union(recurringPrefsSubQuery);

Dan ClaroniOP•7mo ago

@Sillvva that query in the Union block throws an error that it doesn’t know about the usersSubQuery

    const usersSubQuery = db
        .$with(usersSubQueryName)
        .as(getUsersWithPrefsQuery());

    const prefsSubQuery = getPrefsQuery(usersSubQuery); // uses db.with(usersSubQuery)

    const recurringPrefsSubQuery = getRecurringPrefsQuery( // doesnt use db.with(usersSubQuery), but does use usersSubQuery in the from block
        usersSubQuery,
    );

    const rows = await prefsSubQuery
        .union(recurringPrefsSubQuery)

    const usersSubQuery = db
        .$with(usersSubQueryName)
        .as(getUsersWithPrefsQuery());

    const prefsSubQuery = getPrefsQuery(usersSubQuery); // uses db.with(usersSubQuery)

    const recurringPrefsSubQuery = getRecurringPrefsQuery( // doesnt use db.with(usersSubQuery), but does use usersSubQuery in the from block
        usersSubQuery,
    );

    const rows = await prefsSubQuery
        .union(recurringPrefsSubQuery)

PostgresError: relation "usersSubQuery" does not exist it's wrapping everything up until the union in parens

Sillvva•7mo ago

Hmm. I'm not sure.

Dan ClaroniOP•7mo ago

does that query you posted work for you just fine?

Sillvva•7mo ago

I haven't tried it with query functions like that. I don't have any I can work with right now to test it. The docs show this builder pattern though. (Click the builder-pattern tab in the code block) https://orm.drizzle.team/docs/set-operations#union

Drizzle ORM - Set Operations

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Sillvva•7mo ago

I'm not sure why it wouldn't work with with

Dan ClaroniOP•7mo ago

@Sillvva I got around to trying this today and it doenst work. you have to specify either db.select or db.with(sq).select for the right side of the union. Either way, drizzle wraps the entire left side in its own parens and the entire right side in its own parens, so the CTE is scoped within the parens. I tried using a query builder object too, but same issue @alexblokh @Andrew Sherman any ideas on this?

Andrii Sherman•7mo ago

I thinks it's a bug, we will work on fixing it. Also if someone wants to take it - feel free to file a PR

Dan ClaroniOP•7mo ago

Thank you!

Gaming

Programming

using unions with "with" clause