Edit Question - KnowledgeHub

Title Body

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?

Tags (comma-separated) Cancel