DT
Drizzle TeamMr.Propre

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)
}));
M
Mr.Propre•166d 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
A
Angelelz•166d ago
Can you show the userTasksRelations?
K
Kairu•166d 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.
K
Kairu•166d 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.
M
Mr.Propre•164d 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
K
Kairu•164d 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
M
Mr.Propre•164d ago
in their example it clearly is possible
K
Kairu•164d ago
the where key is only available on many relations
M
Mr.Propre•162d 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],
  }),
}));
A
Angelelz•162d 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))
      )
    ),
  });
Want results from more Discord servers?
Add your server
More Posts
Drizzle Studio with AWS Aurora ServerlessHello All! I am having some troubles to run the Drizzle Studio Console. I am using an AWS RDS Auroreslint-plugin-drizzle add "db" as defaultIs there a reason why it is automatically applied for every delete function? I think most users woulDoes Promise.all work correct in transactions?As I just found out, pg only works with one query (or more if there is more than one connection), soFindFirst Config Optional `with`Hi, I am trying to do something like an optional findFirst config here. i am expecting the resultTypSupabase primary key typeWhen I run `introspect`, the primary key of my tables look something like this: ```id: bigint("id", Supabase/postgres get query is stuck on prod appHi, I'm coming across an issue with my discord bot, I currently have a query that checks if an usercreate exportable types for frontend servicesI feel like I'm missing something fundamental. I can create my Drizzle schema `user = mysqlTable('uno migrations for pgenums in drizzle-kitnot able to see migrations generated for pgenums in drizzle kit, is this not implemented yet or maybProperty '[IsDrizzleTable]' is missing in typeHello! I've just updated to drizzle-orm@0.29.1 and I can't use drizzle-zod anymore (@0.5.1). This Return array when parsing a schema with drizzle-valibotHello guys, I just started using drizzle-valibot and I'm not able to find in their docs nor yours hoMysql json field in where clause escaping the field namecan some one help me i am traying to create a eq(sql`${schedules.extraFields}->>'user'`, userId),, What is the best way to create conditional filters on a query?I am building an marketplace for rural real estate. I would like to build a filter system with multiCreating zod insert schema from Query with relationsI am using a Query FindFirst, with multiple relations enabled. I can infer the schema from this usin`.orderBy` influence `sql` outputWith the following code: ```ts const exchanges = await db .select({ ...getTableColumns(sDo I have to import table name type for select query?Doing this simple query: ``` dbDrizzle.select({ id: user.id }).from(user) ``` I get no inCustom SQL in migrationsTLDR: can I change the SQL in migrations or is that considered bad practice? Alright, I need some aTypeError: Cannot read properties of undefined (reading 'type') on drizzle kit push commandCan't run drizzle push:mysql on planetscale database, I'm getting this weird error: ``` + Auditors Bypass migrations when updating Planetscale schema on dev branchesHi there, Sorry, I'm a beginner in MySQL and databases, but I was wondering if there was a way to pIndex not being created in tableHi! I'm using `drizzle-kit push:pg` to update my local dev database. When I use this command, indiceDocumentation Nav menu broken on mobileI cant see the sub menus to navigate around the docs on mobile