def get_playlists(): playlists = [] current_user_id = get_current_user_id(required=False) filter_out_private_playlists = True db = get_db() with db.scoped_session() as session: try: playlist_query = (session.query(Playlist).filter( Playlist.is_current == True)) # playlist ids filter if the optional query param is passed in if "playlist_id" in request.args: playlist_id_str_list = request.args.getlist("playlist_id") playlist_id_list = [] try: playlist_id_list = [int(y) for y in playlist_id_str_list] playlist_query = playlist_query.filter( Playlist.playlist_id.in_(playlist_id_list)) except ValueError as e: raise exceptions.ArgumentError( "Invalid value found in playlist id list", e) if "user_id" in request.args: user_id = request.args.get("user_id", type=int) # user id filter if the optional query param is passed in playlist_query = playlist_query.filter( Playlist.playlist_owner_id == user_id) # if the current user is the same as the user passed in through the query param then we're trying # to get playlists for, check if the users are the same. if they are the same, the current user is # trying to request their own playlists, so allow them to see private playlists if current_user_id and user_id and (int(current_user_id) == int(user_id)): filter_out_private_playlists = False if filter_out_private_playlists: playlist_query = playlist_query.filter( Playlist.is_private == False) playlist_query = playlist_query.order_by(desc(Playlist.created_at)) playlists = paginate_query(playlist_query).all() playlists = helpers.query_result_to_list(playlists) # retrieve playlist ids list playlist_ids = list( map(lambda playlist: playlist["playlist_id"], playlists)) current_user_id = get_current_user_id(required=False) # bundle peripheral info into playlist results playlists = populate_playlist_metadata( session, playlist_ids, playlists, [RepostType.playlist, RepostType.album], [SaveType.playlist, SaveType.album], current_user_id) except sqlalchemy.orm.exc.NoResultFound: pass return api_helpers.success_response(playlists)
def get_saves_route(save_type): try: user_id = get_current_user_id() save_results = get_saves(save_type, user_id) return api_helpers.success_response(save_results) except exceptions.ArgumentError as e: return api_helpers.error_response(str(e), 400)
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 get_repost_feed_for_user_route(user_id): args = to_dict(request.args) if "with_users" in request.args: args["with_users"] = parse_bool_param(request.args.get("with_users")) args["current_user_id"] = get_current_user_id(required=False) feed_results = get_repost_feed_for_user(user_id, args) return api_helpers.success_response(feed_results)
def get_top_followee_saves_route(type): """ Gets a global view into the most saved of `type` amongst followees. Requires an account. This endpoint is useful in generating views like: - Most favorited Args: type: (string) The `type` (same as repost/save type) to query from. Currently only track is supported. limit?: (number) default=25, max=100 """ args = to_dict(request.args) if "limit" in request.args: args["limit"] = min(request.args.get("limit", type=int), 100) else: args["limit"] = 25 if "with_users" in request.args: args["with_users"] = parse_bool_param(request.args.get("with_users")) user_id = get_current_user_id() args["user_id"] = user_id try: tracks = get_top_followee_saves(type, args) return api_helpers.success_response(tracks) except exceptions.ArgumentError as e: return api_helpers.error_response(str(e), 400)
def get_top_followee_windowed_route(type, window): """ Gets a windowed (over a certain timerange) view into the "top" of a certain type amongst followees. Requires an account. This endpoint is useful in generating views like: - New releases Args: type: (string) The `type` (same as repost/save type) to query from. Currently only track is supported. window: (string) The window from now() to look back over. Supports all standard SqlAlchemy interval notation (week, month, year, etc.). limit?: (number) default=25, max=100 """ args = to_dict(request.args) if "limit" in request.args: args["limit"] = min(request.args.get("limit", type=int), 100) else: args["limit"] = 25 if "with_users" in request.args: args["with_users"] = parse_bool_param(request.args.get("with_users")) user_id = get_current_user_id() args["user_id"] = user_id try: tracks = get_top_followee_windowed(type, window, args) return api_helpers.success_response(tracks) except exceptions.ArgumentError as e: return api_helpers.error_response(str(e), 400)
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_top_followee_saves(saveType, args): if saveType != 'track': raise exceptions.ArgumentError( "Invalid type provided, must be one of 'track'") limit = args.get('limit', 25) current_user_id = get_current_user_id() db = get_db_read_replica() with db.scoped_session() as session: # Construct a subquery of all followees followee_user_ids = (session.query(Follow.followee_user_id).filter( Follow.follower_user_id == current_user_id, Follow.is_current == True, Follow.is_delete == False)) followee_user_ids_subquery = followee_user_ids.subquery() # Construct a subquery of all saves from followees aggregated by id save_count = (session.query( Save.save_item_id, func.count(Save.save_item_id).label( response_name_constants.save_count)).join( followee_user_ids_subquery, Save.user_id == followee_user_ids_subquery.c.followee_user_id).filter( Save.is_current == True, Save.is_delete == False, Save.save_type == saveType, ).group_by(Save.save_item_id).order_by( desc(response_name_constants.save_count)).limit(limit)) save_count_subquery = save_count.subquery() # Query for tracks joined against followee save counts tracks_query = (session.query(Track, ).join( save_count_subquery, Track.track_id == save_count_subquery.c.save_item_id).filter( Track.is_current == True, Track.is_delete == False, Track.is_unlisted == False, Track.stem_of == None, )) tracks_query_results = tracks_query.all() tracks = helpers.query_result_to_list(tracks_query_results) track_ids = list(map(lambda track: track['track_id'], tracks)) # bundle peripheral info into track results tracks = populate_track_metadata(session, track_ids, tracks, current_user_id) if args.get('with_users', False): user_id_list = get_users_ids(tracks) users = get_users_by_id(session, user_id_list) for track in tracks: user = users[track['owner_id']] if user: track['user'] = user return tracks
def get_saves(save_type): save_query_type = None if save_type == 'albums': save_query_type = SaveType.album elif save_type == 'playlists': save_query_type = SaveType.playlist elif save_type == 'tracks': save_query_type = SaveType.track else: raise exceptions.ArgumentError("Invalid save type provided") save_results = [] current_user_id = get_current_user_id() db = get_db() with db.scoped_session() as session: query = ( session.query(Save) .filter( Save.user_id == current_user_id, Save.is_current == True, Save.is_delete == False, Save.save_type == save_query_type ) ) # filter out saves for deleted entries if save_type == 'albums': query = query.filter( Save.save_item_id.in_( session.query(Playlist.playlist_id).filter( Playlist.is_album == True, Playlist.is_current == True ) ) ) elif save_type == 'playlists': query = query.filter( Save.save_item_id.in_( session.query(Playlist.playlist_id).filter( Playlist.is_album == False, Playlist.is_current == True ) ) ) elif save_type == 'tracks': query = query.filter( Save.save_item_id.in_( session.query(Track.track_id).filter( Track.is_current == True ) ) ) query_results = paginate_query(query).all() save_results = helpers.query_result_to_list(query_results) return api_helpers.success_response(save_results)
def get_remix_track_parents_route(track_id): args = to_dict(request.args) if "with_users" in request.args: args["with_users"] = parse_bool_param(request.args.get("with_users")) args["track_id"] = track_id args["current_user_id"] = get_current_user_id(required=False) limit, offset = get_pagination_vars() args["limit"] = limit args["offset"] = offset tracks = get_remix_track_parents(args) return api_helpers.success_response(tracks)
def get_followees_for_user_route(follower_user_id): current_user_id = get_current_user_id(required=False) (limit, offset) = get_pagination_vars() args = { 'follower_user_id': follower_user_id, 'current_user_id': current_user_id, 'limit': limit, 'offset': offset } users = get_followees_for_user(args) return api_helpers.success_response(users)
def get_followees_for_user_route(follower_user_id): current_user_id = get_current_user_id(required=False) (limit, offset) = get_pagination_vars() args = { "follower_user_id": follower_user_id, "current_user_id": current_user_id, "limit": limit, "offset": offset, } users = get_followees_for_user(args) return api_helpers.success_response(users)
def get_playlists_route(): args = to_dict(request.args) if "playlist_id" in request.args: args["playlist_id"] = [int(y) for y in request.args.getlist("playlist_id")] if "user_id" in request.args: args["user_id"] = request.args.get("user_id", type=int) if "with_users" in request.args: args["with_users"] = parse_bool_param(request.args.get("with_users")) current_user_id = get_current_user_id(required=False) args["current_user_id"] = current_user_id playlists = get_playlists(args) return api_helpers.success_response(playlists)
def get_users_route(): args = to_dict(request.args) if "is_creator" in request.args: args["is_creator"] = parse_bool_param(request.args.get("is_creator")) if "id" in request.args: args["id"] = parse_id_array_param(request.args.getlist("id")) if "min_block_number" in request.args: args["min_block_number"] = request.args.get("min_block_number", type=int) current_user_id = get_current_user_id(required=False) args["current_user_id"] = current_user_id users = get_users(args) return api_helpers.success_response(users)
def get_tracks_including_unlisted_route(): args = to_dict(request.args) if "filter_deleted" in request.args: args["filter_deleted"] = parse_bool_param(request.args.get("filter_deleted")) if "with_users" in request.args: args["with_users"] = parse_bool_param(request.args.get("with_users")) current_user_id = get_current_user_id(required=False) args["current_user_id"] = current_user_id identifiers = request.get_json()["tracks"] args["identifiers"] = identifiers tracks = get_tracks_including_unlisted(args) return api_helpers.success_response(tracks)
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_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_tracks(): tracks = [] db = get_db() with db.scoped_session() as session: # Create initial query base_query = session.query(Track) base_query = base_query.filter(Track.is_current == True) # Conditionally process an array of tracks if "id" in request.args: # Retrieve argument from flask request object # Ensures empty parameters are not processed track_id_str_list = request.args.getlist("id") track_id_list = [] try: track_id_list = [int(y) for y in track_id_str_list] # Update query with track_id list base_query = base_query.filter( Track.track_id.in_(track_id_list)) except ValueError as e: logger.error("Invalid value found in track id list", exc_info=True) raise e # Allow filtering of tracks by a certain creator if "user_id" in request.args: user_id = request.args.get("user_id", type=int) base_query = base_query.filter(Track.owner_id == user_id) if "min_block_number" in request.args: min_block_number = request.args.get("min_block_number", type=int) base_query = base_query.filter( Track.blocknumber >= min_block_number) whitelist_params = [ 'created_at', 'create_date', 'release_date', 'blocknumber', 'track_id' ] base_query = parse_sort_param(base_query, Track, whitelist_params) query_results = paginate_query(base_query).all() tracks = helpers.query_result_to_list(query_results) track_ids = list(map(lambda track: track["track_id"], tracks)) current_user_id = get_current_user_id(required=False) # bundle peripheral info into track results tracks = populate_track_metadata(session, track_ids, tracks, current_user_id) return api_helpers.success_response(tracks)
def get_savers_for_playlist_route(save_playlist_id): try: current_user_id = get_current_user_id(required=False) (limit, offset) = get_pagination_vars() args = { 'save_playlist_id': save_playlist_id, 'current_user_id': current_user_id, 'limit': limit, 'offset': offset } user_results = get_savers_for_playlist(args) return api_helpers.success_response(user_results) except exceptions.NotFoundError as e: return api_helpers.error_response(str(e), 404)
def get_savers_for_track_route(save_track_id): try: current_user_id = get_current_user_id(required=False) (limit, offset) = get_pagination_vars() args = { "save_track_id": save_track_id, "current_user_id": current_user_id, "limit": limit, "offset": offset, } user_results = get_savers_for_track(args) return api_helpers.success_response(user_results) except exceptions.NotFoundError as e: return api_helpers.error_response(str(e), 404)
def get_remixes_of_route(track_id): args = to_dict(request.args) args["track_id"] = track_id args["current_user_id"] = get_current_user_id(required=False) limit, offset = get_pagination_vars() args["limit"] = limit args["offset"] = offset if "with_users" in request.args: args["with_users"] = parse_bool_param(request.args.get("with_users")) try: remixes = get_remixes_of(args) return api_helpers.success_response(remixes) except exceptions.ArgumentError as e: return api_helpers.error_response(str(e), 400)
def get_tracks_route(): args = to_dict(request.args) if "id" in request.args: args["id"] = parse_id_array_param(request.args.getlist("id")) if "user_id" in request.args: args["user_id"] = request.args.get("user_id", type=int) if "filter_deleted" in request.args: args["filter_deleted"] = parse_bool_param(request.args.get("filter_deleted")) if "with_users" in request.args: args["with_users"] = parse_bool_param(request.args.get("with_users")) if "min_block_number" in request.args: args["min_block_number"] = request.args.get("min_block_number", type=int) current_user_id = get_current_user_id(required=False) args["current_user_id"] = current_user_id tracks = get_tracks(args) return api_helpers.success_response(tracks)
def get_trending_tracks(args): (limit, offset) = get_pagination_vars() current_user_id = get_current_user_id(required=False) db = get_db_read_replica() time = args.get('time') # Identity understands allTime as millennium. # TODO: Change this in https://github.com/AudiusProject/audius-protocol/pull/768/files query_time = time if time == 'allTime': query_time = 'millennium' with db.scoped_session() as session: trending_tracks = generate_trending(get_db_read_replica(), query_time, args.get('genre', None), limit, offset) track_scores = [ z(time, track) for track in trending_tracks['listen_counts'] ] sorted_track_scores = sorted(track_scores, key=lambda k: k['score'], reverse=True) track_ids = [track['track_id'] for track in sorted_track_scores] tracks = session.query(Track).filter( Track.is_current == True, Track.is_unlisted == False, Track.stem_of == None, Track.track_id.in_(track_ids)).all() tracks = helpers.query_result_to_list(tracks) tracks = populate_track_metadata(session, track_ids, tracks, current_user_id) tracks_map = {track['track_id']: track for track in tracks} # Re-sort the populated tracks b/c it loses sort order in sql query sorted_tracks = [tracks_map[track_id] for track_id in track_ids] if args.get("with_users", False): user_id_list = get_users_ids(sorted_tracks) users = get_users_by_id(session, user_id_list) for track in sorted_tracks: user = users[track['owner_id']] if user: track['user'] = user return sorted_tracks
def search_autocomplete(): args = to_dict(request.args) validation_error = validate_search_args(args) if validation_error: return validation_error current_user_id = get_current_user_id(required=False) limit, offset = get_pagination_vars() search_args = { "is_auto_complete": True, "kind": args.get("kind", "all"), "query": args.get("query"), "current_user_id": current_user_id, "with_users": False, "limit": limit, "offset": offset } resp = search(search_args) return api_helpers.success_response(resp)
def get_trending_tracks(args): (limit, offset) = get_pagination_vars() current_user_id = get_current_user_id(required=False) db = get_db_read_replica() time = args.get('time') with db.scoped_session() as session: trending_tracks = generate_trending(get_db_read_replica(), time, args.get('genre', None), limit, offset) track_scores = [ z(time, track) for track in trending_tracks['listen_counts'] ] sorted_track_scores = sorted(track_scores, key=lambda k: k['score'], reverse=True) track_ids = [track['track_id'] for track in sorted_track_scores] tracks = session.query(Track).filter( Track.is_current == True, Track.is_unlisted == False, Track.stem_of == None, Track.track_id.in_(track_ids)).all() tracks = helpers.query_result_to_list(tracks) tracks = populate_track_metadata(session, track_ids, tracks, current_user_id) tracks_map = {track['track_id']: track for track in tracks} # Re-sort the populated tracks b/c it loses sort order in sql query sorted_tracks = [tracks_map[track_id] for track_id in track_ids] if args.get("with_users", False): user_id_list = get_users_ids(sorted_tracks) users = get_users_by_id(session, user_id_list) for track in sorted_tracks: user = users[track['owner_id']] if user: track['user'] = user return sorted_tracks
def get_remix_track_parents(track_id, args): db = get_db_read_replica() with db.scoped_session() as session: base_query = (session.query(Track).join( Remix, and_(Remix.parent_track_id == Track.track_id, Remix.child_track_id == track_id)).filter( Track.is_current == True, Track.is_unlisted == False).order_by( desc(Track.created_at), desc(Track.track_id))) tracks = paginate_query(base_query).all() tracks = helpers.query_result_to_list(tracks) track_ids = list(map(lambda track: track["track_id"], tracks)) current_user_id = get_current_user_id(required=False) tracks = populate_track_metadata(session, track_ids, tracks, current_user_id) if args.get("with_users", False): add_users_to_tracks(session, tracks) return tracks
def get_feed_route(): args = to_dict(request.args) # filter should be one of ["all", "reposts", "original"] # empty filter value results in "all" if "filter" in request.args and request.args.get("filter") in [ "all", "repost", "original", ]: args["filter"] = args.get("filter") else: args["filter"] = "all" if "tracks_only" in request.args: args["tracks_only"] = parse_bool_param(request.args.get("tracks_only")) if "with_users" in request.args: args["with_users"] = parse_bool_param(request.args.get("with_users")) if "followee_user_id" in request.args: args["followee_user_ids"] = parse_id_array_param( request.args.getlist("followee_user_id")) user_id = get_current_user_id() args["user_id"] = user_id feed_results = get_feed(args) return api_helpers.success_response(feed_results)
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 playlist_search_query(session, searchStr, limit, offset, is_album, personalized, isAutocomplete): current_user_id = get_current_user_id(required=False) 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( 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 = ( session.query(Playlist) .filter( Playlist.is_current == True, Playlist.is_album == is_album, Playlist.playlist_id.in_(playlist_ids) ) .all() ) playlists = helpers.query_result_to_list(playlists) if isAutocomplete == True: # fetch users for playlists playlist_owner_ids = list(map(lambda playlist: playlist["playlist_owner_id"], playlists)) users = ( session.query(User) .filter( User.is_current == True, User.user_id.in_(playlist_owner_ids) ) .all() ) users = helpers.query_result_to_list(users) 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) for playlist_id in playlist_ids] return playlists
def user_search_query(session, searchStr, limit, offset, personalized, isAutocomplete): current_user_id = get_current_user_id(required=False) 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 isAutocomplete: # 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