DT
Drizzle Team•4d ago
johncmacy

Query to include parent with child infers types as `never`

Hello, In my schema, I have a self-referential foreign key set up: 
export const Account = pgTable(
  "account",
  {
    id: uuid("id").primaryKey().notNull().defaultRandom(),
    parentId: uuid("parent_id"),
    name: text().notNull(),
  },
  (table) => [
    foreignKey({
      columns: [table.parentId],
      foreignColumns: [table.id],
      name: "account_parent_id_fkey",
    }).onDelete("restrict"),
  ],
)
export const Account = pgTable(
  "account",
  {
    id: uuid("id").primaryKey().notNull().defaultRandom(),
    parentId: uuid("parent_id"),
    name: text().notNull(),
  },
  (table) => [
    foreignKey({
      columns: [table.parentId],
      foreignColumns: [table.id],
      name: "account_parent_id_fkey",
    }).onDelete("restrict"),
  ],
)
I'm trying to select the parent with the child like this: 
  const ParentAccount = aliasedTable(Account, "parent")

  const accounts = (
    await db
      .select()
      .from(Account)
      .leftJoin(ParentAccount, eq(ParentAccount.id, Account.parentId))
  )
  const ParentAccount = aliasedTable(Account, "parent")

  const accounts = (
    await db
      .select()
      .from(Account)
      .leftJoin(ParentAccount, eq(ParentAccount.id, Account.parentId))
  )
However, the inferred type of accounts is: 
{
    account: never;
}[]
{
    account: never;
}[]
3 Replies
TOSL
TOSL•4d ago
Use the following instead import {alias} from 'drizzle-orm/pg-core' @johncmacy aliasedTable for the relational api only
johncmacy
johncmacyOP•3d ago
Thanks @TOSL, that did the trick. I think the docs could be improved in this area - here's where I got my attempted solution above from, and I can't find anything about your solution.
Drizzle ORM - Joins
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
TOSL
TOSL•3d ago
You're welcome. Yeah that is very misleading. Thankfully that's a easy fix. I'll make a PR.

Did you find this page helpful?