SQLAlchemy only func.rank() rows in table where value = x

user_14543774 0
Viewed 621

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):

class MoviePersonScores(db.Model):
     movie_id = db.Column(db.Integer, db.ForeignKey('movie.id'), primary_key=True)
     person_id = db.Column(db.Integer, db.ForeignKey('person.id'), primary_key=True)
     score = db.Column(db.Integer)
     person = relationship("Person", back_populates="movies")
     movie = relationship("Movie", back_populates="people")

And answer provided by @YaakovBressler:

from sqlalchemy import func

query = session.query(
    MoviePersonScores, 
    func.rank()\
        .over(
            order_by=MoviePersonScores.score.desc()
        )\
        .label('rank')
)
# now filter
query = query.filter_by(movie_id=movie_id)
# now execute:
my_movie = query.all()

This works great for getting the rank of ALL rows against each other. But now I want to rank based on the people in them. So person_id might be a ID of a director, so now if I want the rank of all Quentin Tarantino movies I would first need to filter the database for all of his movies and then apply the ranks, so how would I go about using the above method on a already queried and filtered set of rows?

Thank you in advance!

sqlalchemy python
1 Answers

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()
user_105219590
Related