Sqlalchemy "double layer" query

Question

So I didn't really know how to name my problem, so that was my best shot. Right now, my situation is I have a couple tables:

Post
---------
Id
#....

Alert
-------------
Id
like_id
Seen

Like
--------
id
post_id

And right now, I need to setup request handling where I have the id of the post, but what I need to find out is whether another alert corresponding to the same post has already been initiated. So basically, its a "two layer" query, where I have to find alerts that correspond to a like that correspond to the same post. How could I do this with sqlalchemy?

score 3 · Accepted Answer · answered Sep 12 '12 at 03:16

For example:

class Post(Base):
    __tablename__ = 'post'

    id = Column(Integer, primary_key=True)
    text = Column(Unicode)

class Like(Base):
    __tablename__ = 'like'

    id = Column(Integer, primary_key=True)
    post_id = Column(Integer, ForeignKey(Post.id), nullable=False)

class Alert(Base):
    __tablename__ = 'alert'

    id = Column(Integer, primary_key=True)
    like_id = Column(Integer, ForeignKey(Like.id))

Then in SQLAlchemy you can use the following query:

DBSession.query(Alert.id).join(Like).join(Post).filter(Post.id==2).all()

Sqlalchemy "double layer" query

1 Answers1