Drizzle Team•4mo ago

RQB: Why is a where clause inside a many-to-many relation not allowed?

Say we have posts and tags. Every post can have multiple tags, but a tag can have many posts. So a classical many-to-many relation. Note: we have a junction table to store the relation posts 1-n postsToTags n-1 tag Now we want to fetch a post with all the tags. We can just do this:

db.query.postsTbl.findMany({
  where: (postsTbl, { eq }) => {
    return eq(postsTbl.owner, authId)
  },
  with: {
    postsToTags: {
      with: {
        tag: {
          columns: {
            id: true,
            label: true,
          },
        },
      },
    },
  },
});

db.query.postsTbl.findMany({
  where: (postsTbl, { eq }) => {
    return eq(postsTbl.owner, authId)
  },
  with: {
    postsToTags: {
      with: {
        tag: {
          columns: {
            id: true,
            label: true,
          },
        },
      },
    },
  },
});

But say we only want the tags that contain "hello" in the label:

db.query.postsTbl.findMany({
  where: (postsTbl, { eq }) => {
    return eq(postsTbl.owner, authId);
  },
  with: {
    postsToTags: {
      with: {
        tag: {
          // @ts-ignore
          where: (_, { like }) => like(tagsTbl.label, "%hello%"),
          columns: {
            id: true,
            label: true,
          },
        },
      },
    },
  },
});

db.query.postsTbl.findMany({
  where: (postsTbl, { eq }) => {
    return eq(postsTbl.owner, authId);
  },
  with: {
    postsToTags: {
      with: {
        tag: {
          // @ts-ignore
          where: (_, { like }) => like(tagsTbl.label, "%hello%"),
          columns: {
            id: true,
            label: true,
          },
        },
      },
    },
  },
});

Why is it not allowed in the types? Is there something that could easily break if we add a where clause to the subquery?

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

RQB: Why is a where clause inside a many-to-many relation not allowed?