sqlalchemy query

ger.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, ForeignKey, String
from sqlalchemy.orm import relationship, sessionmaker, aliased
from sqlalchemy import create_engine, func


DATABASE_URI = 'postgresql://gonz@localhost:5432/ger'
engine = create_engine(DATABASE_URI, echo=True)

Session = sessionmaker(bind=engine)
Base = declarative_base()


class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(100))

    grandma_contents = relationship('Content', backref='grandma',
                                    primaryjoin="User.id==Content.grandma_id")
    authored_contents = relationship('Content', backref='author',
                                     primaryjoin="User.id==Content.author_id")


class Content(Base):
    __tablename__ = 'contents'

    id = Column(Integer, primary_key=True)
    grandma_id = Column(Integer, ForeignKey('users.id'))
    author_id = Column(Integer, ForeignKey('users.id'))


def create_relations():
    Base.metadata.create_all(engine)


def insert_test_data():
    session = Session()
    users = [User(name=i) for i in xrange(20)]
    session.add_all(users)

    for i in xrange(0, 20, 2):
        for j in xrange(2):
            c = Content()
            c.grandma = users[1] if i < 10 else users[11]
            c.author = users[i + 1]
            session.add(c)
    session.commit()


def query():
    """
    SELECT * FROM
       (SELECT *, row_number() OVER(w)
        FROM (select distinct on (grandma_id, author_id) * from contents) as c
        WINDOW w AS (PARTITION BY grandma_id ORDER BY RANDOM())) AS v1
    WHERE row_number <= 4;
    """
    s = Session()

    unique_users_contents = s.query(Content).distinct(
        Content.grandma_id, Content.author_id).subquery('c')

    q = (s.query(Content,
                 func.row_number().over(
                     partition_by=Content.grandma_id,
                     order_by=func.random()).label("row_number"))
         .select_from(unique_users_contents).subquery())

    q = s.query(Content).select_from(q).filter(q.c.row_number <= 4)

    for content in q:
        print "%s\t%s\t%s" % (content.id, content.grandma_id,
                              content.author_id)