#!/usr/bin/python

from sqlalchemy import create_engine, MetaData, Column, Integer, String, Boolean, Unicode, Float, ForeignKey, DateTime
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm.query import Query

db = create_engine('sqlite:///:memory:', echo=True)

Base = declarative_base(db)
metadata = Base.metadata

Session = sessionmaker(db)

class Blog(Base):
    __tablename__ = 'blogs'

    id = Column(Integer, primary_key=True)

    def __init__(self):
        pass

class Post(Base):
    __tablename__ = 'posts'

    id = Column(Integer, primary_key=True)
    blog_id = Column(Integer, ForeignKey(Blog.id))

    blog = relationship(Blog, backref='posts')

    def __init__(self, blog):
        self.blog = blog

class Comment(Base):
    __tablename__ = 'comments'

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

    post = relationship(Post, backref='comments')

    def __init__(self, post):
        self.post = post

Blog.comments = relationship(Comment,
                             primaryjoin=Comment.post_id.in_(Query(Post.id).filter(Post.blog_id == Blog.id)),
                             foreign_keys=[Comment.post_id],
                             viewonly=True)

metadata.drop_all()
metadata.create_all()

session = Session()

b1 = Blog()
b2 = Blog()
session.add(b1)
session.add(b2)

p1 = Post(b1)
p2 = Post(b1)
p3 = Post(b2)

c1 = Comment(p1)
c2 = Comment(p1)
c3 = Comment(p2)
c4 = Comment(p3)
c5 = Comment(p3)

session.commit()

print b1.posts

#print session.query(Comment).join(Post).filter(Post.blog == b1).all()
#print session.query(Post.id).filter(Post.blog == b1).all()
print session.query(Comment).filter(Comment.post_id.in_(Query(Post.id).filter(Post.blog == b1))).all()

session.close()