zid
zid•16mo ago

bidirectional relationships

Trying to determine the best schema for "friends" (many to many) feature. As someone who's not experienced on the SQL side of things, would love to know if im missing anything and/or there's a better approach altogether. The main features/capabilities for the friends feature will be to fetch a list of friends, as well as to update any meta data between these friends. Fetching a users friends is much more frequent. Bidirectional Approach:
defineSchema({
users: defineTable({
// user fields
}),
friends: defineTable({
lowerUserId: v.id("users"),
higherUserId: v.id("users"),
// any additional fields
})
.index("by_userIds", ["lowerUserId", "higherUserId"]),
});
defineSchema({
users: defineTable({
// user fields
}),
friends: defineTable({
lowerUserId: v.id("users"),
higherUserId: v.id("users"),
// any additional fields
})
.index("by_userIds", ["lowerUserId", "higherUserId"]),
});
Pros: Only one document per friendship, which means writes are atomic and there is less data to keep in sync. May be more intuitive from a data modeling perspective as a friendship is inherently a bidirectional relationship. Cons: More complex queries are required to find all friends of a given user. The sorting mechanism for user IDs must be strictly followed on every operation to ensure data consistency. Unidirectional Approach:
defineSchema({
users: defineTable({
// user fields
}),
friends: defineTable({
userId: v.id("users"),
friendId: v.id("users")
})
.index("by_userIds", ["userId"), // fetch all of users friends
.index("by_userIds_friendId", ["userId", "friendId")
});
defineSchema({
users: defineTable({
// user fields
}),
friends: defineTable({
userId: v.id("users"),
friendId: v.id("users")
})
.index("by_userIds", ["userId"), // fetch all of users friends
.index("by_userIds_friendId", ["userId", "friendId")
});
Pros: Simpler and more efficient queries for fetching all friends of a user. Cons: Requires two documents for each friendship, doubling the write operations. More complex logic needed to ensure both documents are kept in sync, despite transactions guaranteeing atomicity.
8 Replies
lee
lee•16mo ago
I would go with the unidirectional approach, with two relationship documents for each friendship. You shouldn't get inconsistencies because mutations are atomic. What kind of inconsistencies are you worried about? Having two documents makes inserting and fetching simpler, and gives you an extra place to put extra unidirectional fields about the friendship like "best friend" or "nickname"
zid
zidOP•16mo ago
Thanks Lee! Not worried about inconsistencies (just edited my post). And that latter point is great, thank you!
lee
lee•16mo ago
you might be worried about a buggy mutation creating an inconsistent relationship that doesn't get cleaned up when you fix the bug. if that sounds plausible, you could make a cron that looks at all data and detects inconsistencies, but you shouldn't need that unless you suspect dangling references
zid
zidOP•16mo ago
no im good, guanteed trnsactions should be more than enough hmm, i realize that for every friend that I fetch, I need to fetch a document from a separate table. This is mainly due to the fact that many of the fields are dynamic. is the following the proper/best way to create the query? This is a simplified version as I'll be using the usePaginated hook instead.
async function findAllFriendsWithDetails(ctx, userId) {
// First, find all friends for the user
const friends = await ctx.db.query("friends")
.withIndex("by_userId", q => q.eq("userId", userId))
.collect();

// Then, for each friend, fetch the corresponding friendDetails
const friendsWithDetailsPromises = friends.map(async (friend) => {
const friendDetail = await ctx.db.get("friendDetails", friend.friendId);
return {
...friend,
detail: friendDetail,
};
});

// Resolve all promises to get the details for all friends
const friendsWithDetails = await Promise.all(friendsWithDetailsPromises);

return friendsWithDetails;
}
async function findAllFriendsWithDetails(ctx, userId) {
// First, find all friends for the user
const friends = await ctx.db.query("friends")
.withIndex("by_userId", q => q.eq("userId", userId))
.collect();

// Then, for each friend, fetch the corresponding friendDetails
const friendsWithDetailsPromises = friends.map(async (friend) => {
const friendDetail = await ctx.db.get("friendDetails", friend.friendId);
return {
...friend,
detail: friendDetail,
};
});

// Resolve all promises to get the details for all friends
const friendsWithDetails = await Promise.all(friendsWithDetailsPromises);

return friendsWithDetails;
}
lee
lee•16mo ago
Looks good to me. Let us know if you have any issues
zid
zidOP•16mo ago
Hmm, I am getting an issue here.. TypeError: currResult.page is not iterable (cannot read property undefined)
export const getFriends = query({
args: {
paginationOpts: paginationOptsValidator,
userId: v.union(v.string(), v.null()),
},
handler: async ({ db }, { userId, paginationOpts }) => {
if (userId === null || userId === undefined) {
return;
}

const friends = await db
.query("friends")
.withIndex("by_userId", (q) => q.eq("userId", userId))
.order("desc")
.paginate(paginationOpts);
console.log("🚀 ~ handler: ~ friends:", friends);

// Then, for each friend, fetch the corresponding friendDetails
const friendsStatsPromises = friends.page.map(async (friend) => {
const friendStats = await db.get(friend.friendId);
return {
...friend,
...friendStats,
};
});

// Resolve all promises to get the details for all friends
const friendsStats = await Promise.all(
friendsStatsPromises
);
return friendsStats;
},
});
export const getFriends = query({
args: {
paginationOpts: paginationOptsValidator,
userId: v.union(v.string(), v.null()),
},
handler: async ({ db }, { userId, paginationOpts }) => {
if (userId === null || userId === undefined) {
return;
}

const friends = await db
.query("friends")
.withIndex("by_userId", (q) => q.eq("userId", userId))
.order("desc")
.paginate(paginationOpts);
console.log("🚀 ~ handler: ~ friends:", friends);

// Then, for each friend, fetch the corresponding friendDetails
const friendsStatsPromises = friends.page.map(async (friend) => {
const friendStats = await db.get(friend.friendId);
return {
...friend,
...friendStats,
};
});

// Resolve all promises to get the details for all friends
const friendsStats = await Promise.all(
friendsStatsPromises
);
return friendsStats;
},
});
I see the page property..
handler: ~ friends:' {
page: [
{
_creationTime: 1699289023475.6567,
_id: '7cghbzj11c6tpbjsc3spyg2g9k9ppsg',
friendId: '6qq1g8354jwwqxy5ngmq0g3p9k8kser',
userId: '6pvv03jd7q1mxyw560p40d589k9nr08'
}
],
isDone: true,
continueCursor: '074b05308ffa6112329c318d03617e72744dc397cd93f60624d0abde461d78ee06ac3c68002160e347f84b785354a97457a833058c047fe167e08ae00ca3f22981055d0392f12e343b3bc1e9f54ad6ae5a3c4c9c6703d3934d50d41065cd7583ffbd',
splitCursor: null,
pageStatus: null
}
handler: ~ friends:' {
page: [
{
_creationTime: 1699289023475.6567,
_id: '7cghbzj11c6tpbjsc3spyg2g9k9ppsg',
friendId: '6qq1g8354jwwqxy5ngmq0g3p9k8kser',
userId: '6pvv03jd7q1mxyw560p40d589k9nr08'
}
],
isDone: true,
continueCursor: '074b05308ffa6112329c318d03617e72744dc397cd93f60624d0abde461d78ee06ac3c68002160e347f84b785354a97457a833058c047fe167e08ae00ca3f22981055d0392f12e343b3bc1e9f54ad6ae5a3c4c9c6703d3934d50d41065cd7583ffbd',
splitCursor: null,
pageStatus: null
}
Made a workaround, however, not sure if this is stable/safe
export const getFriends = query({
args: {
paginationOpts: paginationOptsValidator,
userId: v.union(v.string(), v.null()),
},
handler: async ({ db }, { userId, paginationOpts }) => {
if (userId === null || userId === undefined) {
return;
}

const friends = await db
.query("friends")
.withIndex("by_userId", (q) => q.eq("userId", userId))
.order("desc")
.paginate(paginationOpts);
console.log("🚀 ~ handler: ~ friends:", friends);

// Then, for each friend, fetch the corresponding friendDetails
const friendsStatsPromises = friends?.page?.map(
async (friend) => {
console.log(
"🚀 ~ friendsStatsPromises ~ friend:",
friend.friendId
);
const friendStats = await db.get(friend.friendId);
return {
...friend,
...friendStats,
};
}
);

// Resolve all promises to get the details for all friends
const friendsStats = await Promise.all(
friendsStatsPromises
);

return {
isDone: friends.isDone,
continueCursor: friends.continueCursor,
splitCursor: friends.splitCursor,
pageStatus: friends.pageStatus,
page: friendsStats,
};
},
});
export const getFriends = query({
args: {
paginationOpts: paginationOptsValidator,
userId: v.union(v.string(), v.null()),
},
handler: async ({ db }, { userId, paginationOpts }) => {
if (userId === null || userId === undefined) {
return;
}

const friends = await db
.query("friends")
.withIndex("by_userId", (q) => q.eq("userId", userId))
.order("desc")
.paginate(paginationOpts);
console.log("🚀 ~ handler: ~ friends:", friends);

// Then, for each friend, fetch the corresponding friendDetails
const friendsStatsPromises = friends?.page?.map(
async (friend) => {
console.log(
"🚀 ~ friendsStatsPromises ~ friend:",
friend.friendId
);
const friendStats = await db.get(friend.friendId);
return {
...friend,
...friendStats,
};
}
);

// Resolve all promises to get the details for all friends
const friendsStats = await Promise.all(
friendsStatsPromises
);

return {
isDone: friends.isDone,
continueCursor: friends.continueCursor,
splitCursor: friends.splitCursor,
pageStatus: friends.pageStatus,
page: friendsStats,
};
},
});
lee
lee•16mo ago
Oh yeah i would do return {...friends, page: friendsStats} To copy over existing fields that pagination needs
zid
zidOP•16mo ago
Hmm, i realized that I need to fetch dynamic properties belonging to the friend. This means that with the unidirecitonal approach, i wouldn't be able to efficiently add these specific fields. The limit of friends for each user has not been determined yet, but if need be, the smallest number would ideally be atleast 1000 friends. With this in mind for the paginated query, i'm thinking of filtering and sorting in javascript...seems like this is the only way? Concerned about performance though
export const getFriends = query({
args: {
paginationOpts: paginationOptsValidator,
userId: v.union(v.string(), v.null()),
},
handler: async ({ db }, { userId, paginationOpts }) => {
// ...

// Resolve all promises to get the details for all friends
const friendsStats = await Promise.all(friendsStatsPromises);

if (friendsFilter === "111") {
friendsStats.sort((a, b) => b.prop - a.prop);
} else if (friendsFilter === "124") {
friendsStats.sort((a, b) => a.prop - b.prop);
} else if (friendsFilter === "515") {
friendsStats.filter((friend) => friend.prop === "515");
} else if (friendsFilter === "408") {
friendsStats.filter((friend) => friend.prop === "408");
} else if (friendsFilter === "533") {
friendsStats.filter((friend) => friend.prop === "533");
} else if (friendsFilter === "312") {
friendsStats.filter((friend) => friend.prop === "312");
} else if (friendsFilter === "123") {
friendsStats.filter((friend) => friend.prop === "123");
} else if (friendsFilter === "515") {
friendsStats.filter((friend) => friend.prop === "515");
} else if (friendsFilter === "125125") {
friendsStats.filter((friend) => friend.prop === "125125");
} else if (friendsFilter === "124125") {
friendsStats.filter((friend) => friend.prop === "124125");
} else if (friendsFilter === "234") {
friendsStats.filter((friend) => friend.prop === "234");
} else if (friendsFilter === "1355") {
friendsStats.filter((friend) => friend.prop === "1355");
}

return {
...friends,
page: friendsStats,
};
},
});
export const getFriends = query({
args: {
paginationOpts: paginationOptsValidator,
userId: v.union(v.string(), v.null()),
},
handler: async ({ db }, { userId, paginationOpts }) => {
// ...

// Resolve all promises to get the details for all friends
const friendsStats = await Promise.all(friendsStatsPromises);

if (friendsFilter === "111") {
friendsStats.sort((a, b) => b.prop - a.prop);
} else if (friendsFilter === "124") {
friendsStats.sort((a, b) => a.prop - b.prop);
} else if (friendsFilter === "515") {
friendsStats.filter((friend) => friend.prop === "515");
} else if (friendsFilter === "408") {
friendsStats.filter((friend) => friend.prop === "408");
} else if (friendsFilter === "533") {
friendsStats.filter((friend) => friend.prop === "533");
} else if (friendsFilter === "312") {
friendsStats.filter((friend) => friend.prop === "312");
} else if (friendsFilter === "123") {
friendsStats.filter((friend) => friend.prop === "123");
} else if (friendsFilter === "515") {
friendsStats.filter((friend) => friend.prop === "515");
} else if (friendsFilter === "125125") {
friendsStats.filter((friend) => friend.prop === "125125");
} else if (friendsFilter === "124125") {
friendsStats.filter((friend) => friend.prop === "124125");
} else if (friendsFilter === "234") {
friendsStats.filter((friend) => friend.prop === "234");
} else if (friendsFilter === "1355") {
friendsStats.filter((friend) => friend.prop === "1355");
}

return {
...friends,
page: friendsStats,
};
},
});

Did you find this page helpful?