Upvote/Downvote Datamodel


#1

Hey guys,

I have a quick architecture question. I’m not sure how relations are handled on the backend and am wondering if they are creating an intermediate relation table.

I am looking to replicate a similar system to stackoverflow/reddit’s upvote/downvote system. For posts I need to know the upvote/downvote count and if the current user has upvote/downvote to indicate this to the user.

My current schema has types for Post and User and defines relations on post: upvoters and downvoters.

type Post { 
  id: ID!
  …
  upvoters: [User]!
  downvoters: [User]!
}

type User { 
  id: ID!
  …
  upvoted: [Post]!
  downvotes: [Post]!
}

I can’t decide if this is best practice or to create separate types for upvote/downvote relating the users…

type Upvote {
  id: ID!
  user: User!
  post: Post!
}

type Downvote {
  id: ID!
  user: User!
  post: Post!
}

Perhaps this would be faster at scale?


#2

Hi,

I will use this schema for your case

type Post {
id: ID!
…
upvotes: [Vote]! @relation(name: "PosttoUpvotes")
downvotes: [User]! @relation(name: "PosttoDownvotes")
}

type User {
id: ID!
…
}

type Vote {
id: ID!
user: User!
post: Post!
}

This way you can filer data. I will not necessarily store this data in the user type as it is related to post and vote only.

Now you can easily filter votes like so:

{
posts{
upvotes(where:{ user: { id: "....." } } ){
id
}
}

.

Thanks,
Harshit


#3

How would you count or orderby the number of upvotes/downvotes for a post here?


#4

Especially when you then want to include upvotes on comments as well?

type Comment {
  id: ID! @unique
  subcomments: [Comment]!
  content: String!
  author: User!
  post: Post
  upvotes: [Upvote]!
  downvotes: [Downvote]!
}

not sure if this is best way to handle subcomments either? Anyone have thoughts?


#5

This then brings in the question of which situation is faster?

1. Having multiple queries on the same page

… then use apollo on the frontend to query and feed the data to the different components

// to determine if user upvoted on this post so I can set background color on upvote button.
{
  upvotesConnection(where: { AND: [{ post: { id: "cjrvbxnuf01bw08174ula0xqq" }, user: { id: "cjrv2xg4y00ap0817a8creh61" } }] }) {
    aggregate {
      count
    }
  }
}

// to determine if user downvoted on this post so I can set background color on upvote button.
{
  downvotesConnection(where: { AND: [{ post: { id: "cjrvbxnuf01bw08174ula0xqq" }, user: { id: "cjrv2xg4y00ap0817a8creh61" } }] }) {
    aggregate {
      count
    }
  }
}

// notice no user: { id: --- } in where statement. Used to get a count to show on frontend
{
  upvotesConnection(where: { AND: [{ post: { id: "cjrvbxnuf01bw08174ula0xqq" }}] }) {
    aggregate {
      count
    }
  }
}

// SAME: notice no user: { id: --- } in where statement. Used to get a count to show on frontend
{
  downvotesConnection(where: { AND: [{ post: { id: "cjrvbxnuf01bw08174ula0xqq" }}] }) {
    aggregate {
      count
    }
  }
}

{
  posts {
    id
    title
    content
    description
    comments {
       id
       title
       user {
         id
         name
       }
    ...
  }
}

2. Having one large query with all the information and do the calculations on the frontend

… and then doing all the calculations on the frontend (seems bad to me, maybe I’m wrong?)
e.g.
<Button color={upvotes.some(upvote => upvote.user.id === #currentuserid) ? 'green' : 'white'} >Upvote</Button>

{
  posts {
    id
    title
    content
    author {
      id
      name
    }
    description
    upvotes {
      id
      user {
        id
      }
    }
    downvotes {
      id
      user {
        id
      }
    }
    comments {
      id
      subcomments {
        id
        content
        author {
          id
          name
        }
      }
      author {
        id
        name
      }
    }
  }
}

#3. Possibly? -> Making one vote type with a int indicator of up/downvote

{
  posts {
    id
    title
    content
    author {
      id
      name
    }
    description
    votes {
      id
      user {
        id
      }
      voteBinary # -1,1 or 0,1 for indicating if up/downvote
    }
    comments {
      id
      subcomments {
        id
        content
        author {
          id
          name
        }
      }
      author {
        id
        name
      }
    }
  }
}