Home > Enterprise >  How to change order_by clause in Flask-SQLAlchemy based on dropdown box selection?
How to change order_by clause in Flask-SQLAlchemy based on dropdown box selection?

Time:09-24

I would like to give users on my site the ability to change the order of the results obtained from a query in my database (for example alphabetically ascending, alphabetically descending, or by another parameter).

This is the query I would like to perform an order_by on:

def get_curators_and_total_followers():
    total_playlist_followers =  db.session.query(SpotifyPlaylist.curator_id.label('user_id'), db.func.sum(SpotifyPlaylist.playlist_follower_count).label("total_followers"))\
        .outerjoin(User, SpotifyPlaylist.curator_id==User.id)\
        .group_by(User.id)\
        .subquery()
    curators = db.session.query(User, total_playlist_followers.c.total_followers)\
        .outerjoin(total_playlist_followers, User.id==total_playlist_followers.c.user_id)\
        .join(SpotifyPlaylist, SpotifyPlaylist.curator_id==User.id)\
        .order_by("This is where I want to change the fields")   
    return curators

How can I set a default value so that the page loads, then give users the option to display the results in a different way based on a dropdown box with a few different order_by options?

CodePudding user response:

def get_curators_and_total_followers():
    total_playlist_followers =  db.session.query(SpotifyPlaylist.curator_id.label('user_id'), db.func.sum(SpotifyPlaylist.playlist_follower_count).label("total_followers"))\
        .outerjoin(User, SpotifyPlaylist.curator_id==User.id)\
        .group_by(User.id)\
        .subquery()
    curators = db.session.query(User, total_playlist_followers.c.total_followers)\
        .outerjoin(total_playlist_followers, User.id==total_playlist_followers.c.user_id)\
        .join(SpotifyPlaylist, SpotifyPlaylist.curator_id==User.id)\
    
    order_by = form.get('orderBy')
    if order_by is None:
        curators.order_by("your default value")
        
    elif order_by == 'value 1':
        curators.order_by("value1")
        
    elif order_by == 'value 2':
        curators.order_by("value2")
        
    return curators
  • Related