I have a database of objects like these bellow:

from flask_sqlalchemy import SQLAlchemy
from flask_login import UserMixin

app = Flask(__name__, static_folder="../public", static_url_path="")

db = SQLAlchemy()
db.init_app(app)


class User(UserMixin, db.Model):
    __tablename__ = "users"
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(40), nullable=False, unique=True)
    post_viewed = relationship(
        "PostViewed", back_populates="author", cascade="all, delete-orphan"
    )

class PostViewed(db.Model):
    __tablename__ = "post_viewed"
    id = db.Column(db.Integer, unique=True, primary_key=True)
    author_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True)
    author = relationship("User", back_populates="post_viewed")
    post_id = db.Column(db.String, db.ForeignKey("posts.id"), nullable=False)
    post = relationship("Post", back_populates="post_viewed")


class Post(db.Model):
    __tablename__ = "posts"
    id = db.Column(db.String, unique=True, primary_key=True)
    title = db.Column(db.String(124), nullable=False)
    description = db.Column(db.Text, nullable=False)
    post_viewed = relationship("PostViewed", back_populates="post", cascade="all, delete-orphan")

I need a way to sort posts by post_viewed table, using SQLAlchemy. So I can show the user posts, they haven't viewed.

CodePudding user response：

To show only not viewed posts by User(id=user_id) you can use subquery:

seen_posts = session.query(
    Post.id.label('post_id')
).join(
    PostViewed
).filter(
    PostViewed.author_id = user_id
).subquery()
not_seen_posts = Post.query.filter(~Post.id.in_(seen_posts))