P
Prisma2mo ago
Florian

Tips on designing this recursive database model

In my Twitter clone, a post sometimes needs to be displayed by itself, and sometimes together with its parent post. I do this by including the replyToPost in the query. But every post can also be a repost of another post, so both the post and the ´replyToPost need to include a potential repostOfPost. And each post can also contain a quote of another post. So there are 3 of these relationships in my model. To get the data I want, I needed to build this very confusing includes` setup. Is there a better approach than this?
No description
7 Replies
Florian
Florian2mo ago
function getPostInclude(loggedInUserId: string | undefined) {
return Prisma.validator<Prisma.PostInclude>()({
user: {
select: getUserProfileSelect(loggedInUserId),
},
reposts: {
where: {
userId: loggedInUserId,
},
select: {
userId: true,
},
},
likes: {
where: {
userId: loggedInUserId,
},
select: {
userId: true,
},
},
bookmarks: {
where: {
userId: loggedInUserId,
},
select: {
userId: true,
},
},
attachments: true,
_count: {
select: {
likes: true,
replies: true,
reposts: true,
views: true,
},
},
});
}

function getPostWithQuoteInclude(loggedInUserId: string | undefined) {
return Prisma.validator<Prisma.PostInclude>()({
...getPostInclude(loggedInUserId),
quoteOfPost: { include: getPostInclude(loggedInUserId) },
});
}

export function getPostWithRepostInclude(loggedInUserId: string | undefined) {
return Prisma.validator<Prisma.PostInclude>()({
...getPostWithQuoteInclude(loggedInUserId),
repostOfPost: { include: getPostWithQuoteInclude(loggedInUserId) },
});
}

export function getPostWithReplyToInclude(loggedInUserId: string | undefined) {
return Prisma.validator<Prisma.PostInclude>()({
...getPostWithRepostInclude(loggedInUserId),
replyToPost: { include: getPostWithRepostInclude(loggedInUserId) },
});
}
function getPostInclude(loggedInUserId: string | undefined) {
return Prisma.validator<Prisma.PostInclude>()({
user: {
select: getUserProfileSelect(loggedInUserId),
},
reposts: {
where: {
userId: loggedInUserId,
},
select: {
userId: true,
},
},
likes: {
where: {
userId: loggedInUserId,
},
select: {
userId: true,
},
},
bookmarks: {
where: {
userId: loggedInUserId,
},
select: {
userId: true,
},
},
attachments: true,
_count: {
select: {
likes: true,
replies: true,
reposts: true,
views: true,
},
},
});
}

function getPostWithQuoteInclude(loggedInUserId: string | undefined) {
return Prisma.validator<Prisma.PostInclude>()({
...getPostInclude(loggedInUserId),
quoteOfPost: { include: getPostInclude(loggedInUserId) },
});
}

export function getPostWithRepostInclude(loggedInUserId: string | undefined) {
return Prisma.validator<Prisma.PostInclude>()({
...getPostWithQuoteInclude(loggedInUserId),
repostOfPost: { include: getPostWithQuoteInclude(loggedInUserId) },
});
}

export function getPostWithReplyToInclude(loggedInUserId: string | undefined) {
return Prisma.validator<Prisma.PostInclude>()({
...getPostWithRepostInclude(loggedInUserId),
replyToPost: { include: getPostWithRepostInclude(loggedInUserId) },
});
}
Some feeds render parent posts if one exists:
{posts.map((post) => (
<Fragment key={post.id}>
{post.replyToPost && (
<Post post={post.replyToPost} showReplyIndicatorBottom />
)}
<Post post={post} showReplyIndicatorTop={!!post.replyToPost} />
<hr />
</Fragment>
))}
{posts.map((post) => (
<Fragment key={post.id}>
{post.replyToPost && (
<Post post={post.replyToPost} showReplyIndicatorBottom />
)}
<Post post={post} showReplyIndicatorTop={!!post.replyToPost} />
<hr />
</Fragment>
))}
If the post is a repost of another post, the Post.tsx component detects this and decides which post to show:
const postToDisplay = post.repostOfPost ?? post;
const postToDisplay = post.repostOfPost ?? post;
This all works but the types are really messy
jonfanz
jonfanz2mo ago
It’s a bit late in my day, but I think one change might make your structure and queries a lot nicer. Posts can be replies to other posts. That you can model by setting a “parent” or “replyTo” on a post. Then, you have to handle normal posts, reposts, and quote posts. In my opinion, it would be a lot easier to model all of these as the same thing. A normal post is a post with text, attachments, etc. A repost is a post with no text attachments, etc, but with a foreign key “repost” pointing to another post. A quote post is both. A post with text, attachments, etc that also references another post via a “repost” field. Does that make sense?
Florian
Florian2mo ago
Yes but this is almost what I have, isn't it? I have a repostOfId FK. I could get rid of the quoteOfPostId with your suggestions, but would that make the structure a lot easier? In my JS code, the additional distinction helps
jonfanz
jonfanz2mo ago
If the distinction helps you then I would keep it 🙂 My suggestion was if you’re looking to simplify the queries, then reducing the number of joins you have to do would be the first thing I would look at
Florian
Florian2mo ago
This will become even more complex if I ever need to add more FKs and query them. I was wondering if my whole DB structure is wrong. It already feels impossible to work with in its current form I'm not a database expert I'll try your suggestion and remove the separation between quotes and reposts. It will only get rid of one FK but maybe that's enough. A related question: Is it correct to create functions like getPostInclude around Prisma.validator? I do that because I need the authenticated user's ID in the query.
jonfanz
jonfanz2mo ago
If you're using TypeScript, the satisfies operator might be better: https://www.prisma.io/blog/satisfies-operator-ur8ys8ccq7zb But, yes, those functions look right to me
Prisma
How TypeScript 4.9 satisfies Your Prisma Workflows
Learn how TypeScript 4.9''s new satisfies operator can help you write type-safe code with Prisma
Florian
Florian2mo ago
Thank you for the hint
Want results from more Discord servers?
Add your server
More Posts