Drizzle Team•12mo ago

Limitation in Drizzle? findMany does not work the same as an innerjoin, is it possible?

So I want to get all posts with supporters with a specific user_id. Q1 gives me that but I'd like to do it with Q2 but Q2 gives me all posts instead of just the ones that the user_id is supporting.

Q1:
const result = await db
  .select()
  .from(posts)
  .innerJoin(supporters, eq(posts.id, supporters.idea_id))
  .innerJoin(users, eq(supporters.user_id, users.id))
  .where(eq(users.id, user_id))
  .all();

Q2:
const result = await db.query.posts.findMany({
  with: {
    supporters: {
      where: (supporters, { eq }) => eq(supporters.user_id, user_id)
    }
  }
});

Q1:
const result = await db
  .select()
  .from(posts)
  .innerJoin(supporters, eq(posts.id, supporters.idea_id))
  .innerJoin(users, eq(supporters.user_id, users.id))
  .where(eq(users.id, user_id))
  .all();

Q2:
const result = await db.query.posts.findMany({
  with: {
    supporters: {
      where: (supporters, { eq }) => eq(supporters.user_id, user_id)
    }
  }
});

UPDATE I went this Q3 which works fine.

Q3:
const supporterResult = await db.query.supporters.findMany({
    where: (supporters, { eq }) => eq(supporters.user_id, user_id)
});
const ideaIds = supporterResult.map((supporter) => supporter.idea_id);

const result = await db.query.posts.findMany({
    with: {
        supporters: true
    },
    where: (posts) => inArray(posts.id, ideaIds)
});

Q3:
const supporterResult = await db.query.supporters.findMany({
    where: (supporters, { eq }) => eq(supporters.user_id, user_id)
});
const ideaIds = supporterResult.map((supporter) => supporter.idea_id);

const result = await db.query.posts.findMany({
    with: {
        supporters: true
    },
    where: (posts) => inArray(posts.id, ideaIds)
});

2 Replies

Angelelz•12mo ago

This is known, the RQB doesn't support filtering by nested relations Your workaround works but you could do this in a single query

const ideaIds = db
  .select({id: supporter.idea_id })
  .from(supporters)
  .where(eq(supporters.user_id, user_id))

const result = await db.query.posts.findMany({
    with: {
        supporters: true
    },
    where: (posts) => inArray(posts.id, ideaIds)
});

const ideaIds = db
  .select({id: supporter.idea_id })
  .from(supporters)
  .where(eq(supporters.user_id, user_id))

const result = await db.query.posts.findMany({
    with: {
        supporters: true
    },
    where: (posts) => inArray(posts.id, ideaIds)
});

This will send the ideaIds as a subquery inside the RQB, in only one request

stenOP•12mo ago

Thanks for the input @Angelelz. I just saw the 0.28.0 update after I posted here

GitHub

Release 0.28.0 · drizzle-team/drizzle-orm

Breaking changes Removed support for filtering by nested relations Current example won't work in 0.28.0: const usersWithPosts = await db.query.users.findMany({ where: (table, { sql }) => (...

Gaming

Programming

Limitation in Drizzle? findMany does not work the same as an innerjoin, is it possible?