How to describe where statement for null children?

[perevezencev]

Hello everyone. I have this code:

export type CategoryId = number & { __typeName: 'Category' };

export const categories = pgTable(
  'categories',
  {
    id: uuid('id').$type<CategoryId>().defaultRandom().primaryKey(),
    createdAt: timestamp('createdAt').defaultNow().notNull(),
    title: varchar('title').notNull(),
    description: text('description').notNull(),
    parentId: uuid('parentId').references((): AnyPgColumn => categories.id),
  },
)

export const categoriesRelation = relations(categories, ({ many, one }) => ({
  subcategories: many(categories, { relationName: "subcategories" }),
  parent: one(categories, {
    fields: [categories.parentId],
    references: [categories.id],
    relationName: "subcategories"
  })
}))

export type Category = typeof categories.$inferSelect;

How to describe where statement, for get all category where children is empty?

[Angelelz]

Children is not a column on the actual table. You are trying to find a category that doesn't have any descendants, meaning, a category whose id is not present in any other category's parentId. Here's a solution:

drizzle.query.categories.findMany({
  where: notInArray(categories.id, db.select({ parentId: categories.parentId }).from(categories))
})

It's basically a subquery.

[Angelelz]

When that error happens there is a missing part of the relation. Can you share what your schema look like?

[perevezencev]

I fixed error by adding: parentId: uuid('parentId').references((): AnyPgColumn => categories.id),

Now my scheme look like this:

export const categories = pgTable(
  'categories',
  {
    id: uuid('id').$type<CategoryId>().defaultRandom().primaryKey(),
    createdAt: timestamp('createdAt').defaultNow().notNull(),
    title: varchar('title').notNull(),
    description: text('description').notNull(),
    parentId: uuid('parentId').references((): AnyPgColumn => categories.id),
  },
)

export const categoriesRelation = relations(categories, ({ many, one }) => ({
  subcategories: many(categories, { relationName: "subcategories" }),
  parent: one(categories, {
    fields: [categories.parentId],
    references: [categories.id],
    relationName: "subcategories"
  })
}))

but this code

    const items = await drizzle.query.categories.findMany({
      with: {
        subcategories: true,
      },
      where: notInArray(
        categories.id,
        drizzle
          .select({
            parentId: categories.parentId,
          })
          .from(categories),
      )
    })

still not work

[perevezencev]

The SQL query looks different:

select "categories"."id", "categories"."createdAt", "categories"."title", "categories"."description", "categories"."parentId", "categories_subcategories"."data" as "subcategories" from "categories" left join lateral (select coalesce(json_agg(json_build_array("categories_subcategories"."id", "categories_subcategories"."createdAt", "categories_subcategories"."title", "categories_subcategories"."description", "categories_subcategories"."parentId")), '[]'::json) as "data" from "categories" "categories_subcategories" where "categories_subcategories"."parentId" = "categories"."id") "categories_subcategories" on true where "categories"."id" not in (select "parentId" from "categories")

[Angelelz]

I recently bumped into another issues and that one reminded me of this one. This solution should work but the uuid field is not a text field, so you need to cast it like this:

drizzle.query.categories.findMany({
  where: notInArray(sql`${categories.id}::text`, db.select({ parentId: sql`${categories.parentId}::text` }).from(categories))
})

I believe this query should work.

[perevezencev]

Unfortunately this code doesn't work either