Sqlalchemy Only Func.rank() Rows In Table Where Value = X

In my previous question, I managed to get the rank of a table based on its value in that table. I can now rank movies based on a given score (using the following table and method):

Solution 1:

The solution to this new query will be similar to the previous one, except you'll include a partition_by argument which is similar to a groupby method, and a filter_by to specify your given person_id:

query = session.query(
    MoviePersonScores, 
    func.rank()\
        .over(
            order_by=MoviePersonScores.score.desc(),
            partition_by=MoviePersonScores.person_id,
        )\
        .label('rank')
)

# If you want to filter for a specific person, filter your query
my_person_id = 1 # substitute this for the correct id
query = query.filter(MoviePersonScores.person_id == my_person_id)

# I'd suggest ordering by the person_id, so you get neat results when debugging
query = query.order_by(MoviePersonScores.person_id, 'rank')

# Convert to a subquery so you can apply more filters
all_movies = query.subquery()

# Now filter your subquery based on a specific movie
movie_id = 1 # some movie ID
query = session.query(all_movies).filter(all_movies.c.movie_id == movie_id)

# Get all results
result = query.all()