DT
Drizzle Team•4mo ago
desgroup

Multiple leftJoins always returns never[]

Hey, I found something interesting with drizzle's leftJoin typing. If I stack multiple leftJoins back-to-back, the result is always never[]. Is this intentional or a bug? 
import { pgSchema, primaryKey, text, uuid } from 'drizzle-orm/pg-core'
import { drizzle } from 'drizzle-orm/postgres-js'
import { eq } from 'drizzle-orm'

export const schema = pgSchema('neon_app')

export const users = schema.table('users', {
  id: uuid('id').primaryKey(),
})

export const pizzas = schema.table('pizzas', {
  id: uuid('id').primaryKey(),
  flavour: text('flavour').notNull(),
})

export const usersToPizzas = schema.table('users', {
  userId: uuid('user_id').references(() => users.id),
  pizzaId: uuid('pizza_id').references(() => pizzas.id),
}, table => [primaryKey({ columns: [table.userId, table.pizzaId] })])

const db = drizzle(process.env.DATABASE_URL!)

// Not an especially sane example, but SQL will return values for this.
async function getUserAndTheirFlavours() {
  const result = await db
    .select({
      userId: users.id,
      flavours: pizzas.flavour
    })
    .from(users)
    .leftJoin(usersToPizzas, eq(usersToPizzas.userId, users.id))
    .leftJoin(pizzas, eq(pizzas.id, usersToPizzas.pizzaId))
  
  // result is never[]!
}
import { pgSchema, primaryKey, text, uuid } from 'drizzle-orm/pg-core'
import { drizzle } from 'drizzle-orm/postgres-js'
import { eq } from 'drizzle-orm'

export const schema = pgSchema('neon_app')

export const users = schema.table('users', {
  id: uuid('id').primaryKey(),
})

export const pizzas = schema.table('pizzas', {
  id: uuid('id').primaryKey(),
  flavour: text('flavour').notNull(),
})

export const usersToPizzas = schema.table('users', {
  userId: uuid('user_id').references(() => users.id),
  pizzaId: uuid('pizza_id').references(() => pizzas.id),
}, table => [primaryKey({ columns: [table.userId, table.pizzaId] })])

const db = drizzle(process.env.DATABASE_URL!)

// Not an especially sane example, but SQL will return values for this.
async function getUserAndTheirFlavours() {
  const result = await db
    .select({
      userId: users.id,
      flavours: pizzas.flavour
    })
    .from(users)
    .leftJoin(usersToPizzas, eq(usersToPizzas.userId, users.id))
    .leftJoin(pizzas, eq(pizzas.id, usersToPizzas.pizzaId))
  
  // result is never[]!
}
Is this a bug?
2 Replies
Sillvva
Sillvva•4mo ago
In your schema, the usersToPizzas table has the same table name as the users table. 
export const usersToPizzas = schema.table('users', {
//                                        ^ here
  userId: uuid('user_id').references(() => users.id),
  pizzaId: uuid('pizza_id').references(() => pizzas.id),
}, table => [primaryKey({ columns: [table.userId, table.pizzaId] })])
export const usersToPizzas = schema.table('users', {
//                                        ^ here
  userId: uuid('user_id').references(() => users.id),
  pizzaId: uuid('pizza_id').references(() => pizzas.id),
}, table => [primaryKey({ columns: [table.userId, table.pizzaId] })])
Changing that should fix your type issue
desgroup
desgroupOP•4mo ago
beautiful thank you so much

Did you find this page helpful?