relation select query with where condition

I want to select all user's tasks where parent id is null. Can someone help me fix this query please:

const result = await db.query.usersTasks.findMany({
    with: {
      tasks: {
        with: {
          subtasks: true,
        },
        where: isNull(tasks.parentId),
      },
    },
    where: eq(usersTasks.userId, id),
  });

const result = await db.query.usersTasks.findMany({
    with: {
      tasks: {
        with: {
          subtasks: true,
        },
        where: isNull(tasks.parentId),
      },
    },
    where: eq(usersTasks.userId, id),
  });

these are the tables in question :

export const users = mysqlTable("users", {
  userId: varchar("userId", { length: 256 }).primaryKey().notNull().unique(),
  theme: mysqlEnum("theme", ["DARK", "LIGHT"]).default("DARK"),
  createdAt: timestamp("created_at").default(sql`CURRENT_TIMESTAMP`),
  updatedAt: timestamp("updated_at").default(sql`CURRENT_TIMESTAMP`),
});
export const usersTasks = mysqlTable(
  "users_tasks",
  {
    id: int("id").primaryKey().autoincrement(),
    userId: varchar("user_id", { length: 256 }).references(() => users.userId),
    taskId: int("tas_id").references(() => tasks.id),
    createdAt: timestamp("created_at").default(sql`CURRENT_TIMESTAMP`),
    updatedAt: timestamp("updated_at").default(sql`CURRENT_TIMESTAMP`),
  }
);
export const tasks = mysqlTable(
  "tasks",
  {
    id: int("id").primaryKey().autoincrement(),
    parentId: int("parent_id").references((): AnyMySqlColumn => tasks.id),
    title: varchar("title", { length: 256 }).notNull(),
    description: varchar("description", { length: 2000 }),
    dueDate: datetime("due_date"),
    createdAt: timestamp("created_at").default(sql`CURRENT_TIMESTAMP`),
    updatedAt: timestamp("updated_at").default(sql`CURRENT_TIMESTAMP`),
  },
  (table) => ({
    parentIdx: index("parent_idx").on(table.parentId)
  })
);

export const usersRelations = relations(users, ({ many }) => ({
  userTasks: many(usersTasks)
}));
export const tasksRelations = relations(tasks, ({ many, one }) => ({
  userTasks: many(usersTasks),
  subtasks: many(tasks, { relationName: "subtasks" }),
  parent: one(tasks)
}));

export const users = mysqlTable("users", {
  userId: varchar("userId", { length: 256 }).primaryKey().notNull().unique(),
  theme: mysqlEnum("theme", ["DARK", "LIGHT"]).default("DARK"),
  createdAt: timestamp("created_at").default(sql`CURRENT_TIMESTAMP`),
  updatedAt: timestamp("updated_at").default(sql`CURRENT_TIMESTAMP`),
});
export const usersTasks = mysqlTable(
  "users_tasks",
  {
    id: int("id").primaryKey().autoincrement(),
    userId: varchar("user_id", { length: 256 }).references(() => users.userId),
    taskId: int("tas_id").references(() => tasks.id),
    createdAt: timestamp("created_at").default(sql`CURRENT_TIMESTAMP`),
    updatedAt: timestamp("updated_at").default(sql`CURRENT_TIMESTAMP`),
  }
);
export const tasks = mysqlTable(
  "tasks",
  {
    id: int("id").primaryKey().autoincrement(),
    parentId: int("parent_id").references((): AnyMySqlColumn => tasks.id),
    title: varchar("title", { length: 256 }).notNull(),
    description: varchar("description", { length: 2000 }),
    dueDate: datetime("due_date"),
    createdAt: timestamp("created_at").default(sql`CURRENT_TIMESTAMP`),
    updatedAt: timestamp("updated_at").default(sql`CURRENT_TIMESTAMP`),
  },
  (table) => ({
    parentIdx: index("parent_idx").on(table.parentId)
  })
);

export const usersRelations = relations(users, ({ many }) => ({
  userTasks: many(usersTasks)
}));
export const tasksRelations = relations(tasks, ({ many, one }) => ({
  userTasks: many(usersTasks),
  subtasks: many(tasks, { relationName: "subtasks" }),
  parent: one(tasks)
}));

10 Replies

Mr.PropreOP•13mo ago

the problem here is that where: isNull(tasks.parentId) is an error the error says Object literal may only specify known properties, and where does not exist in type

Angelelz•13mo ago

Can you show the userTasksRelations?

Kairu•13mo ago

there's another thread about it here https://discord.com/channels/1043890932593987624/1176166096777248829/1176166096777248829 the documentation suggests this is possible here https://orm.drizzle.team/docs/rqb#where--filters but for some reason the types don't let you run a where on a relation using the query builder.

Drizzle Queries - Drizzle ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Kairu•13mo ago

for my current use case i've gotten around it with a join as i don't need to aggregate like here https://orm.drizzle.team/docs/joins#aggregating-results but in the future i'd really like to avoid this aggregating, and just use the query builder

Joins [SQL] - Drizzle ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Mr.PropreOP•13mo ago

i have the same solution right now, but it's kind of ugly too many loops just to reformat the result array 😅 this is exactly what i saw and it confused me

Kairu•13mo ago

in your case it should work fine as its filtering the relation. for me, i'm trying to filter the parent based on the relation content, which doesn't seem possible

Mr.PropreOP•13mo ago

in their example it clearly is possible

Kairu•13mo ago

the where key is only available on many relations

Mr.PropreOP•13mo ago

i didn't read you comment the first time, sorry 😅

export const usersTasksRelations = relations(usersTasks, ({ one }) => ({
  users: one(users, {
    fields: [usersTasks.userId],
    references: [users.userId],
  }),
  tasks: one(tasks, {
    fields: [usersTasks.taskId],
    references: [tasks.id],
  }),
}));

export const usersTasksRelations = relations(usersTasks, ({ one }) => ({
  users: one(users, {
    fields: [usersTasks.userId],
    references: [users.userId],
  }),
  tasks: one(tasks, {
    fields: [usersTasks.taskId],
    references: [tasks.id],
  }),
}));

Angelelz•13mo ago

The problem is that usersTasks has only one task. A where is not allowed in a one relation. Because if it works, it would return null That's how you have it right now anyway It looks like what you want is to select is only the usersTasks whose tasks has a parentId = null? In the RQB you can't filter a table by a nested relation But you can workaround it with a subquery

const result = await db.query.usersTasks.findMany({
    with: {
      tasks: {
        with: {
          subtasks: true,
        },
      },
    },
    where: and(
      eq(usersTasks.userId, id),
      inArray(
        usersTasks.taskId,
        db.select({id: tasks.id}).from(tasks).where(isNull(tasks.parentId))
      )
    ),
  });

const result = await db.query.usersTasks.findMany({
    with: {
      tasks: {
        with: {
          subtasks: true,
        },
      },
    },
    where: and(
      eq(usersTasks.userId, id),
      inArray(
        usersTasks.taskId,
        db.select({id: tasks.id}).from(tasks).where(isNull(tasks.parentId))
      )
    ),
  });

Gaming

Programming

relation select query with where condition