Drizzle Team•16mo ago

Nullable relational query?

In the following, shouldn't res.jobSeekerProfile possibly be null? Basically what I want is to get the user - Include their jobSeekerProfile (if they have one). Currently, it seems to assume that jobSeekerProfile always exists for the user

let test = db.query.users.findFirst({
  where: (users, { eq }) => eq(users.id, '1'),
  with: {
    jobSeekerProfile: true,
  },
})

test.then((res) => {
  if (res) {
    res.jobSeekerProfile
  }
})

let test = db.query.users.findFirst({
  where: (users, { eq }) => eq(users.id, '1'),
  with: {
    jobSeekerProfile: true,
  },
})

test.then((res) => {
  if (res) {
    res.jobSeekerProfile
  }
})

It's telling me it's this type:

res: {
    id: string;
    name: string | null;
    email: string;
    emailVerified: Date | null;
    image: string | null;
    createdAt: Date;
    updatedAt: Date;
    jobSeekerProfile: {
        id: string;
        userId: string;
        bio: string | null;
        cvUrl: string | null;
    };
}

res: {
    id: string;
    name: string | null;
    email: string;
    emailVerified: Date | null;
    image: string | null;
    createdAt: Date;
    updatedAt: Date;
    jobSeekerProfile: {
        id: string;
        userId: string;
        bio: string | null;
        cvUrl: string | null;
    };
}

Shouldn't jobSeekerProfile be nullable? This is the schema:

export const users = pgTable('users', {
  id: text('id').notNull().primaryKey(),
  name: text('name'),
  email: text('email').notNull(),
  emailVerified: timestamp('emailVerified', { mode: 'date' }),
  image: text('image'),
  createdAt: timestamp('createdAt', { mode: 'date' }).notNull(),
  updatedAt: timestamp('updatedAt', { mode: 'date' }).notNull(),
})

export const userRelations = relations(users, ({ one }) => ({
  jobSeekerProfile: one(jobSeekerProfiles, {
    fields: [users.id],
    references: [jobSeekerProfiles.userId],
  }),
}))

export const jobSeekerProfiles = pgTable('jobSeekerProfiles', {
    id: text('id').notNull().primaryKey(),
    userId: text('userId')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    bio: text('bio'),
    cvUrl: text('cvUrl'),
  })

export const users = pgTable('users', {
  id: text('id').notNull().primaryKey(),
  name: text('name'),
  email: text('email').notNull(),
  emailVerified: timestamp('emailVerified', { mode: 'date' }),
  image: text('image'),
  createdAt: timestamp('createdAt', { mode: 'date' }).notNull(),
  updatedAt: timestamp('updatedAt', { mode: 'date' }).notNull(),
})

export const userRelations = relations(users, ({ one }) => ({
  jobSeekerProfile: one(jobSeekerProfiles, {
    fields: [users.id],
    references: [jobSeekerProfiles.userId],
  }),
}))

export const jobSeekerProfiles = pgTable('jobSeekerProfiles', {
    id: text('id').notNull().primaryKey(),
    userId: text('userId')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    bio: text('bio'),
    cvUrl: text('cvUrl'),
  })

9 Replies

DiamondDragon•16mo ago

Try deleting the fields and references keys of the relation table and I think it should allow undefined

ꚂimonOP•16mo ago

Oh! That did it. Not sure I understand the relations table then 😮 So when should you be defining the fields and references?

DiamondDragon•16mo ago

@Dan Kochetov @Andrew Sherman y’all probably should add some documentation on what fields and references means with regards to relations It’s one of the more frequent questions being asked

Dan•16mo ago

you need to define the fields and relations on the opposite side

DiamondDragon•16mo ago

I’m sure one of us could chatGPT our way to explain the underlying typescript lol

Dan•16mo ago

in general, you need to define fields and references on the relation side that "knows" about both tables i.e. the one that might have the foreign key I know that it's not well-explained in the docs, we're working on improving those bits

AlcaponeYou•9mo ago

@Dan Kochetov hi, is it possible to use the relations on a nullable field? When I try to query using with: { maybeNull: true }, it throws an error: Cannot read properties of undefined (reading 'column's). When maybeNull isn't null, the query works.

Dan•9mo ago

should be possible in any case, we've designed a new API for defining relations, so some of the issues will be solved when it lands

AlcaponeYou•9mo ago

Awesome, looking forward to it. Do you have an ETA on the release? Sorry this is entirely my fault. The error I got wasa b/c I forgot to export the reference model. Relational queries on nullable fields are working.

Gaming

Programming

Nullable relational query?