DT
Drizzle TeamDan Claroni

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 ...
DC
Dan Claroni•16d ago
i'd like to do something like await db.with(usersSubQuery).union(prefsSubQuery, recurringPrefsSubQuery);
DC
Dan Claroni•16d 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...
S
Sillvva•15d 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);
DC
Dan Claroni•15d 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
S
Sillvva•15d ago
Hmm. I'm not sure.
DC
Dan Claroni•15d ago
does that query you posted work for you just fine?
S
Sillvva•15d 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.
S
Sillvva•15d ago
I'm not sure why it wouldn't work with with
DC
Dan Claroni•13d 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?
AS
Andrii Sherman•9d 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
DC
Dan Claroni•9d ago
Thank you!
Want results from more Discord servers?
Add your server
More Posts
ts: Object literal may only specify known properties except the property is known``` No overload matches this call. Overload 2 of 2, '(values: { role: string | SQL<unknown> | Placerror: no schema has been selected to create in while running push commandtrying to setup drizzle with local postgres database, DB_CONN_STRING is 'postgres://postgres:passworNavigating through multiple many-to-many relationships in a single query?Hi all! I've been racking my brain unsuccessfuly on how to accomplish my desired behavior with a queCircular Reference Error when Using Self-Reference in Table Definition with Drizzle-ORMI encountered an error while defining a PostgreSQL table using Drizzle-ORM. The issue arises when atSQL many statements?Any ideas on how to debug this? ```Task migrations deno run -A src/lib/db/migrations.ts error: UncaQuerying a materialized view: relation does not existI define a materialized view as such: ``` export const usersMv = pgMaterializedView("users_mv").as( default to empty object for sqlite text field with JSON modeHow can I default a `mode: 'json'` text type to an empty object in sqlite? The following in a `sqliConverting a field from X type to Serial causes errorsI'm attempting to convert a field from type `text` to type `serial`. Here's the error I'm seeing: `Return type is only "with" types, ignores "columns" typesNot sure if I'm using it wrong but the return type of a relational query is just the columns from thonUpdateupdatedAt: timestamp('updated_at', { mode: 'date', precision: 3 }) .defaultNow() .$onUpdate(Struggling with subqueriesI want to basically do this query in drizzle: SELECT o.id, o.date, t.currency FROM "Operations" o LENot clear what happens during migrateI have a sandbox project and have properly created a migrate that created the initial schema, and apmax connections time out```typescript import { Injectable, Scope } from '@nestjs/common'; import { Pool, PoolConfig } from 'CI/CD check if schema matches databaseHi, is there a way use drizzle-kit or drizzle itself to check if the database structure matches the 'drizzle-kit introspect:pg' generates invalid Typerscript syntax ("::character varying", "NULL")When I use , it generates my schema.ts file, but includes syntax that is invalid, such as: ``` expoCan I create a one to one relationship that would actually fetch the last item?The relationship is actually a one to many but only want to retrieve the last one. goal: one(goalclasses to access dbIs this code possible? Copilot says it's valid but I cannot find it in the documentation ``` importQuery "with" not populatedI have a collection relation and a file relation. I want to query a collection and all their files. Duplicate parentHey, I have a large schema with many tables and many relations. I would like to duplicate a parent rDynamic read replicasI have a use case that read replicas are dynamically located using Service Discovery and I need to s