Using window functions to LIMIT a query with SqlAlchemy on Postgres

Question

I'm trying to write the following sql query with sqlalchemy ORM:

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;

This is what I've done so far:

s = Session()

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

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

contents = (s.query(Content).select_from(windowed_contents)
            .filter(row_number >= 4)) ##  how can I reference the row_number() value?

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

As you can see it's pretty much modeled, but I have no idea how to reference the row_number() result of the subquery from the outer query where. I tried something like windowed_contents.c.row_number and adding a label() call on the window func but it's not working, couldn't find any similar example in the official docs or in stackoverflow.

How can this be accomplished? And also, could you suggest a better way to do this query?

Answer 1

windowed_contents.c.row_number against a label() is how you'd do it, works for me (note the select_entity_from() method is new in SQLA 0.8.2 and will be needed here in 0.9 vs. select_from()):

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Content(Base):
    __tablename__ = 'contents'

    grandma_id = Column(Integer, primary_key=True)
    author_id = Column(Integer, primary_key=True)


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_entity_from(unique_users_contents).subquery()

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

print q