DT
Drizzle Team•12mo ago
Mike Borozdin

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
subrahmanyamv•12mo 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"
Нарбек
Нарбек•12mo 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

Did you find this page helpful?