oscklm•4mo ago

Designing a friend system with Convex

Hi, I’m building a friend system with Convex and am torn between two approaches: 1. Multiple indexes for better scalability. 2. Multiple entries in a single table for each friendship. I’ve implemented lexicographical sorting for user1 and user2 when inserting friendships, but when querying friendships for a user, the following approach is limited by the compound index:

const friendshipsAsUser1 = await ctx.db
  .query('userFriend')
  .withIndex('by_userIds', (q) => q.eq('user1', userId))
  .collect();

const friendshipsAsUser2 = await ctx.db
  .query('userFriend')
  .withIndex('by_userIds', (q) => q.eq('user2', userId))
  .collect();

const friendshipsAsUser1 = await ctx.db
  .query('userFriend')
  .withIndex('by_userIds', (q) => q.eq('user1', userId))
  .collect();

const friendshipsAsUser2 = await ctx.db
  .query('userFriend')
  .withIndex('by_userIds', (q) => q.eq('user2', userId))
  .collect();

The problem is that compound indexes require the query order to match the order in which the fields were indexed (e.g., ['user1', 'user2']), which limits flexibility in querying. I’ve come up with a solution where id just do multiple entries, 1 for each side of the friendship, but I’m concerned about scalability. Is there a better way to handle this without creating multiple entries for each friendship or excessive indexes? I'm very curious how to tackle this, specifically if there is any way that goes great with Convex?

3 Replies

Convex Bot•4mo ago

Thanks for posting in <#1088161997662724167>. Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets. - Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.) - Use search.convex.dev to search Docs, Stack, and Discord all at once. - Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI. - Avoid tagging staff unless specifically instructed. Thank you!

erquhart•4mo ago

Relevant: https://discord.com/channels/1019350475847499849/1262459701275136172/1262459701275136172

oscklmOP•4mo ago

Thanks! that helps in making a decision. Will go with duplicate entries

Designing a friend system with Convex

Did you find this page helpful?