How do I use `where` inside `with`?

There's this example on the query() page (https://orm.drizzle.team/docs/rqb)

await db.query.posts.findMany({
  where: (posts, { eq }) => (eq(posts.id, 1)),
  with: {
    comments: {
      where: (comments, { lt }) => lt(comments.createdAt, new Date()),
    },
  },
});

await db.query.posts.findMany({
  where: (posts, { eq }) => (eq(posts.id, 1)),
  with: {
    comments: {
      where: (comments, { lt }) => lt(comments.createdAt, new Date()),
    },
  },
});

But I'm getting a TypeScript error saying there could be no where inside with. I'm on drizzle-orm 0.29

Drizzle ORM - Query

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

2 Replies

subrahmanyamv•11mo ago

I too have the same issue. Where clause works when the relation is defined as "many", but when we define the relation as "one" and still want to be able to filter, this doesn't work. I used db.select() type of querying whenever I had this limitation with "findMany"

Нарбек•11mo ago

if you define the relation as "one" try this

    await db.query.posts.findMany({
        where: inArray(
            posts.id,
            await db.query.comments.findMany({
                where: eq(comments.someColumn, 'val'),
                columns: { id: true },
            }).map(comment => comment.id)
        ),
        with: {
            comments: true,
        },
    });

    await db.query.posts.findMany({
        where: inArray(
            posts.id,
            await db.query.comments.findMany({
                where: eq(comments.someColumn, 'val'),
                columns: { id: true },
            }).map(comment => comment.id)
        ),
        with: {
            comments: true,
        },
    });

@Mike Borozdin @subrahmanyamv

Gaming

Programming

How do I use `where` inside `with`?