Llabe•12mo ago

Retrieving many-to-many joins

Hey, is there a way to just select so fields to retrieve? Because I querying a table taht contains the userId and the teamId, and I would like to just get the teamId's in order to then query the data of those ID and return it. (In order to show the user all the teams where its a member).

6 Replies

erquhart•12mo ago

ctx.db.query()/.get() will always return entire records, no way to limit to specific fields. You're describing a many-to-many relationship, here's how to handle it Convex:

// Get userTeam records for the current user
const userTeams = await ctx.db.query('userTeams').withIndex('by_userId', q => q.eq('userId', args.userId)).collect()

// Get team records for user's teams, concurrently
const teams = Promise.all(userTeams, userTeam => ctx.db.get(userTeam.teamId))

// Get userTeam records for the current user
const userTeams = await ctx.db.query('userTeams').withIndex('by_userId', q => q.eq('userId', args.userId)).collect()

// Get team records for user's teams, concurrently
const teams = Promise.all(userTeams, userTeam => ctx.db.get(userTeam.teamId))

There are a number of stack articles and helpers for relationships in Convex, here's more on the basic many-to-many pattern: https://stack.convex.dev/relationship-structures-let-s-talk-about-schemas#relationship-table-scalable

Relationship Structures: Let's Talk About Schemas

In this post we’ll look at some patterns for structuring relationships in the Convex database.

trace•12mo ago

This is how I did it in my application

  // Query for groups the user has joined
    const joinedGroupMemberships = await ctx.db
      .query("group_members")
      .withIndex("by_user", (q) => q.eq("userId", args.id))
      .collect();
    const joinedGroups = await Promise.all(
      joinedGroupMemberships.map(async (membership) => {
        const group = await ctx.db.get(membership.groupId);
        return { ...membership, group };
      }),
    );

  // Query for groups the user has joined
    const joinedGroupMemberships = await ctx.db
      .query("group_members")
      .withIndex("by_user", (q) => q.eq("userId", args.id))
      .collect();
    const joinedGroups = await Promise.all(
      joinedGroupMemberships.map(async (membership) => {
        const group = await ctx.db.get(membership.groupId);
        return { ...membership, group };
      }),
    );

trace•12mo ago

Link: https://github.com/trace2798/convex_feedit/blob/master/convex/users.ts Line 161-170

GitHub

convex_feedit/convex/users.ts at master · trace2798/convex_feedit

PostIT: Share it with your group. This is a web application where users can create group and post. - trace2798/convex_feedit

trace•12mo ago

That application is currently deployed you can see the outcome here https://postit-omega.vercel.app/u/kh764btfffnsrpm0s6c5593yyh6n8456

postit-omega.vercel.app

PostIT - Share with your group and the world

A fullstack web application for Convex Zero to One hackathon

trace•12mo ago

@Llabe this thread will work

LlabeOP•12mo ago

Perfect Thankss! I'll give it a look now 🙂

Retrieving many-to-many joins

Did you find this page helpful?