#Tips on designing this recursive database model

17 messages · Page 1 of 1 (latest)

pure whale
#

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 ´replyToPostneed to include a potentialrepostOfPost`.

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?

image.png
plush blazeBOT
#

To help others find answers, you can mark your question as solved via Right click solution message -> Apps -> ✅ Mark Solution

#

To help others find answers, you can mark your question as solved via Right click solution message -> Apps -> ✅ Mark Solution

pure whale
# 
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>
))}
#

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;
#

This all works but the types are really messy

grand tapir
#

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?

pure whale
#

In my JS code, the additional distinction helps

grand tapir
#

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

pure whale
#

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.

grand tapir