Problem

I am trying to make a reddit style voting application that allows users to vote on various posts in a database. I don't want a user to be able to vote on a post more than once. As such, I want to return a query with the post data that also displays a boolean value to indicate whether or not the currently logged in user has voted on each post.

What I'm looking for is the ability to query the database and if the currently logged in user has already voted on post (1, 2, and 4 out of 5 total posts) then the query should return this

{post: {...data...}, voted: true}
{post: {...data...}, voted: true}
{post: {...data...}, voted: false}
{post: {...data...}, voted: true}
{post: {...data...}, voted: false}

What I've Tried

use exists().where() to return a boolean if the user_id in the votes table is equal to the current users id.

def get_posts(db: Session = Depends(get_db), current_user: int = Depends(oauth2.get_current_user)
posts = db.query(
    models.Post,
    exists().where(models.Vote.user_id == current_user.id).label("voted")
  ).join(
    models.Vote, models.Vote.post_id == models.Post.id, isouter=True
  ).group_by(
    models.Post.id
  ).all()

This one seems to be no good because if a user has voted on any single post in the database then the query will return True for every post when that user is logged in. It returns this.

{post: {...data...}, voted: true}
{post: {...data...}, voted: true}
{post: {...data...}, voted: true}
{post: {...data...}, voted: true}
{post: {...data...}, voted: true}

I have also tried implementing a count to determine the number of times that the current user has voted on a post. My thinking was that if a 1 is returned that can be treated as representing True whereas if a 0 is returned that can be treated as representing False. This however seems to be no good as it keeps a count of all the votes from all users in the database. As such if more than one user votes on a post it will have a value of 2, 3, 4 etc... which does not help me determine whether or not the current user has voted on the post or not.

Here is the code for this query

def get_posts(db: Session = Depends(get_db), current_user: int = Depends(oauth2.get_current_user)
posts = db.query(
    models.Post,
    func.count(models.Vote.user_id == current_user.id).label("voted")
  ).join(
    models.Vote, models.Vote.post_id == models.Post.id, isouter=True
  ).group_by(
    models.Post.id
  ).all()

For reference these are my models

from sqlalchemy import Column, Integer, String, Boolean, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.sql.expression import text
from sqlalchemy.sql.sqltypes import TIMESTAMP
from .database import Base

class Post(Base):
  __tablename__ = "posts"

  id = Column(Integer, primary_key=True, nullable=False)
  title = Column(String, nullable=False)
  content = Column(String, nullable=False)
  published = Column(Boolean, server_default="True", nullable=False)
  created_at = Column(TIMESTAMP(timezone=True), server_default=text('now()'), nullable=False)
  owner_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
  owner = relationship("User", backref="posts")


class User(Base): 
  __tablename__ = "users"

  id = Column(Integer, primary_key=True, nullable=False)
  username = Column(String, nullable=False, unique=True)
  email = Column(String, nullable=False, unique=True)
  password = Column(String, nullable=False)
  created_at = Column(TIMESTAMP(timezone=True), server_default=text('now()'), nullable=False)


class Vote(Base):
  __tablename__ = "votes"

  user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), primary_key=True)
  user = relationship("User", backref="votes")
  post_id = Column(Integer, ForeignKey("posts.id", ondelete="CASCADE"), primary_key=True)
  post = relationship("Post", backref="votes")
  upvote = Column(Boolean, nullable=False)

Thank you for taking the time to read this! Any help or documentation of specific methods/solutions you can point me towards would be greatly appreciated!

CodePudding user response：

Using LEFT OUTER JOIN conditioned on the current user id. This works I think but usually breaks down quickly if you need to start doing other joins and is error prone. The subquery solution is better.

    q = session.query(
        Post,
        (Vote.post_id != None).label('user_voted'),
    ).join(
        Vote, and_(Post.id == Vote.post_id, Vote.user_id == current_user_id), isouter=True
    ).group_by(
        Post.id, Vote.post_id, Vote.user_id
    )

SELECT posts.id AS posts_id, posts.title AS posts_title, votes.post_id IS NOT NULL AS user_voted 
FROM posts LEFT OUTER JOIN votes ON posts.id = votes.post_id AND votes.user_id = %(user_id_1)s GROUP BY posts.id, votes.post_id, votes.user_id

Using sqlalchemy 1.4/2.0 new select() statement to generate subquery:

    subq = select(Vote.post_id).distinct().where(Vote.user_id == current_user_id)
    q = session.query(Post, Post.id.in_(subq).label('user_voted'))

SELECT posts.id AS posts_id, posts.title AS posts_title, posts.id IN (SELECT DISTINCT votes.post_id 
FROM votes 
WHERE votes.user_id = %(user_id_1)s) AS user_voted 
FROM posts