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_repost_feed_for_user(user_id, args): feed_results = {} db = get_db_read_replica() current_user_id = args.get("current_user_id") with db.scoped_session() as session: if "handle" in args: handle = args.get("handle") user_id = session.query( User.user_id).filter(User.handle_lc == handle.lower()).first() # query all reposts by user repost_query = (session.query(Repost).filter( Repost.is_current == True, Repost.is_delete == False, Repost.user_id == user_id).order_by(desc(Repost.created_at), desc(Repost.repost_item_id), desc(Repost.repost_type))) reposts = paginate_query(repost_query).all() # get track reposts from above track_reposts = [ r for r in reposts if r.repost_type == RepostType.track ] # get reposted track ids repost_track_ids = [r.repost_item_id for r in track_reposts] # get playlist reposts from above playlist_reposts = [ r for r in reposts if r.repost_type == RepostType.playlist or r.repost_type == RepostType.album ] # get reposted playlist ids repost_playlist_ids = [r.repost_item_id for r in playlist_reposts] track_reposts = helpers.query_result_to_list(track_reposts) playlist_reposts = helpers.query_result_to_list(playlist_reposts) # build track/playlist id --> repost dict from repost lists track_repost_dict = { repost["repost_item_id"]: repost for repost in track_reposts } playlist_repost_dict = { repost["repost_item_id"]: repost for repost in playlist_reposts } # query tracks for repost_track_ids track_query = (session.query(Track).filter( Track.is_current == True, Track.is_delete == False, Track.is_unlisted == False, Track.stem_of == None, Track.track_id.in_(repost_track_ids)).order_by( desc(Track.created_at))) tracks = track_query.all() tracks = helpers.query_result_to_list(tracks) # get track ids track_ids = [track["track_id"] for track in tracks] # query playlists for repost_playlist_ids playlist_query = (session.query(Playlist).filter( Playlist.is_current == True, Playlist.is_delete == False, Playlist.is_private == False, Playlist.playlist_id.in_(repost_playlist_ids)).order_by( desc(Playlist.created_at))) playlists = playlist_query.all() playlists = helpers.query_result_to_list(playlists) # get playlist ids playlist_ids = [playlist["playlist_id"] for playlist in playlists] # populate full metadata tracks = populate_track_metadata(session, track_ids, tracks, current_user_id) playlists = populate_playlist_metadata( session, playlist_ids, playlists, [RepostType.playlist, RepostType.album], [SaveType.playlist, SaveType.album], current_user_id) # add activity timestamps for track in tracks: track[response_name_constants. activity_timestamp] = track_repost_dict[ track["track_id"]]["created_at"] for playlist in playlists: playlist[response_name_constants.activity_timestamp] = \ playlist_repost_dict[playlist["playlist_id"]]["created_at"] unsorted_feed = tracks + playlists # sort feed by repost timestamp desc feed_results = sorted(unsorted_feed, key=lambda entry: entry[response_name_constants. activity_timestamp], reverse=True) if args.get("with_users", False): user_id_list = get_users_ids(feed_results) users = get_users_by_id(session, user_id_list) for result in feed_results: if 'playlist_owner_id' in result: user = users[result['playlist_owner_id']] if user: result['user'] = user elif 'owner_id' in result: user = users[result['owner_id']] if user: result['user'] = user return feed_results
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 = (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 is_auto_complete == 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 get_feed(): feed_results = [] db = get_db() # Current user - user for whom feed is being generated current_user_id = get_current_user_id() with db.scoped_session() as session: # Generate list of users followed by current user, i.e. '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).all()) followee_user_ids = [f[0] for f in followee_user_ids] # Query playlists posted by followees, sorted and paginated by created_at desc created_playlists_query = (session.query(Playlist).filter( Playlist.is_current == True, Playlist.is_private == False, Playlist.playlist_owner_id.in_(followee_user_ids)).order_by( desc(Playlist.created_at))) created_playlists = paginate_query(created_playlists_query, False).all() # get track ids for all tracks in playlists playlist_track_ids = set() for playlist in created_playlists: for track in playlist.playlist_contents["track_ids"]: playlist_track_ids.add(track["track"]) # get all track objects for track ids playlist_tracks = (session.query(Track).filter( Track.is_current == True, Track.track_id.in_(playlist_track_ids)).all()) playlist_tracks_dict = { track.track_id: track for track in playlist_tracks } # get all track ids that have same owner as playlist and created in "same action" # "same action": track created within [x time] before playlist creation tracks_to_dedupe = set() for playlist in created_playlists: for track_entry in playlist.playlist_contents["track_ids"]: track = playlist_tracks_dict.get(track_entry["track"]) if not track: return api_helpers.error_response( "Something caused the server to crash.") max_timedelta = datetime.timedelta( minutes=trackDedupeMaxMinutes) if (track.owner_id == playlist.playlist_owner_id) and \ (track.created_at <= playlist.created_at) and \ (playlist.created_at - track.created_at <= max_timedelta): tracks_to_dedupe.add(track.track_id) tracks_to_dedupe = list(tracks_to_dedupe) # Query tracks posted by followees, sorted & paginated by created_at desc # exclude tracks that were posted in "same action" as playlist created_tracks_query = (session.query(Track).filter( Track.is_current == True, Track.owner_id.in_(followee_user_ids), Track.track_id.notin_(tracks_to_dedupe)).order_by( desc(Track.created_at))) created_tracks = paginate_query(created_tracks_query, False).all() # extract created_track_ids and created_playlist_ids created_track_ids = [track.track_id for track in created_tracks] created_playlist_ids = [ playlist.playlist_id for playlist in created_playlists ] # query items reposted by followees, sorted by oldest followee repost of item; # paginated by most recent repost timestamp # exclude items also created by followees to guarantee order determinism repost_subquery = (session.query(Repost).filter( Repost.is_current == True, Repost.is_delete == False, Repost.user_id.in_(followee_user_ids), or_( and_(Repost.repost_type == RepostType.track, Repost.repost_item_id.notin_(created_track_ids)), and_(Repost.repost_type == RepostType.track, Repost.repost_item_id.notin_( created_playlist_ids)))).subquery()) repost_query = (session.query( repost_subquery.c.repost_item_id, repost_subquery.c.repost_type, func.min(repost_subquery.c.created_at).label("min_created_at") ).group_by( repost_subquery.c.repost_item_id, repost_subquery.c.repost_type).order_by("min_created_at desc")) followee_reposts = paginate_query(repost_query, False).all() # build dict of track id -> oldest followee repost timestamp from followee_reposts above track_repost_timestamp_dict = {} playlist_repost_timestamp_dict = {} for (repost_item_id, repost_type, oldest_followee_repost_timestamp) in followee_reposts: if repost_type == RepostType.track: track_repost_timestamp_dict[ repost_item_id] = oldest_followee_repost_timestamp elif repost_type in (RepostType.playlist, RepostType.album): playlist_repost_timestamp_dict[ repost_item_id] = oldest_followee_repost_timestamp # extract reposted_track_ids and reposted_playlist_ids reposted_track_ids = list(track_repost_timestamp_dict.keys()) reposted_playlist_ids = list(playlist_repost_timestamp_dict.keys()) # Query tracks reposted by followees, excluding tracks already fetched from above reposted_tracks = (session.query(Track).filter( Track.is_current == True, Track.track_id.in_(reposted_track_ids), Track.track_id.notin_(created_track_ids)).order_by( desc(Track.created_at)).all()) # Query playlists reposted by followees, excluding playlists already fetched from above reposted_playlists = (session.query(Playlist).filter( Playlist.is_current == True, Playlist.is_private == False, Playlist.playlist_id.in_(reposted_playlist_ids), Playlist.playlist_id.notin_(created_playlist_ids)).all()) # Combine created + reposted track and playlist lists tracks = helpers.query_result_to_list(created_tracks + reposted_tracks) playlists = helpers.query_result_to_list(created_playlists + reposted_playlists) # define top level feed activity_timestamp to enable sorting # activity_timestamp: created_at if item created by followee, else reposted_at for track in tracks: if track["owner_id"] in followee_user_ids: track[response_name_constants. activity_timestamp] = track["created_at"] else: track[response_name_constants. activity_timestamp] = track_repost_timestamp_dict[ track["track_id"]] for playlist in playlists: if playlist["playlist_owner_id"] in followee_user_ids: playlist[response_name_constants. activity_timestamp] = playlist["created_at"] else: playlist[response_name_constants.activity_timestamp] = \ playlist_repost_timestamp_dict[playlist["playlist_id"]] # bundle peripheral info into track and playlist objects track_ids = list(map(lambda track: track["track_id"], tracks)) playlist_ids = list( map(lambda playlist: playlist["playlist_id"], playlists)) tracks = populate_track_metadata(session, track_ids, tracks, current_user_id) playlists = populate_playlist_metadata( session, playlist_ids, playlists, [RepostType.playlist, RepostType.album], [SaveType.playlist, SaveType.album], current_user_id) # build combined feed of tracks and playlists unsorted_feed = tracks + playlists # sort feed based on activity_timestamp sorted_feed = sorted(unsorted_feed, key=lambda entry: entry[response_name_constants. activity_timestamp], reverse=True) # truncate feed to requested limit (limit, _) = get_pagination_vars() feed_results = sorted_feed[0:limit] return api_helpers.success_response(feed_results)
def get_trending_playlists(args, strategy): """Returns Trending Playlists. Checks Redis cache for unpopulated playlists.""" db = get_db_read_replica() with db.scoped_session() as session: current_user_id = args.get("current_user_id", None) with_tracks = args.get("with_tracks", False) time = args.get("time") limit, offset = args.get("limit"), args.get("offset") key = make_trending_cache_key(time, strategy.version) # Get unpopulated playlists, # cached if it exists. (playlists, playlist_ids) = use_redis_cache( key, None, make_get_unpopulated_playlists(session, time, strategy)) # Apply limit + offset early to reduce the amount of # population work we have to do if limit is not None and offset is not None: playlists = playlists[offset:limit + offset] playlist_ids = playlist_ids[offset:limit + offset] # Populate playlist metadata playlists = populate_playlist_metadata( session, playlist_ids, playlists, [RepostType.playlist, RepostType.album], [SaveType.playlist, SaveType.album], current_user_id) trimmed_track_ids = None for playlist in playlists: playlist["track_count"] = len(playlist["tracks"]) playlist["tracks"] = playlist["tracks"][:PLAYLIST_TRACKS_LIMIT] # Trim track_ids, which ultimately become added_timestamps # and need to match the tracks. trimmed_track_ids = { track["track_id"] for track in playlist["tracks"] } playlist_track_ids = playlist["playlist_contents"]["track_ids"] playlist_track_ids = list( filter(lambda track_id: track_id["track"] in trimmed_track_ids, playlist_track_ids)) playlist["playlist_contents"]["track_ids"] = playlist_track_ids playlists_map = { playlist['playlist_id']: playlist for playlist in playlists } if with_tracks: # populate track metadata tracks = [] for playlist in playlists: playlist_tracks = playlist["tracks"] tracks.extend(playlist_tracks) track_ids = [track["track_id"] for track in tracks] populated_tracks = populate_track_metadata(session, track_ids, tracks, current_user_id) # Add users if necessary add_users_to_tracks(session, populated_tracks, current_user_id) # Re-associate tracks with playlists # track_id -> populated_track populated_track_map = { track["track_id"]: track for track in populated_tracks } for playlist in playlists_map.values(): for i in range(len(playlist["tracks"])): track_id = playlist["tracks"][i]["track_id"] populated = populated_track_map[track_id] playlist["tracks"][i] = populated playlist["tracks"] = list(map(extend_track, playlist["tracks"])) # re-sort playlists to original order, because populate_playlist_metadata # unsorts. sorted_playlists = [ playlists_map[playlist_id] for playlist_id in playlist_ids ] # Add users to playlists user_id_list = get_users_ids(sorted_playlists) users = get_users_by_id(session, user_id_list, current_user_id) for playlist in sorted_playlists: user = users[playlist['playlist_owner_id']] if user: playlist['user'] = user # Extend the playlists playlists = list(map(extend_playlist, playlists)) return sorted_playlists
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 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": max(limit, MIN_SEARCH_LEXEME_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) # 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 playlist in playlists: playlist["user"] = users_dict[playlist["playlist_owner_id"]] playlists = populate_playlist_repost_counts(session, playlist_ids, playlists, [repost_type]) 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] # Sort playlists by extra criteria for "best match" playlists.sort(key=cmp_to_key(compare_playlists)) return playlists[0:limit]
def get_playlists(args): playlists = [] current_user_id = args.get("current_user_id") db = get_db_read_replica() with db.scoped_session() as session: def get_unpopulated_playlists(): 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 args: playlist_id_list = args.get("playlist_id") try: 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 args: user_id = args.get("user_id") # user id filter if the optional query param is passed in playlist_query = playlist_query.filter( Playlist.playlist_owner_id == user_id) # If no current_user_id, never show hidden playlists if not current_user_id: playlist_query = playlist_query.filter( Playlist.is_private == False) # Filter out deletes unless we're fetching explicitly by id if "playlist_id" not in args: playlist_query = playlist_query.filter( Playlist.is_delete == False) playlist_query = playlist_query.order_by(desc(Playlist.created_at)) playlists = paginate_query(playlist_query).all() playlists = helpers.query_result_to_list(playlists) # if we passed in a current_user_id, filter out all privte playlists where # the owner_id doesn't match the current_user_id if current_user_id: playlists = list( filter( lambda playlist: (not playlist["is_private"]) or playlist["playlist_owner_id"] == current_user_id, playlists)) # retrieve playlist ids list playlist_ids = list( map(lambda playlist: playlist["playlist_id"], playlists)) return (playlists, playlist_ids) try: # Get unpopulated playlists, either via # redis cache or via get_unpopulated_playlists key = make_cache_key(args) (playlists, playlist_ids) = use_redis_cache( key, UNPOPULATED_PLAYLIST_CACHE_DURATION_SEC, get_unpopulated_playlists) # 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) if args.get("with_users", False): user_id_list = get_users_ids(playlists) users = get_users_by_id(session, user_id_list, current_user_id) for playlist in playlists: user = users[playlist['playlist_owner_id']] if user: playlist['user'] = user except sqlalchemy.orm.exc.NoResultFound: pass return playlists
def test_populate_playlist_metadata(app): """Tests that populate_playlist_metadata works after aggregate_user refresh""" with app.app_context(): db = get_db() test_entities = { 'playlists': [{ "playlist_id": 1, "playlist_owner_id": 1 }, { "playlist_id": 2, "playlist_owner_id": 1 }, { "playlist_id": 3, "playlist_owner_id": 2 }, { "playlist_id": 4, "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' }], 'reposts': [{ "repost_item_id": 1, "repost_type": 'playlist', "user_id": 2 }, { "repost_item_id": 1, "repost_type": 'playlist', "user_id": 3 }, { "repost_item_id": 2, "repost_type": 'playlist', "user_id": 1 }], 'saves': [{ "save_item_id": 1, "save_type": 'playlist', "user_id": 2 }, { "save_item_id": 1, "save_type": 'playlist', "user_id": 3 }, { "save_item_id": 3, "save_type": 'playlist', "user_id": 2 }, { "save_item_id": 3, "save_type": 'playlist', "user_id": 1 }], 'follows': [{ "follower_user_id": 1, "followee_user_id": 2 }, { "follower_user_id": 1, "followee_user_id": 3 }] } populate_mock_db(db, test_entities) with db.scoped_session() as session: session.execute("REFRESH MATERIALIZED VIEW aggregate_playlist") playlist_ids = [1, 2, 3, 4] playlists = [{ "playlist_id": 1, "playlist_contents": { "track_ids": [] } }, { "playlist_id": 2, "playlist_contents": { "track_ids": [] } }, { "playlist_id": 3, "playlist_contents": { "track_ids": [] } }, { "playlist_id": 4, "playlist_contents": { "track_ids": [] } }] playlists = populate_playlist_metadata( session, playlist_ids, playlists, [RepostType.playlist, RepostType.album], [SaveType.playlist, SaveType.album], None) assert len(playlists) == 4 assert playlists[0]['playlist_id'] == 1 assert playlists[0][response_name_constants.repost_count] == 2 assert playlists[0][response_name_constants.save_count] == 2 assert playlists[0][response_name_constants.total_play_count] == 0 assert playlists[1]['playlist_id'] == 2 assert playlists[1][response_name_constants.repost_count] == 1 assert playlists[1][response_name_constants.save_count] == 0 assert playlists[1][response_name_constants.total_play_count] == 0 assert playlists[2]['playlist_id'] == 3 assert playlists[2][response_name_constants.repost_count] == 0 assert playlists[2][response_name_constants.save_count] == 2 assert playlists[2][response_name_constants.total_play_count] == 0 curr_playlist_ids = [1, 2, 3] curr_playlists = [{ "playlist_id": 1, "playlist_contents": { "track_ids": [] } }, { "playlist_id": 2, "playlist_contents": { "track_ids": [] } }, { "playlist_id": 3, "playlist_contents": { "track_ids": [] } }] playlists = populate_playlist_metadata( session, curr_playlist_ids, curr_playlists, [RepostType.playlist, RepostType.album], [SaveType.playlist, SaveType.album], 1) assert len(playlists) == 3 assert playlists[0]['playlist_id'] == 1 repost_user_ids = [ repost['user_id'] for repost in playlists[0][ response_name_constants.followee_reposts] ] repost_user_ids.sort() assert repost_user_ids == [2, 3] save_user_ids = [ save['user_id'] for save in playlists[0][response_name_constants.followee_saves] ] save_user_ids.sort() assert save_user_ids == [2, 3] assert playlists[0][ response_name_constants.has_current_user_reposted] == False assert playlists[0][ response_name_constants.has_current_user_saved] == False assert playlists[1]['playlist_id'] == 2 assert playlists[1][response_name_constants.followee_reposts] == [] assert playlists[1][response_name_constants.followee_saves] == [] assert playlists[1][ response_name_constants.has_current_user_reposted] == True assert playlists[1][ response_name_constants.has_current_user_saved] == False assert playlists[2]['playlist_id'] == 3 assert playlists[2][response_name_constants.followee_reposts] == [] save_user_ids = [ save['user_id'] for save in playlists[2][response_name_constants.followee_saves] ] save_user_ids.sort() assert save_user_ids == [2] assert playlists[2][ response_name_constants.has_current_user_reposted] == False assert playlists[2][ response_name_constants.has_current_user_saved] == True
def get_top_playlists(kind, args): current_user_id = get_current_user_id(required=False) # Argument parsing and checking if kind not in ("playlist", "album"): raise exceptions.ArgumentError( "Invalid kind provided, must be one of 'playlist', 'album'" ) limit = args.get("limit", 16) mood = args.get("mood", None) if "filter" in args: query_filter = args.get("filter") if query_filter != "followees": raise exceptions.ArgumentError( "Invalid filter provided, must be one of 'followees'" ) if query_filter == "followees": if not current_user_id: raise exceptions.ArgumentError( "User id required to query for followees" ) else: query_filter = None db = get_db_read_replica() with db.scoped_session() as session: # If filtering by followees, set the playlist view to be only playlists from # users that the current user follows. if query_filter == "followees": playlists_to_query = create_followee_playlists_subquery( session, current_user_id ) # Otherwise, just query all playlists else: playlists_to_query = session.query(Playlist).subquery() # Create a decayed-score view of the playlists playlist_query = ( session.query( playlists_to_query, (AggregatePlaylist.repost_count + AggregatePlaylist.save_count).label( "count" ), decayed_score( AggregatePlaylist.repost_count + AggregatePlaylist.save_count, playlists_to_query.c.created_at, ).label("score"), ) .select_from(playlists_to_query) .join( AggregatePlaylist, AggregatePlaylist.playlist_id == playlists_to_query.c.playlist_id, ) .filter( playlists_to_query.c.is_current == True, playlists_to_query.c.is_delete == False, playlists_to_query.c.is_private == False, playlists_to_query.c.is_album == (kind == "album"), ) ) # Filter by mood (no-op if no mood is provided) playlist_query = filter_to_playlist_mood( session, mood, playlist_query, playlists_to_query ) # Order and limit the playlist query by score playlist_query = playlist_query.order_by( desc("score"), desc(playlists_to_query.c.playlist_id) ).limit(limit) playlist_results = playlist_query.all() # Unzip query results into playlists and scores score_map = {} # playlist_id : score playlists = [] if playlist_results: for result in playlist_results: # The playlist is the portion of the query result before repost_count and score playlist = result[0:-2] score = result[-1] # Convert the playlist row tuple into a dictionary keyed by column name playlist = helpers.tuple_to_model_dictionary(playlist, Playlist) score_map[playlist["playlist_id"]] = score playlists.append(playlist) playlist_ids = list(map(lambda playlist: playlist["playlist_id"], playlists)) # 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, ) # Add scores into the response for playlist in playlists: playlist["score"] = score_map[playlist["playlist_id"]] if args.get("with_users", False): user_id_list = get_users_ids(playlists) users = get_users_by_id(session, user_id_list) for playlist in playlists: user = users[playlist["playlist_owner_id"]] if user: playlist["user"] = user return playlists
def _get_repost_feed_for_user(session: Session, user_id: int, args: GetRepostFeedForUserArgs): feed_results = [] current_user_id = args.get("current_user_id") limit = args.get("limit") offset = args.get("offset") if "handle" in args: handle = args.get("handle") or "" user_id = cast( int, session.query( User.user_id).filter(User.handle_lc == handle.lower()).first(), ) # Query all reposts by a user. # Outerjoin both tracks and playlists to collect both # so that a single limit/offset pagination does what we intend when tracks or playlists # are deleted. repost_query = ( session.query(Repost, Track, Playlist).outerjoin( Track, and_( Repost.repost_item_id == Track.track_id, Repost.repost_type == "track", Track.is_current == True, Track.is_delete == False, Track.is_unlisted == False, Track.stem_of == None, ), ).outerjoin( Playlist, and_( Repost.repost_item_id == Playlist.playlist_id, or_(Repost.repost_type == "playlist", Repost.repost_type == "album"), Playlist.is_current == True, Playlist.is_delete == False, Playlist.is_private == False, ), ).filter( Repost.is_current == True, Repost.is_delete == False, Repost.user_id == user_id, # Drop rows that have no join found for either track or playlist or_(Track.track_id != None, Playlist.playlist_id != None), ).order_by( desc(Repost.created_at), desc(Repost.repost_item_id), desc(Repost.repost_type), )) reposts = add_query_pagination(repost_query, limit, offset).all() # get track reposts from above track_reposts = [r[0] for r in reposts if r[1] is not None] track_reposts = helpers.query_result_to_list(track_reposts) # get playlist reposts from above playlist_reposts = [r[0] for r in reposts if r[2] is not None] playlist_reposts = helpers.query_result_to_list(playlist_reposts) # build track/playlist id --> repost dict from repost lists track_repost_dict = { repost["repost_item_id"]: repost for repost in track_reposts } playlist_repost_dict = { repost["repost_item_id"]: repost for repost in playlist_reposts } tracks = helpers.query_result_to_list( filter(None, [repost[1] for repost in reposts])) playlists = helpers.query_result_to_list( filter(None, [repost[2] for repost in reposts])) # get track ids track_ids = [track["track_id"] for track in tracks] # get playlist ids playlist_ids = [playlist["playlist_id"] for playlist in playlists] # populate full metadata tracks = populate_track_metadata(session, track_ids, tracks, current_user_id) playlists = populate_playlist_metadata( session, playlist_ids, playlists, [RepostType.playlist, RepostType.album], [SaveType.playlist, SaveType.album], current_user_id, ) # add activity timestamps for track in tracks: track[response_name_constants.activity_timestamp] = track_repost_dict[ track["track_id"]]["created_at"] for playlist in playlists: playlist[ response_name_constants.activity_timestamp] = playlist_repost_dict[ playlist["playlist_id"]]["created_at"] unsorted_feed = tracks + playlists # sort feed by repost timestamp desc feed_results = sorted( unsorted_feed, key=lambda entry: entry[response_name_constants.activity_timestamp], reverse=True, ) if args.get("with_users", False): user_id_list = get_users_ids(feed_results) users = get_users_by_id(session, user_id_list) for result in feed_results: if "playlist_owner_id" in result: user = users[result["playlist_owner_id"]] if user: result["user"] = user elif "owner_id" in result: user = users[result["owner_id"]] if user: result["user"] = user return feed_results
def get_feed_sql(args): feed_results = [] db = get_db_read_replica() feed_filter = args.get("filter") # Allow for fetching only tracks tracks_only = args.get("tracks_only", False) followee_user_ids = args.get("followee_user_ids", []) # Current user - user for whom feed is being generated current_user_id = args.get("user_id") with db.scoped_session() as session: # Generate list of users followed by current user, i.e. 'followees' if not followee_user_ids: 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, ) .all() ) followee_user_ids = [f[0] for f in followee_user_ids] # Fetch followee creations if requested if feed_filter in ["original", "all"]: if not tracks_only: # Query playlists posted by followees, sorted and paginated by created_at desc created_playlists_query = ( session.query(Playlist) .filter( Playlist.is_current == True, Playlist.is_delete == False, Playlist.is_private == False, Playlist.playlist_owner_id.in_(followee_user_ids), ) .order_by(desc(Playlist.created_at)) ) created_playlists = paginate_query(created_playlists_query, False).all() # get track ids for all tracks in playlists playlist_track_ids = set() for playlist in created_playlists: for track in playlist.playlist_contents["track_ids"]: playlist_track_ids.add(track["track"]) # get all track objects for track ids playlist_tracks = get_unpopulated_tracks(session, playlist_track_ids) playlist_tracks_dict = { track["track_id"]: track for track in playlist_tracks } # get all track ids that have same owner as playlist and created in "same action" # "same action": track created within [x time] before playlist creation tracks_to_dedupe = set() for playlist in created_playlists: for track_entry in playlist.playlist_contents["track_ids"]: track = playlist_tracks_dict.get(track_entry["track"]) if not track: continue max_timedelta = datetime.timedelta( minutes=trackDedupeMaxMinutes ) if ( (track["owner_id"] == playlist.playlist_owner_id) and (track["created_at"] <= playlist.created_at) and ( playlist.created_at - track["created_at"] <= max_timedelta ) ): tracks_to_dedupe.add(track["track_id"]) tracks_to_dedupe = list(tracks_to_dedupe) else: # No playlists to consider tracks_to_dedupe = [] created_playlists = [] # Query tracks posted by followees, sorted & paginated by created_at desc # exclude tracks that were posted in "same action" as playlist created_tracks_query = ( session.query(Track) .filter( Track.is_current == True, Track.is_delete == False, Track.is_unlisted == False, Track.stem_of == None, Track.owner_id.in_(followee_user_ids), Track.track_id.notin_(tracks_to_dedupe), ) .order_by(desc(Track.created_at)) ) created_tracks = paginate_query(created_tracks_query, False).all() # extract created_track_ids and created_playlist_ids created_track_ids = [track.track_id for track in created_tracks] created_playlist_ids = [ playlist.playlist_id for playlist in created_playlists ] # Fetch followee reposts if requested if feed_filter in ["repost", "all"]: # query items reposted by followees, sorted by oldest followee repost of item; # paginated by most recent repost timestamp repost_subquery = session.query(Repost).filter( Repost.is_current == True, Repost.is_delete == False, Repost.user_id.in_(followee_user_ids), ) # exclude items also created by followees to guarantee order determinism, in case of "all" filter if feed_filter == "all": repost_subquery = repost_subquery.filter( or_( and_( Repost.repost_type == RepostType.track, Repost.repost_item_id.notin_(created_track_ids), ), and_( Repost.repost_type != RepostType.track, Repost.repost_item_id.notin_(created_playlist_ids), ), ) ) repost_subquery = repost_subquery.subquery() repost_query = ( session.query( repost_subquery.c.repost_item_id, repost_subquery.c.repost_type, func.min(repost_subquery.c.created_at).label("min_created_at"), ) .group_by( repost_subquery.c.repost_item_id, repost_subquery.c.repost_type ) .order_by(desc("min_created_at")) ) followee_reposts = paginate_query(repost_query, False).all() # build dict of track_id / playlist_id -> oldest followee repost timestamp from followee_reposts above track_repost_timestamp_dict = {} playlist_repost_timestamp_dict = {} for ( repost_item_id, repost_type, oldest_followee_repost_timestamp, ) in followee_reposts: if repost_type == RepostType.track: track_repost_timestamp_dict[ repost_item_id ] = oldest_followee_repost_timestamp elif repost_type in (RepostType.playlist, RepostType.album): playlist_repost_timestamp_dict[ repost_item_id ] = oldest_followee_repost_timestamp # extract reposted_track_ids and reposted_playlist_ids reposted_track_ids = list(track_repost_timestamp_dict.keys()) reposted_playlist_ids = list(playlist_repost_timestamp_dict.keys()) # Query tracks reposted by followees reposted_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_(reposted_track_ids), ) # exclude tracks already fetched from above, in case of "all" filter if feed_filter == "all": reposted_tracks = reposted_tracks.filter( Track.track_id.notin_(created_track_ids) ) reposted_tracks = reposted_tracks.order_by(desc(Track.created_at)).all() if not tracks_only: # Query playlists reposted by followees, excluding playlists already fetched from above reposted_playlists = session.query(Playlist).filter( Playlist.is_current == True, Playlist.is_delete == False, Playlist.is_private == False, Playlist.playlist_id.in_(reposted_playlist_ids), ) # exclude playlists already fetched from above, in case of "all" filter if feed_filter == "all": reposted_playlists = reposted_playlists.filter( Playlist.playlist_id.notin_(created_playlist_ids) ) reposted_playlists = reposted_playlists.order_by( desc(Playlist.created_at) ).all() else: reposted_playlists = [] if feed_filter == "original": tracks_to_process = created_tracks playlists_to_process = created_playlists elif feed_filter == "repost": tracks_to_process = reposted_tracks playlists_to_process = reposted_playlists else: tracks_to_process = created_tracks + reposted_tracks playlists_to_process = created_playlists + reposted_playlists tracks = helpers.query_result_to_list(tracks_to_process) playlists = helpers.query_result_to_list(playlists_to_process) # define top level feed activity_timestamp to enable sorting # activity_timestamp: created_at if item created by followee, else reposted_at for track in tracks: if track["owner_id"] in followee_user_ids: track[response_name_constants.activity_timestamp] = track["created_at"] else: track[ response_name_constants.activity_timestamp ] = track_repost_timestamp_dict[track["track_id"]] for playlist in playlists: if playlist["playlist_owner_id"] in followee_user_ids: playlist[response_name_constants.activity_timestamp] = playlist[ "created_at" ] else: playlist[ response_name_constants.activity_timestamp ] = playlist_repost_timestamp_dict[playlist["playlist_id"]] # bundle peripheral info into track and playlist objects track_ids = list(map(lambda track: track["track_id"], tracks)) playlist_ids = list(map(lambda playlist: playlist["playlist_id"], playlists)) tracks = populate_track_metadata(session, track_ids, tracks, current_user_id) playlists = populate_playlist_metadata( session, playlist_ids, playlists, [RepostType.playlist, RepostType.album], [SaveType.playlist, SaveType.album], current_user_id, ) # build combined feed of tracks and playlists unsorted_feed = tracks + playlists # sort feed based on activity_timestamp sorted_feed = sorted( unsorted_feed, key=lambda entry: entry[response_name_constants.activity_timestamp], reverse=True, ) # truncate feed to requested limit (limit, _) = get_pagination_vars() feed_results = sorted_feed[0:limit] if "with_users" in args and args.get("with_users") != False: user_id_list = get_users_ids(feed_results) users = get_users_by_id(session, user_id_list) for result in feed_results: if "playlist_owner_id" in result: user = users[result["playlist_owner_id"]] if user: result["user"] = user elif "owner_id" in result: user = users[result["owner_id"]] if user: result["user"] = user return feed_results
def get_playlists(args): playlists = [] current_user_id = get_current_user_id(required=False) filter_out_private_playlists = True db = get_db_read_replica() 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 args: playlist_id_list = args.get("playlist_id") try: 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 args: user_id = args.get("user_id") # 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 ) # Filter out deletes unless we're fetching explicitly by id if "playlist_id" not in args: playlist_query = playlist_query.filter( Playlist.is_delete == 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 ) if args.get("with_users", False): user_id_list = get_users_ids(playlists) users = get_users_by_id(session, user_id_list) for playlist in playlists: user = users[playlist['playlist_owner_id']] if user: playlist['user'] = user except sqlalchemy.orm.exc.NoResultFound: pass return playlists