Livog Posts - Answer Overflow

Livog

•Created by Livog on 11/27/2023 in #help

unionAll gives TypeError: leftSelect.getSelectedFields is not a function

I'm trying to query 2 collections in the same query as the amount of collections might grow over time that I will check against I would want a scalable way and also use drizzles built in features, so I tryed this: Note I'm using Payload CMS, so I don't have access to the schema files directly, actually got no clue if its db.schema or db.tables I should use. Running these queries separately works great, just when I add unionAll I get:

[15:34:20] ERROR (payload): TypeError: leftSelect.getSelectedFields is not a function
    at /Users/livog/Documents/Projects/Payload.CMS/node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/select.ts:677:42

[15:34:20] ERROR (payload): TypeError: leftSelect.getSelectedFields is not a function
    at /Users/livog/Documents/Projects/Payload.CMS/node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/select.ts:677:42

Code:

const db = payload?.db
const drizzle = payload?.db?.drizzle

if (!payload || !db || !drizzle || collectionsToCheck.length === 0) return false // This will be checked server side or we don't support it without drizzle.
const rows = await unionAll(
    // @ts-ignore
    ...collectionsToCheck.map(collection => {
      const andCondtions = [eq(db.schema[collection]['path'], path)]
      if (currentCollection !== collection) {
        andCondtions.push(ne(db.schema[collection]['id'], currentDocId))
      }
      return drizzle
        .select({ id: db.schema[collection]['id'], path: db.schema[collection]['path'] })
        .from(db.tables[collection])
        .where(
          // @ts-ignore // Todo: type checking
          and(...andCondtions)
        )
    })
  )

const db = payload?.db
const drizzle = payload?.db?.drizzle

if (!payload || !db || !drizzle || collectionsToCheck.length === 0) return false // This will be checked server side or we don't support it without drizzle.
const rows = await unionAll(
    // @ts-ignore
    ...collectionsToCheck.map(collection => {
      const andCondtions = [eq(db.schema[collection]['path'], path)]
      if (currentCollection !== collection) {
        andCondtions.push(ne(db.schema[collection]['id'], currentDocId))
      }
      return drizzle
        .select({ id: db.schema[collection]['id'], path: db.schema[collection]['path'] })
        .from(db.tables[collection])
        .where(
          // @ts-ignore // Todo: type checking
          and(...andCondtions)
        )
    })
  )

This is the documentation that I looked at which I feel like I replicated: https://orm.drizzle.team/docs/set-operations#union-all Any help is super appreciated!

4 replies

DTDrizzle Team

•Created by Livog on 7/13/2023 in #help

Foreign Key Reference to auth Schema Not Generated in Code

I'm having an issue with my Drizzle-ORM code where the foreign key reference to the 'auth' schema is not being generated as expected. The issue lies in this block of code:

import { index, jsonb, pgSchema, pgTable, text, uniqueIndex, uuid } from 'drizzle-orm/pg-core'

export const USER_TABLE = 'user'

const authSchema = pgSchema('auth')
const authUser = authSchema.table('users', { id: uuid('id').primaryKey().unique().notNull() })

export const user = pgTable(
  USER_TABLE,
  {
    id: uuid('id')
      .primaryKey()
      .unique()
      .references(() => authUser.id),
    firstName: text('firstName'),
    lastName: text('lastName'),
    email: text('email').unique(),
    image: text('image'),
    customData: jsonb('customData'),
    notificationKeys: jsonb('notificationKeys')
  },
  (table) => {
    return {
      emailKey: uniqueIndex('user_email_key').on(table.email),
      userIdEmailIdx: index('user_id_email_idx').on(table.id, table.email)
    }
  }
)

import { index, jsonb, pgSchema, pgTable, text, uniqueIndex, uuid } from 'drizzle-orm/pg-core'

export const USER_TABLE = 'user'

const authSchema = pgSchema('auth')
const authUser = authSchema.table('users', { id: uuid('id').primaryKey().unique().notNull() })

export const user = pgTable(
  USER_TABLE,
  {
    id: uuid('id')
      .primaryKey()
      .unique()
      .references(() => authUser.id),
    firstName: text('firstName'),
    lastName: text('lastName'),
    email: text('email').unique(),
    image: text('image'),
    customData: jsonb('customData'),
    notificationKeys: jsonb('notificationKeys')
  },
  (table) => {
    return {
      emailKey: uniqueIndex('user_email_key').on(table.email),
      userIdEmailIdx: index('user_id_email_idx').on(table.id, table.email)
    }
  }
)

The output SQL generated is:

DO $$ BEGIN
 ALTER TABLE "user" ADD CONSTRAINT "user_id_users_id_fk" FOREIGN KEY ("id") REFERENCES "users"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "user" ADD CONSTRAINT "user_id_users_id_fk" FOREIGN KEY ("id") REFERENCES "users"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

The problem is, it should reference the auth schema that has a users table, not just the users table. The 'auth' schema is not reflected in the ALTER TABLE SQL statement generated. I am unsure if this is a bug in the Drizzle-ORM package or if there is something wrong with my code. Please advise.

3 replies

Gaming

Programming