Beerus-sama•2mo ago

what is the BEST way to do left-join query like in Convex?

schema: users { userId, name } post { postId, userId, post } relationship: users.userId = post.userId query: get all post with user.name on it. HELP. (best/fastest and convex approach)

5 Replies

BluePenguin•2mo ago

db.query("post").withIndex("by_user", q => q.eq(q.field("userId"), userId)) You do need an index in that case:

post: defineSchema({
  ...,
  userId: v.id("users"),
  ...,
}).index("by_user", ["userId"])

post: defineSchema({
  ...,
  userId: v.id("users"),
  ...,
}).index("by_user", ["userId"])

Be aware this is not a join -> it's just a separate read filter is simpler, but doesn't scale and will count as a full-table read in usage. Fine if you're below 1000 documents, you can always drop in index later. db.query("post").filter(q => q.eq(q.field("userId"), userId))

Beerus-samaOP•2mo ago

@BluePenguin - it did not get the users data.. here's my query.. ctx.db.query("sample") .withIndex("by_users", q => q .eq("usersId", 'k175jrw7kjyt0d1nmrn3j8j1f57rpjgr' as Id("users"))) .collect();

Clever Tagline•2mo ago

It didn't get the user data because you weren't querying the users table. Your original question asked how to get all posts for the user, and that's what that query does. To get the user data, you would need to get the user document as well. If you want that to be returned with the user's posts, you could include that in the same query function. Something like this:

export default getUserAndPosts = query({
  args: {
    userId: v.id("users"),
  },
  handler: async (ctx, { userId }) => {
    const user = ctx.db.get(userId)
    const posts = ctx.db.query("sample")
      .withIndex("by_users", q => q.eq("usersId", userId))
      .collect();
    return { user, posts }
  }
})

export default getUserAndPosts = query({
  args: {
    userId: v.id("users"),
  },
  handler: async (ctx, { userId }) => {
    const user = ctx.db.get(userId)
    const posts = ctx.db.query("sample")
      .withIndex("by_users", q => q.eq("usersId", userId))
      .collect();
    return { user, posts }
  }
})

Beerus-samaOP•2mo ago

@Clever Tagline - my question is "get all post with user.name on it.", which is related to the title about "left-join". the expectation should be like.. { _id: 'post_id here', userId: 'user_id', post: 'this is a sample post', name: 'this is the name from user table' } cc: @BluePenguin

Clever Tagline•2mo ago

Thanks for the clarification. The wording threw me a bit, and I'm not familiar with left joins. If you want to get all posts for a specific user and add their name to the post data, this variation of my function above should do the trick (also updated to await the queries; not sure how my brain blanked on that the first time):

export default getUserAndPosts = query({
  args: {
    userId: v.id("users"),
  },
  handler: async (ctx, { userId }) => {
    const user = await ctx.db.get(userId)
    const posts = await ctx.db.query("sample")
      .withIndex("by_users", q => q.eq("usersId", userId))
      .collect();
    return posts.map(post => ({
      ...post,
      name: user.name
    })
  }
})

export default getUserAndPosts = query({
  args: {
    userId: v.id("users"),
  },
  handler: async (ctx, { userId }) => {
    const user = await ctx.db.get(userId)
    const posts = await ctx.db.query("sample")
      .withIndex("by_users", q => q.eq("usersId", userId))
      .collect();
    return posts.map(post => ({
      ...post,
      name: user.name
    })
  }
})

If you literally want to collect all posts and return them in that format, the code wouldn't be much different:

export default getPostsWithUsername = query({
  handler: async (ctx, { userId }) => {
    const allUsers = await ctx.db.query("users").collect()
    const posts = await ctx.db.query("sample").collect()
    return posts.map(post => ({
      ...post,
      name: allUsers.find(user => user._id === post.userId)?.name ?? "Unknown user"
    })
  }
})

export default getPostsWithUsername = query({
  handler: async (ctx, { userId }) => {
    const allUsers = await ctx.db.query("users").collect()
    const posts = await ctx.db.query("sample").collect()
    return posts.map(post => ({
      ...post,
      name: allUsers.find(user => user._id === post.userId)?.name ?? "Unknown user"
    })
  }
})

However, if you have a high post count, I wouldn't recommend that approach because full table reads are expensive. A paginated query would be more efficient for that. It would generally follow a similar process, though it could be optimized further to only collect the users for a single page of posts.

Paginated Queries | Convex Developer Hub

Load paginated queries

what is the BEST way to do left-join query like in Convex?

Did you find this page helpful?