Here is the code: from sqlalchemy import Column, ForeignKey, Integer, create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker, relationship
engine = create_engine('postgresql://postgres@localhost/test_db') Session = sessionmaker(bind=engine) session = Session() Base = declarative_base() class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key=True) class News(Base): __tablename__ = 'news' id = Column(Integer, primary_key=True) comments = relationship('Comment') class Comment(Base): __tablename__ = 'comment' id = Column(Integer, primary_key=True) news_id = Column(Integer, ForeignKey(News.id)) class CommentRead(Base): __tablename__ = 'comment_read' comment_id = Column(Integer, ForeignKey(Comment.id), primary_key=True) user_id = Column(Integer, ForeignKey(User.id), primary_key=True) Base.metadata.create_all(engine) # Initialize data session.add_all([User(id=1), News(id=2)]) session.commit() session.add(Comment(id=3, news_id=2)) session.commit() session.add(CommentRead(comment_id=3, user_id=1)) session.commit() # Make query comment_read_query = ( session.query(CommentRead).filter( CommentRead.comment_id == Comment.id, CommentRead.user_id == 1, # Interested in reads by specific user ).exists().label('is_read') ) comment_query = session.query(Comment, comment_read_query).subquery() news = session.query(News).outerjoin(comment_query).all() # How can I get `is_read` column value as comment attribute? assert news[0].comments[0].is_read -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.