def _get_top_users(session, current_user_id, limit, offset): top_users = session.execute(text(sql), {"limit": limit, "offset": offset}) top_users = [dict(row) for row in top_users] user_ids = list(map(lambda user: user["user_id"], top_users)) top_users = populate_user_metadata(session, user_ids, top_users, current_user_id) return top_users
def get_followers_for_user(args): users = [] followee_user_id = args.get("followee_user_id") current_user_id = args.get("current_user_id") limit = args.get("limit") offset = args.get("offset") db = get_db_read_replica() with db.scoped_session() as session: rows = session.execute( sql, { "followee_user_id": followee_user_id, "limit": limit, "offset": offset }, ) user_ids = [r[0] for r in rows] # get all users for above user_ids users = get_unpopulated_users(session, user_ids) # bundle peripheral info into user results users = populate_user_metadata(session, user_ids, users, current_user_id) return users
def get_follow_intersection_users(followee_user_id, follower_user_id): users = [] db = get_db() with db.scoped_session() as session: query = (session.query(User).filter( User.is_current == True, User.is_ready == True, User.user_id.in_( session.query(Follow.follower_user_id).filter( Follow.followee_user_id == followee_user_id, Follow.is_current == True, Follow.is_delete == False).intersect( session.query(Follow.followee_user_id).filter( Follow.follower_user_id == follower_user_id, Follow.is_current == True, Follow.is_delete == False))))) users = paginate_query(query).all() users = helpers.query_result_to_list(users) user_ids = [user[response_name_constants.user_id] for user in users] current_user_id = get_current_user_id(required=False) # bundle peripheral info into user results users = populate_user_metadata(session, user_ids, users, current_user_id) # order by follower_count desc users.sort( key=lambda user: user[response_name_constants.follower_count], reverse=True) return api_helpers.success_response(users)
def user_search_query(session, searchStr, limit, offset, personalized, is_auto_complete, current_user_id): if personalized and not current_user_id: return [] res = sqlalchemy.text(f""" select user_id from ( select user_id, (sum(score) + (:name_weight * similarity(coalesce(name, ''), query))) as total_score from ( select d."user_id" as user_id, d."word" as word, similarity(d."word", :query) as score, d."user_name" as name, :query as query from "user_lexeme_dict" d { 'inner join "follows" f on f.followee_user_id=d.user_id' if personalized and current_user_id else "" } where d."word" % :query { "and f.is_current=true and f.is_delete=false and f.follower_user_id=:current_user_id" if personalized and current_user_id else "" } ) as results group by user_id, name, query ) as results2 order by total_score desc, user_id asc limit :limit offset :offset; """) user_ids = session.execute( res, { "query": searchStr, "limit": limit, "offset": offset, "name_weight": userNameWeight, "current_user_id": current_user_id }, ).fetchall() # user_ids is list of tuples - simplify to 1-D list user_ids = [i[0] for i in user_ids] users = (session.query(User).filter(User.is_current == True, User.user_id.in_(user_ids)).all()) users = helpers.query_result_to_list(users) if not is_auto_complete: # bundle peripheral info into user results users = populate_user_metadata(session, user_ids, users, current_user_id) # preserve order from user_ids above users = [ next(u for u in users if u["user_id"] == user_id) for user_id in user_ids ] return users
def get_reposters_for_track(args): user_results = [] current_user_id = args.get('current_user_id') repost_track_id = args.get('repost_track_id') limit = args.get('limit') offset = args.get('offset') db = get_db_read_replica() with db.scoped_session() as session: # Ensure Track exists for provided repost_track_id. track_entry = session.query(Track).filter( Track.track_id == repost_track_id, Track.is_current == True).first() if track_entry is None: raise exceptions.NotFoundError( 'Resource not found for provided track id') # Subquery to get all (user_id, follower_count) entries from Follows table. follower_count_subquery = (session.query( Follow.followee_user_id, func.count(Follow.followee_user_id).label( response_name_constants.follower_count)).filter( Follow.is_current == True, Follow.is_delete == False).group_by( Follow.followee_user_id).subquery()) # Get all Users that reposted track, ordered by follower_count desc & paginated. query = ( session.query( User, # Replace null values from left outer join with 0 to ensure sort works correctly. (func.coalesce(follower_count_subquery.c.follower_count, 0) ).label(response_name_constants.follower_count)) # Left outer join to associate users with their follower count. .outerjoin( follower_count_subquery, follower_count_subquery.c.followee_user_id == User.user_id).filter( User.is_current == True, # Only select users that reposted given track. User.user_id.in_( session.query(Repost.user_id).filter( Repost.repost_item_id == repost_track_id, Repost.repost_type == RepostType.track, Repost.is_current == True, Repost.is_delete == False) )).order_by(desc(response_name_constants.follower_count))) user_results = add_query_pagination(query, limit, offset).all() # Fix format to return only Users objects with follower_count field. if user_results: users, _ = zip(*user_results) user_results = helpers.query_result_to_list(users) # bundle peripheral info into user results user_ids = [user['user_id'] for user in user_results] user_results = populate_user_metadata(session, user_ids, user_results, current_user_id) return user_results
def get_followers_for_user(followee_user_id): users = [] db = get_db() with db.scoped_session() as session: # correlated subquery sqlalchemy code: # https://groups.google.com/forum/#!topic/sqlalchemy/WLIy8jxD7qg inner_follow = aliased(Follow) outer_follow = aliased(Follow) # subquery to get a user's follower count inner_select = (session.query(func.count( inner_follow.followee_user_id)).filter( inner_follow.is_current == True, inner_follow.is_delete == False, inner_follow.followee_user_id == outer_follow.follower_user_id).correlate(outer_follow)) # get all users that follow input user, sorted by their follower count desc outer_select = ( session.query( outer_follow.follower_user_id, inner_select.as_scalar().label( response_name_constants.follower_count)).filter( outer_follow.followee_user_id == followee_user_id, outer_follow.is_current == True, outer_follow.is_delete == False). group_by(outer_follow.follower_user_id).order_by( response_name_constants.follower_count + " desc", # secondary sort to guarantee determinism as explained here: # https://stackoverflow.com/questions/13580826/postgresql-repeating-rows-from-limit-offset asc(outer_follow.follower_user_id))) follower_user_ids_by_follower_count = paginate_query( outer_select).all() user_ids = [ user_id for (user_id, follower_count) in follower_user_ids_by_follower_count ] # get all users for above user_ids users = (session.query(User).filter(User.is_current == True, User.is_ready == True, User.user_id.in_(user_ids)).all()) users = helpers.query_result_to_list(users) current_user_id = get_current_user_id(required=False) # bundle peripheral info into user results users = populate_user_metadata(session, user_ids, users, current_user_id) # order by (follower_count desc, user_id asc) to match query sorting # tuple key syntax from: https://stackoverflow.com/a/4233482/8414360 users.sort(key=lambda user: (user[response_name_constants.follower_count], (user['user_id']) * (-1)), reverse=True) return api_helpers.success_response(users)
def get_users(): users = [] db = get_db() with db.scoped_session() as session: # Create initial query base_query = session.query(User) # Don't return the user if they have no wallet or handle (user creation did not finish properly on chain) base_query = base_query.filter(User.is_current == True, User.wallet != None, User.handle != None) # Process filters if "is_creator" in request.args: is_creator_flag = request.args.get("is_creator") == "true" base_query = base_query.filter(User.is_creator == is_creator_flag) if "wallet" in request.args: wallet = request.args.get("wallet") wallet = wallet.lower() if len(wallet) == 42: base_query = base_query.filter_by(wallet=wallet) else: logger.warning("Invalid wallet length") if "handle" in request.args: handle = request.args.get("handle").lower() base_query = base_query.filter_by(handle_lc=handle) # Conditionally process an array of users if "id" in request.args: user_id_str_list = request.args.getlist("id") user_id_list = [] try: user_id_list = [int(y) for y in user_id_str_list] base_query = base_query.filter(User.user_id.in_(user_id_list)) except ValueError as e: raise exceptions.ArgumentError( "Invalid value found in user id list", e) if "min_block_number" in request.args: min_block_number = request.args.get("min_block_number", type=int) base_query = base_query.filter( User.blocknumber >= min_block_number) users = paginate_query(base_query).all() users = helpers.query_result_to_list(users) user_ids = list(map(lambda user: user["user_id"], users)) current_user_id = get_current_user_id(required=False) # bundle peripheral info into user results users = populate_user_metadata(session, user_ids, users, current_user_id) return api_helpers.success_response(users)
def get_reposters_for_playlist(args): user_results = [] current_user_id = args.get("current_user_id") repost_playlist_id = args.get("repost_playlist_id") limit = args.get("limit") offset = args.get("offset") db = get_db_read_replica() with db.scoped_session() as session: # Ensure Playlist exists for provided repost_playlist_id. playlist_entry = (session.query(Playlist).filter( Playlist.playlist_id == repost_playlist_id, Playlist.is_current == True).first()) if playlist_entry is None: raise exceptions.NotFoundError( "Resource not found for provided playlist id") # Get all Users that reposted Playlist, ordered by follower_count desc & paginated. query = ( session.query( User, # Replace null values from left outer join with 0 to ensure sort works correctly. (func.coalesce(AggregateUser.follower_count, 0) ).label(response_name_constants.follower_count), ) # Left outer join to associate users with their follower count. .outerjoin(AggregateUser, AggregateUser.user_id == User.user_id). filter( User.is_current == True, # Only select users that reposted given playlist. User.user_id.in_( session.query(Repost.user_id).filter( Repost.repost_item_id == repost_playlist_id, # Select Reposts for Playlists and Albums (i.e. not Tracks). Repost.repost_type != RepostType.track, Repost.is_current == True, Repost.is_delete == False, )), ).order_by(desc(response_name_constants.follower_count))) user_results = add_query_pagination(query, limit, offset).all() # Fix format to return only Users objects with follower_count field. if user_results: users, _ = zip(*user_results) user_results = helpers.query_result_to_list(users) # bundle peripheral info into user results user_ids = [user["user_id"] for user in user_results] user_results = populate_user_metadata(session, user_ids, user_results, current_user_id) return user_results
def get_followees_for_user(follower_user_id): users = [] db = get_db() with db.scoped_session() as session: # correlated subquery sqlalchemy code: # https://groups.google.com/forum/#!topic/sqlalchemy/WLIy8jxD7qg inner_follow = aliased(Follow) outer_follow = aliased(Follow) # subquery to get a user's follower count inner_select = (session.query(func.count( inner_follow.followee_user_id)).filter( inner_follow.followee_user_id == outer_follow.followee_user_id, inner_follow.is_current == True, inner_follow.is_delete == False).correlate(outer_follow)) # get all users followed by input user, sorted by their follower count desc outer_select = (session.query( outer_follow.followee_user_id, inner_select.as_scalar().label( response_name_constants.follower_count)).filter( outer_follow.follower_user_id == follower_user_id, outer_follow.is_current == True, outer_follow.is_delete == False).group_by( outer_follow.followee_user_id).order_by( response_name_constants.follower_count + " desc")) followee_user_ids_by_follower_count = paginate_query( outer_select).all() user_ids = [ user_id for (user_id, follower_count) in followee_user_ids_by_follower_count ] # get all users for above user_ids users = (session.query(User).filter(User.is_current == True, User.is_ready == True, User.user_id.in_(user_ids)).all()) users = helpers.query_result_to_list(users) current_user_id = get_current_user_id(required=False) # bundle peripheral info into user results users = populate_user_metadata(session, user_ids, users, current_user_id) # order by follower_count desc users.sort( key=lambda user: user[response_name_constants.follower_count], reverse=True) return api_helpers.success_response(users)
def get_related_artists(user_id: int, current_user_id: int, limit: int = 100): db = get_db_read_replica() users = [] with db.scoped_session() as session: aggregate_user = (session.query(AggregateUser).filter( AggregateUser.user_id == user_id).one_or_none()) if (aggregate_user and aggregate_user.track_count > 0 and aggregate_user.follower_count >= MIN_FOLLOWER_REQUIREMENT): users = _get_related_artists(session, user_id, limit) user_ids = list(map(lambda user: user["user_id"], users)) users = populate_user_metadata(session, user_ids, users, current_user_id) return users
def get_tips(args: GetTipsArgs) -> List[TipResult]: db = get_db_read_replica() with db.scoped_session() as session: results: Union[List[Tuple[UserTip, List[str]]], List[UserTip]] = _get_tips(session, args) tips_results: List[Tuple[UserTip, List[str]]] = [] # Wrap in tuple for consistency if results and isinstance(results[0], UserTip): tips_results = [(cast(UserTip, tip), []) for tip in results] else: # MyPy doesn't seem smart enough to figure this out, help it with a cast tips_results = cast(List[Tuple[UserTip, List[str]]], results) # Collect user IDs and fetch users user_ids = set() for result in tips_results: user_ids.add(result[0].sender_user_id) user_ids.add(result[0].receiver_user_id) users = get_unpopulated_users(session, user_ids) users = populate_user_metadata( session, user_ids, users, args["user_id"] if "user_id" in args else None) users_map = {} for user in users: users_map[user["user_id"]] = user # Not using model_to_dictionary() here because TypedDict complains about dynamic keys tips: List[TipResult] = [{ "amount": result[0].amount, "sender": users_map[result[0].sender_user_id], "receiver": users_map[result[0].receiver_user_id], "followee_supporters": list(filter( lambda id: id is not None, result[1], )), "slot": result[0].slot, "created_at": result[0].created_at, "tx_signature": result[0].signature, } for result in tips_results] return tips
def search_user_tags(session, args): """ Gets the users with tracks with a given tag Args: session: sqlalchemy db session instance args: dict of arguments args.search_str: string the tag search string args.current_user_id: id | null The user id making the query args.limit: number the query limit of number of returns tracks args.offset: number the query offset for results args.user_tag_count: number The number of tracks with the query tag Returns: list of users sorted by followee count """ user_ids = ( session.query(TagTrackUserMatview.owner_id) .filter(TagTrackUserMatview.tag == args["search_str"].lower()) .group_by(TagTrackUserMatview.owner_id) .having(func.count(TagTrackUserMatview.owner_id) >= args["user_tag_count"]) .all() ) # user_ids is list of tuples - simplify to 1-D list user_ids = [i[0] for i in user_ids] users = ( session.query(User) .filter(User.is_current == True, User.user_id.in_(user_ids)) .all() ) users = helpers.query_result_to_list(users) users = populate_user_metadata(session, user_ids, users, args["current_user_id"]) followee_sorted_users = sorted( users, key=lambda i: i[response_name_constants.follower_count], reverse=True ) followee_sorted_users = followee_sorted_users[ slice(args["offset"], args["offset"] + args["limit"], 1) ] return followee_sorted_users
def search_tags(): search_str = request.args.get("query", type=str) current_user_id = get_current_user_id(required=False) if not search_str: raise exceptions.ArgumentError("Invalid value for parameter 'query'") user_tag_count = request.args.get("user_tag_count", type=str) if not user_tag_count: user_tag_count = "2" kind = request.args.get("kind", type=str, default="all") validSearchKinds = [SearchKind.all, SearchKind.tracks, SearchKind.users] try: searchKind = SearchKind[kind] if searchKind not in validSearchKinds: raise Exception except Exception: return api_helpers.error_response( "Invalid value for parameter 'kind' must be in %s" % [k.name for k in validSearchKinds], 400) results = {} (limit, offset) = get_pagination_vars() like_tags_str = str.format('%{}%', search_str) db = get_db_read_replica() with db.scoped_session() as session: if (searchKind in [SearchKind.all, SearchKind.tracks]): track_res = sqlalchemy.text(f""" select distinct(track_id) from ( select strip(to_tsvector(tracks.tags)) as tagstrip, track_id from tracks where (tags like :like_tags_query) and (is_current is true) and (is_delete is false) and (is_unlisted is false) and (stem_of is NULL) order by updated_at desc ) as t where tagstrip @@ to_tsquery(:query); """) track_ids = session.execute(track_res, { "query": search_str, "like_tags_query": like_tags_str }).fetchall() # track_ids is list of tuples - simplify to 1-D list track_ids = [i[0] for i in track_ids] tracks = (session.query(Track).filter( Track.is_current == True, Track.is_delete == False, Track.is_unlisted == False, Track.stem_of == None, Track.track_id.in_(track_ids), ).all()) tracks = helpers.query_result_to_list(tracks) track_play_counts = get_track_play_counts(track_ids) tracks = populate_track_metadata(session, track_ids, tracks, current_user_id) for track in tracks: track_id = track["track_id"] track[response_name_constants. play_count] = track_play_counts.get(track_id, 0) play_count_sorted_tracks = \ sorted( tracks, key=lambda i: i[response_name_constants.play_count], reverse=True) # Add pagination parameters to track and user results play_count_sorted_tracks = \ play_count_sorted_tracks[slice(offset, offset + limit, 1)] results['tracks'] = play_count_sorted_tracks if (searchKind in [SearchKind.all, SearchKind.users]): user_res = sqlalchemy.text(f""" select * from ( select count(track_id), owner_id from ( select strip(to_tsvector(tracks.tags)) as tagstrip, track_id, owner_id from tracks where (tags like :like_tags_query) and (is_current is true) and (is_unlisted is false) and (stem_of is NULL) order by updated_at desc ) as t where tagstrip @@ to_tsquery(:query) group by owner_id order by count desc ) as usr where usr.count >= :user_tag_count; """) user_ids = session.execute( user_res, { "query": search_str, "like_tags_query": like_tags_str, "user_tag_count": user_tag_count }).fetchall() # user_ids is list of tuples - simplify to 1-D list user_ids = [i[1] for i in user_ids] users = (session.query(User).filter( User.is_current == True, User.user_id.in_(user_ids)).all()) users = helpers.query_result_to_list(users) users = populate_user_metadata(session, user_ids, users, current_user_id) followee_sorted_users = \ sorted( users, key=lambda i: i[response_name_constants.follower_count], reverse=True) followee_sorted_users = \ followee_sorted_users[slice(offset, offset + limit, 1)] results['users'] = followee_sorted_users # Add personalized results for a given user if current_user_id: if (searchKind in [SearchKind.all, SearchKind.tracks]): # Query saved tracks for the current user that contain this tag saves_query = (session.query(Save.save_item_id).filter( Save.is_current == True, Save.is_delete == False, Save.save_type == SaveType.track, Save.user_id == current_user_id, Save.save_item_id.in_(track_ids)).all()) saved_track_ids = [i[0] for i in saves_query] saved_tracks = (session.query(Track).filter( Track.is_current == True, Track.is_delete == False, Track.is_unlisted == False, Track.stem_of == None, Track.track_id.in_(saved_track_ids), ).all()) saved_tracks = helpers.query_result_to_list(saved_tracks) for saved_track in saved_tracks: saved_track_id = saved_track["track_id"] saved_track[response_name_constants.play_count] = \ track_play_counts.get(saved_track_id, 0) saved_tracks = \ populate_track_metadata( session, saved_track_ids, saved_tracks, current_user_id) # Sort and paginate play_count_sorted_saved_tracks = \ sorted( saved_tracks, key=lambda i: i[response_name_constants.play_count], reverse=True) play_count_sorted_saved_tracks = \ play_count_sorted_saved_tracks[slice( offset, offset + limit, 1)] results['saved_tracks'] = play_count_sorted_saved_tracks if (searchKind in [SearchKind.all, SearchKind.users]): # Query followed users that have referenced this tag followed_user_query = (session.query( Follow.followee_user_id).filter( Follow.is_current == True, Follow.is_delete == False, Follow.follower_user_id == current_user_id, Follow.followee_user_id.in_(user_ids)).all()) followed_user_ids = [i[0] for i in followed_user_query] followed_users = (session.query(User).filter( User.is_current == True, User.user_id.in_(followed_user_ids)).all()) followed_users = helpers.query_result_to_list(followed_users) followed_users = \ populate_user_metadata( session, followed_user_ids, followed_users, current_user_id ) followed_users_followee_sorted = \ sorted( followed_users, key=lambda i: i[response_name_constants.follower_count], reverse=True) followed_users_followee_sorted = \ followed_users_followee_sorted[slice( offset, offset + limit, 1)] results['followed_users'] = followed_users_followee_sorted return api_helpers.success_response(results)
def get_users(args): users = [] current_user_id = args.get("current_user_id") db = get_db_read_replica() with db.scoped_session() as session: def get_users_and_ids(): can_use_shared_cache = ( "id" in args and "is_creator" not in args and "wallet" not in args and "min_block_number" not in args and "handle" not in args ) if can_use_shared_cache: users = get_unpopulated_users(session, args.get("id")) ids = list(map(lambda user: user["user_id"], users)) return (users, ids) # Create initial query base_query = session.query(User) # Don't return the user if they have no wallet or handle (user creation did not finish properly on chain) base_query = base_query.filter( User.is_current == True, User.wallet != None, User.handle != None) # Process filters if "is_creator" in args: base_query = base_query.filter(User.is_creator == args.get("is_creator")) if "wallet" in args: wallet = args.get("wallet") wallet = wallet.lower() if len(wallet) == 42: base_query = base_query.filter_by(wallet=wallet) base_query = base_query.order_by(asc(User.created_at)) else: logger.warning("Invalid wallet length") if "handle" in args: handle = args.get("handle").lower() base_query = base_query.filter_by(handle_lc=handle) # Conditionally process an array of users if "id" in args: user_id_list = args.get("id") try: base_query = base_query.filter(User.user_id.in_(user_id_list)) except ValueError as e: raise exceptions.ArgumentError( "Invalid value found in user id list", e) if "min_block_number" in args: base_query = base_query.filter( User.blocknumber >= args.get("min_block_number") ) users = paginate_query(base_query).all() users = helpers.query_result_to_list(users) user_ids = list(map(lambda user: user["user_id"], users)) return (users, user_ids) (users, user_ids) = get_users_and_ids() # bundle peripheral info into user results users = populate_user_metadata( session, user_ids, users, current_user_id) return users
def get_users(args): users = [] current_user_id = args.get("current_user_id") db = get_db_read_replica() with db.scoped_session() as session: def get_users_and_ids(): can_use_shared_cache = ("id" in args and "is_creator" not in args and "wallet" not in args and "min_block_number" not in args and "handle" not in args) if can_use_shared_cache: users = get_unpopulated_users(session, args.get("id")) ids = list(map(lambda user: user["user_id"], users)) return (users, ids) # Create initial query base_query = session.query(User) # Don't return the user if they have no wallet or handle (user creation did not finish properly on chain) base_query = base_query.filter(User.is_current == True, User.wallet != None, User.handle != None) # Process filters if "is_creator" in args: base_query = base_query.filter( User.is_creator == args.get("is_creator")) if "wallet" in args: wallet = args.get("wallet") wallet = wallet.lower() if len(wallet) == 42: base_query = base_query.filter_by(wallet=wallet) base_query = base_query.order_by(asc(User.created_at)) else: logger.warning("Invalid wallet length") if "handle" in args: handle = args.get("handle").lower() base_query = base_query.filter_by(handle_lc=handle) # Conditionally process an array of users if "id" in args: user_id_list = args.get("id") try: base_query = base_query.filter( User.user_id.in_(user_id_list)) except ValueError as e: raise exceptions.ArgumentError( "Invalid value found in user id list", e) if "min_block_number" in args: base_query = base_query.filter( User.blocknumber >= args.get("min_block_number")) users = paginate_query(base_query).all() users = helpers.query_result_to_list(users) user_ids = list(map(lambda user: user["user_id"], users)) return (users, user_ids) (users, user_ids) = get_users_and_ids() # bundle peripheral info into user results users = populate_user_metadata(session, user_ids, users, current_user_id) # Debugging flag for checking user solana bank existence # used by createSolanaUserBank script to confirm banks are created successfully if "with_banks" in args: secondary_query = session.query(UserBankAccount).filter( UserBankAccount.ethereum_address.in_(user["wallet"] for user in users)) banks: List[UserBankAccount] = secondary_query.all() logger.info(banks) users_by_wallet = {user["wallet"]: user for user in users} for bank in banks: users_by_wallet[ bank.ethereum_address]["has_solana_bank"] = True return users
def test_populate_user_metadata(app): """Tests that populate_user_metadata works after aggregate_user update""" with app.app_context(): db = get_db() test_entities = { "tracks": [ { "track_id": 1, "owner_id": 1 }, { "track_id": 2, "owner_id": 1 }, { "track_id": 3, "owner_id": 2 }, { "track_id": 4, "owner_id": 2 }, { "track_id": 5, "owner_id": 2 }, { "track_id": 6, "owner_id": 2 }, { "track_id": 7, "owner_id": 3 }, { "track_id": 8, "owner_id": 3 }, { "track_id": 9, "is_unlisted": True, "owner_id": 3 }, ], "playlists": [ { "playlist_id": 1, "playlist_owner_id": 1 }, { "playlist_id": 2, "playlist_owner_id": 1 }, { "playlist_id": 3, "is_album": True, "playlist_owner_id": 1 }, { "playlist_id": 4, "playlist_owner_id": 2 }, { "playlist_id": 5, "is_delete": True, "playlist_owner_id": 2 }, { "playlist_id": 6, "is_album": True, "playlist_owner_id": 3 }, { "playlist_id": 6, "is_private": True, "playlist_owner_id": 3 }, ], "users": [ { "user_id": 1, "handle": "user1", "wallet": "0x111" }, { "user_id": 2, "handle": "user2", "wallet": "0x222" }, { "user_id": 3, "handle": "user3", "wallet": "0x333" }, { "user_id": 4, "handle": "user4", "wallet": "0x444" }, ], "follows": [ { "follower_user_id": 1, "followee_user_id": 2 }, { "follower_user_id": 1, "followee_user_id": 3 }, { "follower_user_id": 2, "followee_user_id": 3 }, ], "reposts": [ { "repost_item_id": 1, "repost_type": "track", "user_id": 2 }, { "repost_item_id": 1, "repost_type": "playlist", "user_id": 2 }, { "repost_item_id": 1, "repost_type": "track", "user_id": 3 }, { "repost_item_id": 1, "repost_type": "playlist", "user_id": 3 }, { "repost_item_id": 4, "repost_type": "track", "user_id": 1 }, { "repost_item_id": 5, "repost_type": "track", "user_id": 1 }, { "repost_item_id": 6, "repost_type": "track", "user_id": 1 }, ], "saves": [ { "save_item_id": 1, "save_type": "track", "user_id": 2 }, { "save_item_id": 1, "save_type": "playlist", "user_id": 2 }, { "save_item_id": 1, "save_type": "track", "user_id": 3 }, { "save_item_id": 1, "save_type": "playlist", "user_id": 3 }, { "save_item_id": 4, "save_type": "track", "user_id": 1 }, { "save_item_id": 5, "save_type": "track", "user_id": 1 }, { "save_item_id": 6, "save_type": "track", "user_id": 1 }, ], } populate_mock_db(db, test_entities) with db.scoped_session() as session: _update_aggregate_user(session) user_ids = [1, 2, 3, 4, 5] users = [ { "user_id": 1, "wallet": "0x111", "is_verified": False }, { "user_id": 2, "wallet": "0x222", "is_verified": False }, { "user_id": 3, "wallet": "0x333", "is_verified": False }, { "user_id": 4, "wallet": "0x444", "is_verified": False }, { "user_id": 5, "wallet": "0x555", "is_verified": False }, ] users = populate_user_metadata(session, user_ids, users, 3) assert len(users) == 5 assert users[0]["does_follow_current_user"] == True assert users[1]["does_follow_current_user"] == True assert users[2]["does_follow_current_user"] == False assert users[3]["does_follow_current_user"] == False assert users[4]["does_follow_current_user"] == False assert users[0]["user_id"] == 1 assert users[0][response_name_constants.track_count] == 2 assert users[0][response_name_constants.playlist_count] == 2 assert users[0][response_name_constants.album_count] == 1 assert users[0][response_name_constants.follower_count] == 0 assert users[0][response_name_constants.followee_count] == 2 assert users[0][response_name_constants.repost_count] == 3 assert users[1]["user_id"] == 2 assert users[1][response_name_constants.track_count] == 4 assert users[1][response_name_constants.playlist_count] == 1 assert users[1][response_name_constants.album_count] == 0 assert users[1][response_name_constants.follower_count] == 1 assert users[1][response_name_constants.followee_count] == 1 assert users[1][response_name_constants.repost_count] == 2 assert users[2]["user_id"] == 3 assert users[2][response_name_constants.track_count] == 2 assert users[2][response_name_constants.playlist_count] == 0 assert users[2][response_name_constants.album_count] == 1 assert users[2][response_name_constants.follower_count] == 2 assert users[2][response_name_constants.followee_count] == 0 assert users[2][response_name_constants.repost_count] == 2 assert users[3]["user_id"] == 4 assert users[3][response_name_constants.track_count] == 0 assert users[3][response_name_constants.playlist_count] == 0 assert users[3][response_name_constants.album_count] == 0 assert users[3][response_name_constants.follower_count] == 0 assert users[3][response_name_constants.followee_count] == 0 assert users[3][response_name_constants.repost_count] == 0 assert users[4]["user_id"] == 5 assert users[4][response_name_constants.track_count] == 0 assert users[4][response_name_constants.playlist_count] == 0 assert users[4][response_name_constants.album_count] == 0 assert users[4][response_name_constants.follower_count] == 0 assert users[4][response_name_constants.followee_count] == 0 assert users[4][response_name_constants.repost_count] == 0 curr_user_ids = [1, 2, 3] curr_users = [ { "user_id": 1, "wallet": "0x111", "is_verified": False }, { "user_id": 2, "wallet": "0x222", "is_verified": False }, { "user_id": 3, "wallet": "0x333", "is_verified": False }, ] users = populate_user_metadata(session, curr_user_ids, curr_users, 1) assert len(users) == 3 assert users[0]["user_id"] == 1 assert users[0][ response_name_constants.does_current_user_follow] == False assert users[0][ response_name_constants.current_user_followee_follow_count] == 0 assert users[0][response_name_constants.balance] == "0" assert users[0][ response_name_constants.associated_wallets_balance] == "0" assert users[1]["user_id"] == 2 assert users[1][ response_name_constants.does_current_user_follow] == True assert users[1][ response_name_constants.current_user_followee_follow_count] == 0 assert users[1][response_name_constants.balance] == "0" assert users[1][ response_name_constants.associated_wallets_balance] == "0" assert users[2]["user_id"] == 3 assert users[2][ response_name_constants.does_current_user_follow] == True assert users[2][ response_name_constants.current_user_followee_follow_count] == 1 assert users[2][response_name_constants.balance] == "0" assert users[2][ response_name_constants.associated_wallets_balance] == "0" # get_top_users: should return only artists, most followers first top_user_ids = [ u["user_id"] for u in _get_top_users(session, 1, 100, 0) ] assert top_user_ids == [3, 2, 1]
def user_search_query(session, searchStr, limit, offset, personalized, is_auto_complete, current_user_id): if personalized and not current_user_id: return [] res = sqlalchemy.text(f""" select user_id from ( select user_id, (sum(score) + (:name_weight * similarity(coalesce(name, ''), query))) as total_score from ( select d."user_id" as user_id, d."word" as word, similarity(d."word", :query) as score, d."user_name" as name, :query as query from "user_lexeme_dict" d { 'inner join "follows" f on f.followee_user_id=d.user_id' if personalized and current_user_id else "" } where d."word" % :query { "and f.is_current=true and f.is_delete=false and f.follower_user_id=:current_user_id" if personalized and current_user_id else "" } ) as results group by user_id, name, query ) as results2 order by total_score desc, user_id asc limit :limit offset :offset; """) user_ids = session.execute( res, { "query": searchStr, "limit": max(limit, MIN_SEARCH_LEXEME_LIMIT), "offset": offset, "name_weight": userNameWeight, "current_user_id": current_user_id }, ).fetchall() # user_ids is list of tuples - simplify to 1-D list user_ids = [i[0] for i in user_ids] users = get_unpopulated_users(session, user_ids) # TODO: Populate user metadata should be sped up to be able to be # used in search autocomplete as that'll give us better results. if is_auto_complete: # get follower information to improve search ordering users = populate_user_follower_counts(session, user_ids, users) else: # bundle peripheral info into user results users = populate_user_metadata(session, user_ids, users, current_user_id) # Preserve order from user_ids above user_map = {} for u in users: user_map[u["user_id"]] = u users = [user_map[user_id] for user_id in user_ids] # Sort users by extra criteria for "best match" users.sort(key=cmp_to_key(compare_users)) return users[0:limit]
def test_populate_user_metadata(app): """Tests that populate_user_metadata works after aggregate_user refresh""" with app.app_context(): db = get_db() test_entities = { 'tracks': [{ "track_id": 1, "owner_id": 1 }, { "track_id": 2, "owner_id": 1 }, { "track_id": 3, "owner_id": 2 }, { "track_id": 4, "owner_id": 2 }, { "track_id": 5, "owner_id": 2 }, { "track_id": 6, "owner_id": 2 }, { "track_id": 7, "owner_id": 3 }, { "track_id": 8, "owner_id": 3 }, { "track_id": 8, "owner_id": 3 }, { "track_id": 9, "is_unlisted": True, "owner_id": 3 }], 'playlists': [ { "playlist_id": 1, "playlist_owner_id": 1 }, { "playlist_id": 2, "playlist_owner_id": 1 }, { "playlist_id": 3, "is_album": True, "playlist_owner_id": 1 }, { "playlist_id": 4, "playlist_owner_id": 2 }, { "playlist_id": 5, "is_delete": True, "playlist_owner_id": 2 }, { "playlist_id": 6, "is_album": True, "playlist_owner_id": 3 }, { "playlist_id": 6, "is_private": True, "playlist_owner_id": 3 }, ], 'users': [{ 'user_id': 1, 'handle': 'user1' }, { 'user_id': 2, 'handle': 'user2' }, { 'user_id': 3, 'handle': 'user3' }, { 'user_id': 4, 'handle': 'user4' }], 'follows': [{ "follower_user_id": 1, "followee_user_id": 2 }, { "follower_user_id": 1, "followee_user_id": 3 }, { "follower_user_id": 2, "followee_user_id": 3 }], 'reposts': [{ "repost_item_id": 1, "repost_type": 'track', "user_id": 2 }, { "repost_item_id": 1, "repost_type": 'playlist', "user_id": 2 }, { "repost_item_id": 1, "repost_type": 'track', "user_id": 3 }, { "repost_item_id": 1, "repost_type": 'playlist', "user_id": 3 }, { "repost_item_id": 4, "repost_type": 'track', "user_id": 1 }, { "repost_item_id": 5, "repost_type": 'track', "user_id": 1 }, { "repost_item_id": 6, "repost_type": 'track', "user_id": 1 }], 'saves': [{ "save_item_id": 1, "save_type": 'track', "user_id": 2 }, { "save_item_id": 1, "save_type": 'playlist', "user_id": 2 }, { "save_item_id": 1, "save_type": 'track', "user_id": 3 }, { "save_item_id": 1, "save_type": 'playlist', "user_id": 3 }, { "save_item_id": 4, "save_type": 'track', "user_id": 1 }, { "save_item_id": 5, "save_type": 'track', "user_id": 1 }, { "save_item_id": 6, "save_type": 'track', "user_id": 1 }] } populate_mock_db(db, test_entities) with db.scoped_session() as session: session.execute("REFRESH MATERIALIZED VIEW aggregate_user") user_ids = [1, 2, 3, 4, 5] users = [{ "user_id": 1, "is_verified": False }, { "user_id": 2, "is_verified": False }, { "user_id": 3, "is_verified": False }, { "user_id": 4, "is_verified": False }, { "user_id": 5, "is_verified": False }] users = populate_user_metadata(session, user_ids, users, None) assert len(users) == 5 assert users[0]['user_id'] == 1 assert users[0][response_name_constants.track_count] == 2 assert users[0][response_name_constants.playlist_count] == 2 assert users[0][response_name_constants.album_count] == 1 assert users[0][response_name_constants.follower_count] == 0 assert users[0][response_name_constants.followee_count] == 2 assert users[0][response_name_constants.repost_count] == 3 assert users[1]['user_id'] == 2 assert users[1][response_name_constants.track_count] == 4 assert users[1][response_name_constants.playlist_count] == 1 assert users[1][response_name_constants.album_count] == 0 assert users[1][response_name_constants.follower_count] == 1 assert users[1][response_name_constants.followee_count] == 1 assert users[1][response_name_constants.repost_count] == 2 assert users[2]['user_id'] == 3 assert users[2][response_name_constants.track_count] == 3 assert users[2][response_name_constants.playlist_count] == 0 assert users[2][response_name_constants.album_count] == 1 assert users[2][response_name_constants.follower_count] == 2 assert users[2][response_name_constants.followee_count] == 0 assert users[2][response_name_constants.repost_count] == 2 assert users[3]['user_id'] == 4 assert users[3][response_name_constants.track_count] == 0 assert users[3][response_name_constants.playlist_count] == 0 assert users[3][response_name_constants.album_count] == 0 assert users[3][response_name_constants.follower_count] == 0 assert users[3][response_name_constants.followee_count] == 0 assert users[3][response_name_constants.repost_count] == 0 assert users[4]['user_id'] == 5 assert users[4][response_name_constants.track_count] == 0 assert users[4][response_name_constants.playlist_count] == 0 assert users[4][response_name_constants.album_count] == 0 assert users[4][response_name_constants.follower_count] == 0 assert users[4][response_name_constants.followee_count] == 0 assert users[4][response_name_constants.repost_count] == 0 curr_user_ids = [1, 2, 3] curr_users = [{ "user_id": 1, "is_verified": False }, { "user_id": 2, "is_verified": False }, { "user_id": 3, "is_verified": False }] users = populate_user_metadata(session, curr_user_ids, curr_users, 1) assert len(users) == 3 assert users[0]['user_id'] == 1 assert users[0][ response_name_constants.does_current_user_follow] == False assert users[0][ response_name_constants.current_user_followee_follow_count] == 0 assert users[0][response_name_constants.balance] == '0' assert users[0][ response_name_constants.associated_wallets_balance] == '0' assert users[1]['user_id'] == 2 assert users[1][ response_name_constants.does_current_user_follow] == True assert users[1][ response_name_constants.current_user_followee_follow_count] == 0 assert users[1][response_name_constants.balance] == '0' assert users[1][ response_name_constants.associated_wallets_balance] == '0' assert users[2]['user_id'] == 3 assert users[2][ response_name_constants.does_current_user_follow] == True assert users[2][ response_name_constants.current_user_followee_follow_count] == 1 assert users[2][response_name_constants.balance] == '0' assert users[2][ response_name_constants.associated_wallets_balance] == '0'
def user_search_query( session, search_str, limit, offset, is_auto_complete, current_user_id ): res = sqlalchemy.text( f""" select u.user_id, b.balance, b.associated_wallets_balance, is_followed from ( select user_id, is_followed from ( select user_id, is_followed, ( sum(score) + (:follower_weight * log(case when (follower_count = 0) then 1 else follower_count end)) + (case when (handle=query) then :handle_match_boost else 0 end) + (:name_weight * similarity(coalesce(name, ''), query)) { "+ (case when (is_followed) " + "then :current_user_saved_match_boost else 0 end)" if current_user_id else "" } ) as total_score from ( select d."user_id" as user_id, d."word" as word, d."handle" as handle, similarity(d."word", :query) as score, d."user_name" as name, :query as query, d."follower_count" as follower_count { ', f."follower_user_id" is not null as is_followed' if current_user_id else ", false as is_followed" } from "user_lexeme_dict" d { "left outer join (select follower_user_id, followee_user_id from follows " + "where follows.is_current = true " + "and follows.is_delete = false " + "and follows.follower_user_id = :current_user_id) f " + "on f.followee_user_id = d.user_id" if current_user_id else "" } where d."word" % :query OR d."handle" = :query ) as results group by user_id, name, query, handle, follower_count, is_followed ) as results2 order by total_score desc, user_id asc limit :limit offset :offset ) as u left join user_balances b on u.user_id = b.user_id """ ) user_result_proxy = session.execute( res, { "query": search_str, "limit": limit, "offset": offset, "name_weight": user_name_weight, "follower_weight": user_follower_weight, "current_user_id": current_user_id, "handle_match_boost": user_handle_exact_match_boost, "current_user_saved_match_boost": current_user_saved_match_boost, }, ) user_info = user_result_proxy.fetchall() user_cols = user_result_proxy.keys() # user_ids is list of tuples - simplify to 1-D list user_ids = [user[user_cols.index("user_id")] for user in user_info] # if user has a follower_user_id, the current user has followed that user followed_users = { user[0] for user in user_info if user[user_cols.index("is_followed")] } users = get_unpopulated_users(session, user_ids) if is_auto_complete: for i, user in enumerate(users): balance = user_info[i][1] associated_wallets_balance = user_info[i][2] user[response_name_constants.balance] = balance user[ response_name_constants.associated_wallets_balance ] = associated_wallets_balance else: # bundle peripheral info into user results users = populate_user_metadata(session, user_ids, users, current_user_id) # Preserve order from user_ids above user_map = {} for u in users: user_map[u["user_id"]] = u users = [user_map[user_id] for user_id in user_ids] # Sort users by extra criteria for "best match" users.sort(key=cmp_to_key(compare_users)) users_response = { "all": users, "followed": list(filter(lambda user: user["user_id"] in followed_users, users)), } return users_response
def get_users_account(args): db = get_db_read_replica() with db.scoped_session() as session: # Create initial query base_query = session.query(User) # Don't return the user if they have no wallet or handle (user creation did not finish properly on chain) base_query = base_query.filter( User.is_current == True, User.wallet != None, User.handle != None) if "wallet" not in args: raise exceptions.ArgumentError("Missing wallet param") wallet = args.get("wallet") wallet = wallet.lower() if len(wallet) == 42: base_query = base_query.filter_by(wallet=wallet) base_query = base_query.order_by(asc(User.created_at)) else: raise exceptions.ArgumentError("Invalid wallet length") # If user cannot be found, exit early and return empty response user = base_query.first() if not user: return None user = helpers.model_to_dictionary(user) user_id = user['user_id'] # bundle peripheral info into user results users = populate_user_metadata( session, [user_id], [user], user_id, True) user = users[0] # Get saved playlists / albums ids saved_query = session.query(Save.save_item_id).filter( Save.user_id == user_id, Save.is_current == True, Save.is_delete == False, or_(Save.save_type == SaveType.playlist, Save.save_type == SaveType.album) ) saved_query_results = saved_query.all() save_collection_ids = [item[0] for item in saved_query_results] # Get Playlist/Albums saved or owned by the user playlist_query = session.query(Playlist).filter( or_( and_(Playlist.is_current == True, Playlist.is_delete == False, Playlist.playlist_owner_id == user_id), and_(Playlist.is_current == True, Playlist.is_delete == False, Playlist.playlist_id.in_(save_collection_ids)) ) ).order_by(desc(Playlist.created_at)) playlists = playlist_query.all() playlists = helpers.query_result_to_list(playlists) playlist_owner_ids = list( set([playlist['playlist_owner_id'] for playlist in playlists])) # Get Users for the Playlist/Albums user_query = session.query(User).filter( and_(User.is_current == True, User.user_id.in_(playlist_owner_ids)) ) users = user_query.all() users = helpers.query_result_to_list(users) user_map = {} stripped_playlists = [] # Map the users to the playlists/albums for playlist_owner in users: user_map[playlist_owner['user_id']] = playlist_owner for playlist in playlists: playlist_owner = user_map[playlist['playlist_owner_id']] stripped_playlists.append({ 'id': playlist['playlist_id'], 'name': playlist['playlist_name'], 'is_album': playlist['is_album'], 'user': {'id': playlist_owner['user_id'], 'handle': playlist_owner['handle']} }) user['playlists'] = stripped_playlists return user
def get_top_genre_users(args): genres = [] if "genre" in args: genres = args.get("genre") # If the with_users url arg is provided, then populate the user metadata else return user ids with_users = args.get("with_users", False) db = get_db_read_replica() with db.scoped_session() as session: with_genres = len(genres) != 0 # Associate the user w/ a genre by counting the total # of tracks per genre # taking the genre w/ the most tracks (using genre name as secondary sort) user_genre_count_query = (session.query( User.user_id.label('user_id'), Track.genre.label('genre'), func.row_number().over( partition_by=User.user_id, order_by=(desc(func.count(Track.genre)), asc( Track.genre))).label("row_number")).join( Track, Track.owner_id == User.user_id).filter( User.is_current == True, User.is_creator == True, Track.is_unlisted == False, Track.stem_of == None, Track.is_current == True, Track.is_delete == False).group_by( User.user_id, Track.genre).order_by( desc(func.count(Track.genre)), asc(Track.genre))) user_genre_count_query = user_genre_count_query.subquery( 'user_genre_count_query') user_genre_query = (session.query( user_genre_count_query.c.user_id.label('user_id'), user_genre_count_query.c.genre.label('genre'), ).filter(user_genre_count_query.c.row_number == 1).subquery( 'user_genre_query')) # Using the subquery of user to associated genre, # filter by the requested genres and # sort by user follower count user_genre_followers_query = ( session.query(user_genre_query.c.user_id.label('user_id')).join( Follow, Follow.followee_user_id == user_genre_query.c.user_id).filter( Follow.is_current == True, Follow.is_delete == False).group_by( user_genre_query.c.user_id, user_genre_query.c.genre).order_by( # desc('follower_count') desc(func.count(Follow.follower_user_id)))) if with_genres: user_genre_followers_query = user_genre_followers_query.filter( user_genre_query.c.genre.in_(genres)) # If the with_users flag is not set, respond with the user_ids users = paginate_query(user_genre_followers_query).all() user_ids = list(map(lambda user: user[0], users)) # If the with_users flag is used, retrieve the user metadata if with_users: user_query = session.query(User).filter(User.user_id.in_(user_ids), User.is_current == True) users = user_query.all() users = helpers.query_result_to_list(users) queried_user_ids = list(map(lambda user: user["user_id"], users)) users = populate_user_metadata(session, queried_user_ids, users, None) # Sort the users so that it's in the same order as the previous query user_map = {user['user_id']: user for user in users} users = [user_map[user_id] for user_id in user_ids] return {'users': users} return {'user_ids': user_ids}
def search_tags(): search_str = request.args.get("query", type=str) current_user_id = get_current_user_id(required=False) if not search_str: raise exceptions.ArgumentError("Invalid value for parameter 'query'") user_tag_count = request.args.get("user_tag_count", type=str) if not user_tag_count: user_tag_count = "2" kind = request.args.get("kind", type=str, default="all") validSearchKinds = [SearchKind.all, SearchKind.tracks, SearchKind.users] try: searchKind = SearchKind[kind] if searchKind not in validSearchKinds: raise Exception except Exception: return api_helpers.error_response( "Invalid value for parameter 'kind' must be in %s" % [k.name for k in validSearchKinds], 400) results = {} (limit, offset) = get_pagination_vars() db = get_db_read_replica() with db.scoped_session() as session: if (searchKind in [SearchKind.all, SearchKind.tracks]): results['tracks'] = search_track_tags( session, { 'search_str': search_str, 'current_user_id': current_user_id, 'limit': limit, 'offset': offset }) if (searchKind in [SearchKind.all, SearchKind.users]): results['users'] = search_user_tags( session, { 'search_str': search_str, 'current_user_id': current_user_id, "user_tag_count": user_tag_count, 'limit': limit, 'offset': offset }) # Add personalized results for a given user if current_user_id: if (searchKind in [SearchKind.all, SearchKind.tracks]): # Query saved tracks for the current user that contain this tag track_ids = [track['track_id'] for track in results['tracks']] track_play_counts = { track['track_id']: track[response_name_constants.play_count] for track in results['tracks'] } saves_query = (session.query(Save.save_item_id).filter( Save.is_current == True, Save.is_delete == False, Save.save_type == SaveType.track, Save.user_id == current_user_id, Save.save_item_id.in_(track_ids)).all()) saved_track_ids = [i[0] for i in saves_query] saved_tracks = (session.query(Track).filter( Track.is_current == True, Track.is_delete == False, Track.is_unlisted == False, Track.stem_of == None, Track.track_id.in_(saved_track_ids), ).all()) saved_tracks = helpers.query_result_to_list(saved_tracks) for saved_track in saved_tracks: saved_track_id = saved_track["track_id"] saved_track[response_name_constants.play_count] = \ track_play_counts.get(saved_track_id, 0) saved_tracks = \ populate_track_metadata( session, saved_track_ids, saved_tracks, current_user_id) # Sort and paginate play_count_sorted_saved_tracks = \ sorted( saved_tracks, key=lambda i: i[response_name_constants.play_count], reverse=True) play_count_sorted_saved_tracks = \ play_count_sorted_saved_tracks[slice( offset, offset + limit, 1)] results['saved_tracks'] = play_count_sorted_saved_tracks if (searchKind in [SearchKind.all, SearchKind.users]): # Query followed users that have referenced this tag user_ids = [user['user_id'] for user in results['users']] followed_user_query = (session.query( Follow.followee_user_id).filter( Follow.is_current == True, Follow.is_delete == False, Follow.follower_user_id == current_user_id, Follow.followee_user_id.in_(user_ids)).all()) followed_user_ids = [i[0] for i in followed_user_query] followed_users = get_unpopulated_users(session, followed_user_ids) followed_users = \ populate_user_metadata( session, followed_user_ids, followed_users, current_user_id ) followed_users_followee_sorted = \ sorted( followed_users, key=lambda i: i[response_name_constants.follower_count], reverse=True) followed_users_followee_sorted = \ followed_users_followee_sorted[slice( offset, offset + limit, 1)] results['followed_users'] = followed_users_followee_sorted return api_helpers.success_response(results)
def get_followees_for_user(args): users = [] follower_user_id = args.get('follower_user_id') current_user_id = args.get('current_user_id') limit = args.get('limit') offset = args.get('offset') db = get_db_read_replica() with db.scoped_session() as session: # correlated subquery sqlalchemy code: # https://groups.google.com/forum/#!topic/sqlalchemy/WLIy8jxD7qg inner_follow = aliased(Follow) outer_follow = aliased(Follow) # subquery to get a user's follower count inner_select = ( session.query( func.count(inner_follow.followee_user_id) ) .filter( inner_follow.followee_user_id == outer_follow.followee_user_id, inner_follow.is_current == True, inner_follow.is_delete == False ) .correlate(outer_follow) ) # get all users followed by input user, sorted by their follower count desc outer_select = ( session.query( outer_follow.followee_user_id, inner_select.as_scalar().label(response_name_constants.follower_count) ) .filter( outer_follow.follower_user_id == follower_user_id, outer_follow.is_current == True, outer_follow.is_delete == False ) .group_by(outer_follow.followee_user_id) .order_by(desc(response_name_constants.follower_count)) ) followee_user_ids_by_follower_count = add_query_pagination( outer_select, limit, offset).all() user_ids = [user_id for (user_id, follower_count) in followee_user_ids_by_follower_count] # get all users for above user_ids users = get_unpopulated_users(session, user_ids) # bundle peripheral info into user results users = populate_user_metadata( session, user_ids, users, current_user_id) # order by follower_count desc users.sort( key=lambda user: user[response_name_constants.follower_count], reverse=True ) return users