Complex query (subqueries, window functions) with sqlalchemy

Question

I am 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 have 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 is pretty much modeled, but I have no idea how to refer to the row_number() result of a subquery from an external query where. I tried something like windowed_contents.c.row_number and added the label() call to the func function, but it didn’t work, I could not find any similar example in official docs or in stackoverflow.

How can I do that? And also, can you suggest a better way to make this request?

Answer 1

windowed_contents.c.row_number vs. label() , how you do this works for me (note that the select_entity_from() method is new in SQLA 0.8.2 and will be needed here at 0.9 against 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(qcrow_number <= 4) print q