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 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 = get_unpopulated_users(session, user_ids) 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_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: # 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( desc(response_name_constants.follower_count), # 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 = add_query_pagination( outer_select, limit, offset).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 = 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, 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 users
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)
def get_users_by_id(session, user_ids, current_user_id=None): users = get_unpopulated_users(session, user_ids) if not current_user_id: current_user_id = get_current_user_id(required=False) # bundle peripheral info into user results populated_users = populate_user_metadata(session, user_ids, users, current_user_id) user_map = {} for user in populated_users: user_map[user['user_id']] = user return user_map
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 track_search_query( session, search_str, limit, offset, is_auto_complete, current_user_id, only_downloadable, ): res = sqlalchemy.text( # pylint: disable=C0301 f""" select track_id, b.balance, b.associated_wallets_balance, u.is_saved from ( select distinct on (owner_id) track_id, owner_id, is_saved, total_score from ( select track_id, owner_id, is_saved, ( (:similarity_weight * sum(score)) + (:title_weight * similarity(coalesce(title, ''), query)) + (:user_name_weight * similarity(coalesce(user_name, ''), query)) + (:repost_weight * log(case when (repost_count = 0) then 1 else repost_count end)) + (case when (lower(query) = coalesce(title, '')) then :title_match_boost else 0 end) + (case when (lower(query) = handle) then :handle_match_boost else 0 end) + (case when (lower(query) = user_name) then :user_name_match_boost else 0 end) { '+ (case when (is_saved) then :current_user_saved_match_boost else 0 end)' if current_user_id else "" } ) as total_score from ( select d."track_id" as track_id, d."word" as word, similarity(d."word", :query) as score, d."track_title" as title, :query as query, d."user_name" as user_name, d."handle" as handle, d."repost_count" as repost_count, d."owner_id" as owner_id { ',s."user_id" is not null as is_saved' if current_user_id else ", false as is_saved" } from "track_lexeme_dict" d { "left outer join (select save_item_id, user_id from saves where saves.save_type = 'track' " + "and saves.is_current = true " + "and saves.is_delete = false and saves.user_id = :current_user_id )" + " s on s.save_item_id = d.track_id" if current_user_id else "" } { 'inner join "tracks" t on t.track_id = d.track_id' if only_downloadable else "" } where (d."word" % lower(:query) or d."handle" = lower(:query) or d."user_name" % lower(:query)) { "and (t.download->>'is_downloadable')::boolean is True" if only_downloadable else "" } ) as results group by track_id, title, query, user_name, handle, repost_count, owner_id, is_saved ) as results2 order by owner_id, total_score desc ) as u left join user_balances b on u.owner_id = b.user_id order by total_score desc limit :limit offset :offset; """ ) track_result_proxy = session.execute( res, params={ "query": search_str, "limit": limit, "offset": offset, "title_weight": search_title_weight, "repost_weight": search_repost_weight, "similarity_weight": search_similarity_weight, "current_user_id": current_user_id, "user_name_weight": search_user_name_weight, "title_match_boost": search_title_exact_match_boost, "handle_match_boost": search_handle_exact_match_boost, "user_name_match_boost": search_user_name_exact_match_boost, "current_user_saved_match_boost": current_user_saved_match_boost, }, ) track_data = track_result_proxy.fetchall() track_cols = track_result_proxy.keys() # track_ids is list of tuples - simplify to 1-D list track_ids = [track[track_cols.index("track_id")] for track in track_data] saved_tracks = { track[0] for track in track_data if track[track_cols.index("is_saved")] } tracks = get_unpopulated_tracks(session, track_ids, True) # TODO: Populate track 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: # fetch users for tracks track_owner_ids = list(map(lambda track: track["owner_id"], tracks)) users = get_unpopulated_users(session, track_owner_ids) users_dict = {user["user_id"]: user for user in users} # attach user objects to track objects for i, track in enumerate(tracks): user = users_dict[track["owner_id"]] # Add user balance balance = track_data[i][1] associated_balance = track_data[i][2] user[response_name_constants.balance] = balance user[ response_name_constants.associated_wallets_balance ] = associated_balance track["user"] = user else: # bundle peripheral info into track results tracks = populate_track_metadata(session, track_ids, tracks, current_user_id) # Preserve order from track_ids above tracks_map = {} for t in tracks: tracks_map[t["track_id"]] = t tracks = [tracks_map[track_id] for track_id in track_ids] tracks_response = { "all": tracks, "saved": list(filter(lambda track: track["track_id"] in saved_tracks, tracks)), } return tracks_response
def playlist_search_query(session, searchStr, limit, offset, is_album, personalized, is_auto_complete, current_user_id): if personalized and not current_user_id: return [] table_name = 'album_lexeme_dict' if is_album else 'playlist_lexeme_dict' repost_type = RepostType.album if is_album else RepostType.playlist save_type = SaveType.album if is_album else SaveType.playlist # SQLAlchemy doesn't expose a way to escape a string with double-quotes instead of # single-quotes, so we have to use traditional string substitution. This is safe # because the value is not user-specified. res = sqlalchemy.text( # pylint: disable=C0301 f""" select playlist_id from ( select playlist_id, (sum(score) + (:name_weight * similarity(coalesce(playlist_name, ''), query))) as total_score from ( select d."playlist_id" as playlist_id, d."word" as word, similarity(d."word", :query) as score, d."playlist_name" as playlist_name, :query as query from "{table_name}" d { 'inner join "saves" s on s.save_item_id = d.playlist_id' if personalized and current_user_id else "" } where d."word" % :query { "and s.save_type='" + save_type + "' and s.is_current=true and s.is_delete=false and s.user_id=:current_user_id" if personalized and current_user_id else "" } ) as results group by playlist_id, playlist_name, query ) as results2 order by total_score desc, playlist_id asc limit :limit offset :offset; """) playlist_ids = session.execute( res, { "query": searchStr, "limit": limit, "offset": offset, "name_weight": playlistNameWeight, "current_user_id": current_user_id }, ).fetchall() # playlist_ids is list of tuples - simplify to 1-D list playlist_ids = [i[0] for i in playlist_ids] playlists = get_unpopulated_playlists(session, playlist_ids, True) if is_auto_complete: # fetch users for playlists playlist_owner_ids = list( map(lambda playlist: playlist["playlist_owner_id"], playlists)) users = get_unpopulated_users(session, playlist_owner_ids) users_dict = {user["user_id"]: user for user in users} # attach user objects to playlist objects for playlist in playlists: playlist["user"] = users_dict[playlist["playlist_owner_id"]] else: # bundle peripheral info into playlist results playlists = populate_playlist_metadata(session, playlist_ids, playlists, [repost_type], [save_type], current_user_id) # preserve order from playlist_ids above playlists = [ next((p for p in playlists if p["playlist_id"] == playlist_id), None) for playlist_id in playlist_ids ] return playlists
def track_search_query(session, searchStr, limit, offset, personalized, is_auto_complete, current_user_id, only_downloadable): if personalized and not current_user_id: return [] res = sqlalchemy.text( # pylint: disable=C0301 f""" select track_id from ( select track_id, (sum(score) + (:title_weight * similarity(coalesce(title, ''), query))) as total_score from ( select d."track_id" as track_id, d."word" as word, similarity(d."word", :query) as score, d."track_title" as title, :query as query from "track_lexeme_dict" d { 'inner join "saves" s on s.save_item_id = d.track_id' if personalized and current_user_id else "" } { 'inner join "tracks" t on t.track_id = d.track_id' if only_downloadable else "" } where d."word" % :query { "and s.save_type='track' and s.is_current=true and " + "s.is_delete=false and s.user_id = :current_user_id" if personalized and current_user_id else "" } { "and (t.download->>'is_downloadable')::boolean is True" if only_downloadable else "" } ) as results group by track_id, title, query ) as results2 order by total_score desc, track_id asc limit :limit offset :offset; """) track_ids = session.execute( res, { "query": searchStr, "limit": limit, "offset": offset, "title_weight": trackTitleWeight, "current_user_id": current_user_id }, ).fetchall() # track_ids is list of tuples - simplify to 1-D list track_ids = [i[0] for i in track_ids] tracks = get_unpopulated_tracks(session, track_ids, True) if is_auto_complete == True: # fetch users for tracks track_owner_ids = list(map(lambda track: track["owner_id"], tracks)) users = get_unpopulated_users(session, track_owner_ids) users_dict = {user["user_id"]: user for user in users} # attach user objects to track objects for track in tracks: track["user"] = users_dict[track["owner_id"]] else: # bundle peripheral info into track results tracks = populate_track_metadata(session, track_ids, tracks, current_user_id) # preserve order from track_ids above tracks = [ next((t for t in tracks if t["track_id"] == track_id), None) for track_id in track_ids ] return tracks
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
def get_top_genre_users(args): genres = [] if "genre" in args: genres = args.get("genre") if isinstance(genres, str): genres = [genres] # 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: users = get_unpopulated_users(session, user_ids) 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 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 track_search_query(session, searchStr, limit, offset, personalized, is_auto_complete, current_user_id, only_downloadable): if personalized and not current_user_id: return [] res = sqlalchemy.text( # pylint: disable=C0301 f""" select track_id from ( select track_id, (sum(score) + (:title_weight * similarity(coalesce(title, ''), query))) as total_score from ( select d."track_id" as track_id, d."word" as word, similarity(d."word", :query) as score, d."track_title" as title, :query as query from "track_lexeme_dict" d { 'inner join "saves" s on s.save_item_id = d.track_id' if personalized and current_user_id else "" } { 'inner join "tracks" t on t.track_id = d.track_id' if only_downloadable else "" } where d."word" % :query { "and s.save_type='track' and s.is_current=true and " + "s.is_delete=false and s.user_id = :current_user_id" if personalized and current_user_id else "" } { "and (t.download->>'is_downloadable')::boolean is True" if only_downloadable else "" } ) as results group by track_id, title, query ) as results2 order by total_score desc, track_id asc limit :limit offset :offset; """) track_ids = session.execute( res, { "query": searchStr, "limit": max(limit, MIN_SEARCH_LEXEME_LIMIT), "offset": offset, "title_weight": trackTitleWeight, "current_user_id": current_user_id }, ).fetchall() # track_ids is list of tuples - simplify to 1-D list track_ids = [i[0] for i in track_ids] tracks = get_unpopulated_tracks(session, track_ids, True) # TODO: Populate track 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 == True: # fetch users for tracks track_owner_ids = list(map(lambda track: track["owner_id"], tracks)) users = get_unpopulated_users(session, track_owner_ids) users_dict = {user["user_id"]: user for user in users} # attach user objects to track objects for track in tracks: track["user"] = users_dict[track["owner_id"]] tracks = populate_track_repost_counts(session, track_ids, tracks) else: # bundle peripheral info into track results tracks = populate_track_metadata(session, track_ids, tracks, current_user_id) # Preserve order from track_ids above tracks_map = {} for t in tracks: tracks_map[t["track_id"]] = t tracks = [tracks_map[track_id] for track_id in track_ids] # Sort tracks by extra criteria for "best match" tracks.sort(key=cmp_to_key(compare_tracks)) return tracks[0:limit]
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( {playlist["playlist_owner_id"] for playlist in playlists}) # Get Users for the Playlist/Albums users = get_unpopulated_users(session, playlist_owner_ids) 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_playlist = { "id": playlist["playlist_id"], "name": playlist["playlist_name"], "is_album": playlist["is_album"], "user": { "id": playlist_owner["user_id"], "handle": playlist_owner["handle"], }, } if playlist_owner["is_deactivated"]: stripped_playlist["user"]["is_deactivated"] = True stripped_playlists.append(stripped_playlist) user["playlists"] = stripped_playlists return user
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 playlist_search_query( session, search_str, limit, offset, is_album, is_auto_complete, current_user_id, ): table_name = "album_lexeme_dict" if is_album else "playlist_lexeme_dict" repost_type = RepostType.album if is_album else RepostType.playlist save_type = SaveType.album if is_album else SaveType.playlist # SQLAlchemy doesn't expose a way to escape a string with double-quotes instead of # single-quotes, so we have to use traditional string substitution. This is safe # because the value is not user-specified. res = sqlalchemy.text( # pylint: disable=C0301 f""" select p.playlist_id, b.balance, b.associated_wallets_balance, is_saved from ( select distinct on (owner_id) playlist_id, owner_id, is_saved, total_score from ( select playlist_id, owner_id, is_saved, ( (:similarity_weight * sum(score)) + (:title_weight * similarity(coalesce(playlist_name, ''), query)) + (:user_name_weight * similarity(coalesce(user_name, ''), query)) + (:repost_weight * log(case when (repost_count = 0) then 1 else repost_count end)) + (case when (lower(query) = coalesce(playlist_name, '')) then :title_match_boost else 0 end) + (case when (lower(query) = handle) then :handle_match_boost else 0 end) + (case when (lower(query) = user_name) then :user_name_match_boost else 0 end) { '+ (case when (is_saved) then ' + ':current_user_saved_match_boost else 0 end)' if current_user_id else "" } ) as total_score from ( select d."playlist_id" as playlist_id, d."word" as word, similarity(d."word", :query) as score, d."playlist_name" as playlist_name, :query as query, d."repost_count" as repost_count, d."handle" as handle, d."user_name" as user_name, d."owner_id" as owner_id { ', s."user_id" is not null as is_saved' if current_user_id else ", false as is_saved" } from "{table_name}" d { "left outer join (select save_item_id, user_id from saves where saves.save_type = '" + save_type + "' and saves.is_current = true and " + "saves.is_delete = false and saves.user_id = :current_user_id ) " + "s on s.save_item_id = d.playlist_id" if current_user_id else "" } where (d."word" % lower(:query) or d."handle" = lower(:query) or d."user_name" % lower(:query)) ) as results group by playlist_id, playlist_name, query, repost_count, user_name, handle, owner_id, is_saved ) as results2 order by owner_id, total_score desc ) as p left join user_balances b on p.owner_id = b.user_id order by total_score desc limit :limit offset :offset; """ ) playlist_result_proxy = session.execute( res, { "query": search_str, "limit": limit, "offset": offset, "title_weight": search_title_weight, "repost_weight": search_repost_weight, "similarity_weight": search_similarity_weight, "current_user_id": current_user_id, "user_name_weight": search_user_name_weight, "title_match_boost": search_title_exact_match_boost, "handle_match_boost": search_handle_exact_match_boost, "user_name_match_boost": search_user_name_exact_match_boost, "current_user_saved_match_boost": current_user_saved_match_boost, }, ) playlist_data = playlist_result_proxy.fetchall() playlist_cols = playlist_result_proxy.keys() # playlist_ids is list of tuples - simplify to 1-D list playlist_ids = [ playlist[playlist_cols.index("playlist_id")] for playlist in playlist_data ] saved_playlists = { playlist[0] for playlist in playlist_data if playlist[playlist_cols.index("is_saved")] } playlists = get_unpopulated_playlists(session, playlist_ids, True) # TODO: Populate playlist 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: # fetch users for playlists playlist_owner_ids = list( map(lambda playlist: playlist["playlist_owner_id"], playlists) ) users = get_unpopulated_users(session, playlist_owner_ids) users_dict = {user["user_id"]: user for user in users} # attach user objects to playlist objects for i, playlist in enumerate(playlists): user = users_dict[playlist["playlist_owner_id"]] # Add user balance balance = playlist_data[i][1] associated_balance = playlist_data[i][2] user[response_name_constants.balance] = balance user[ response_name_constants.associated_wallets_balance ] = associated_balance playlist["user"] = user else: # bundle peripheral info into playlist results playlists = populate_playlist_metadata( session, playlist_ids, playlists, [repost_type], [save_type], current_user_id, ) # Preserve order from playlist_ids above playlists_map = {} for p in playlists: playlists_map[p["playlist_id"]] = p playlists = [playlists_map[playlist_id] for playlist_id in playlist_ids] playlists_resp = { "all": playlists, "saved": list( filter( lambda playlist: playlist["playlist_id"] in saved_playlists, playlists ) ), } return playlists_resp
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 = 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)