def get_reposters_for_track(args): user_results = [] current_user_id = args.get('current_user_id') repost_track_id = args.get('repost_track_id') limit = args.get('limit') offset = args.get('offset') db = get_db_read_replica() with db.scoped_session() as session: # Ensure Track exists for provided repost_track_id. track_entry = session.query(Track).filter( Track.track_id == repost_track_id, Track.is_current == True).first() if track_entry is None: raise exceptions.NotFoundError( 'Resource not found for provided track id') # Subquery to get all (user_id, follower_count) entries from Follows table. follower_count_subquery = (session.query( Follow.followee_user_id, func.count(Follow.followee_user_id).label( response_name_constants.follower_count)).filter( Follow.is_current == True, Follow.is_delete == False).group_by( Follow.followee_user_id).subquery()) # Get all Users that reposted track, ordered by follower_count desc & paginated. query = ( session.query( User, # Replace null values from left outer join with 0 to ensure sort works correctly. (func.coalesce(follower_count_subquery.c.follower_count, 0) ).label(response_name_constants.follower_count)) # Left outer join to associate users with their follower count. .outerjoin( follower_count_subquery, follower_count_subquery.c.followee_user_id == User.user_id).filter( User.is_current == True, # Only select users that reposted given track. User.user_id.in_( session.query(Repost.user_id).filter( Repost.repost_item_id == repost_track_id, Repost.repost_type == RepostType.track, Repost.is_current == True, Repost.is_delete == False) )).order_by(desc(response_name_constants.follower_count))) user_results = add_query_pagination(query, limit, offset).all() # Fix format to return only Users objects with follower_count field. if user_results: users, _ = zip(*user_results) user_results = helpers.query_result_to_list(users) # bundle peripheral info into user results user_ids = [user['user_id'] for user in user_results] user_results = populate_user_metadata(session, user_ids, user_results, current_user_id) return user_results
def _get_tracks(session, args): # Create initial query base_query = session.query(Track) base_query = base_query.filter(Track.is_current == True, Track.is_unlisted == False, Track.stem_of == None) # Conditionally process an array of tracks if "id" in args: track_id_list = args.get("id") try: # 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 args: user_id = args.get("user_id") base_query = base_query.filter(Track.owner_id == user_id) # Allow filtering of deletes if "filter_deleted" in args: filter_deleted = args.get("filter_deleted") if filter_deleted: base_query = base_query.filter(Track.is_delete == False) if "min_block_number" in args: min_block_number = args.get("min_block_number") base_query = base_query.filter(Track.blocknumber >= min_block_number) if "sort" in args: if args["sort"] == "date": base_query = base_query.order_by( coalesce( # This func is defined in alembic migrations func.to_date_safe(Track.release_date, 'Dy Mon DD YYYY HH24:MI:SS'), Track.created_at).desc(), Track.track_id.desc()) elif args["sort"] == "plays": base_query = base_query.join( AggregatePlays, AggregatePlays.play_item_id == Track.track_id).order_by( AggregatePlays.count.desc()) else: whitelist_params = [ 'created_at', 'create_date', 'release_date', 'blocknumber', 'track_id' ] base_query = parse_sort_param(base_query, Track, whitelist_params) query_results = add_query_pagination(base_query, args["limit"], args["offset"]) tracks = helpers.query_result_to_list(query_results.all()) return tracks
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_reposters_for_playlist(args): user_results = [] current_user_id = args.get("current_user_id") repost_playlist_id = args.get("repost_playlist_id") limit = args.get("limit") offset = args.get("offset") db = get_db_read_replica() with db.scoped_session() as session: # Ensure Playlist exists for provided repost_playlist_id. playlist_entry = (session.query(Playlist).filter( Playlist.playlist_id == repost_playlist_id, Playlist.is_current == True).first()) if playlist_entry is None: raise exceptions.NotFoundError( "Resource not found for provided playlist id") # Get all Users that reposted Playlist, ordered by follower_count desc & paginated. query = ( session.query( User, # Replace null values from left outer join with 0 to ensure sort works correctly. (func.coalesce(AggregateUser.follower_count, 0) ).label(response_name_constants.follower_count), ) # Left outer join to associate users with their follower count. .outerjoin(AggregateUser, AggregateUser.user_id == User.user_id). filter( User.is_current == True, # Only select users that reposted given playlist. User.user_id.in_( session.query(Repost.user_id).filter( Repost.repost_item_id == repost_playlist_id, # Select Reposts for Playlists and Albums (i.e. not Tracks). Repost.repost_type != RepostType.track, Repost.is_current == True, Repost.is_delete == False, )), ).order_by(desc(response_name_constants.follower_count))) user_results = add_query_pagination(query, limit, offset).all() # Fix format to return only Users objects with follower_count field. if user_results: users, _ = zip(*user_results) user_results = helpers.query_result_to_list(users) # bundle peripheral info into user results user_ids = [user["user_id"] for user in user_results] user_results = populate_user_metadata(session, user_ids, user_results, current_user_id) return user_results
def get_unpopulated_remix_parents(): 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 = add_query_pagination(base_query, limit, offset).all() tracks = helpers.query_result_to_list(tracks) track_ids = list(map(lambda track: track["track_id"], tracks)) return (tracks, track_ids)
def get_save_tracks(args): user_id = args.get("user_id") current_user_id = args.get("current_user_id") limit = args.get("limit") offset = args.get("offset") filter_deleted = args.get("filter_deleted") db = get_db_read_replica() with db.scoped_session() as session: base_query = (session.query(Track, Save.created_at).join( Save, Save.save_item_id == Track.track_id).filter( Track.is_current == True, Save.user_id == user_id, Save.is_current == True, Save.is_delete == False, Save.save_type == SaveType.track, )) # Allow filtering of deletes if filter_deleted: base_query = base_query.filter(Track.is_delete == False) base_query = base_query.order_by(Save.created_at.desc(), Track.track_id.desc()) query_results = add_query_pagination(base_query, limit, offset).all() if not query_results: return [] tracks, save_dates = zip(*query_results) tracks = helpers.query_result_to_list(tracks) 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, current_user_id) for track in tracks: user = users[track["owner_id"]] if user: track["user"] = user for idx, track in enumerate(tracks): track[response_name_constants.activity_timestamp] = save_dates[idx] return tracks
def get_unpopulated_remixes(): # Fetch the parent track to get the track's owner id parent_track_res = get_unpopulated_tracks(session, [track_id], False, False) if not parent_track_res or parent_track_res[0] is None: raise exceptions.ArgumentError("Invalid track_id provided") parent_track = parent_track_res[0] track_owner_id = parent_track['owner_id'] # Create subquery for save counts for sorting save_count_subquery = create_save_count_subquery( session, SaveType.track) # Create subquery for repost counts for sorting repost_count_subquery = create_repost_count_subquery( session, RepostType.track) # Get the 'children' remix tracks # Use the track owner id to fetch reposted/saved tracks returned first base_query = ( session.query( Track ) .join( Remix, and_( Remix.child_track_id == Track.track_id, Remix.parent_track_id == track_id ) ).outerjoin( Save, and_( Save.save_item_id == Track.track_id, Save.save_type == SaveType.track, Save.is_current == True, Save.is_delete == False, Save.user_id == track_owner_id ) ).outerjoin( Repost, and_( Repost.repost_item_id == Track.track_id, Repost.user_id == track_owner_id, Repost.repost_type == RepostType.track, Repost.is_current == True, Repost.is_delete == False ) ).outerjoin( repost_count_subquery, repost_count_subquery.c['id'] == Track.track_id ).outerjoin( save_count_subquery, save_count_subquery.c['id'] == Track.track_id ) .filter( Track.is_current == True, Track.is_delete == False, Track.is_unlisted == False ) # 1. Co-signed tracks ordered by save + repost count # 2. Other tracks ordered by save + repost count .order_by( desc( # If there is no "co-sign" for the track (no repost or save from the parent owner), # defer to secondary sort case( [ (and_(Repost.created_at == None, Save.created_at == None), 0), ], else_=( func.coalesce(repost_count_subquery.c.repost_count, 0) + \ func.coalesce(save_count_subquery.c.save_count, 0) ) ) ), # Order by saves + reposts desc( func.coalesce(repost_count_subquery.c.repost_count, 0) + \ func.coalesce(save_count_subquery.c.save_count, 0) ), # Ties, pick latest track id desc(Track.track_id) ) ) (tracks, count) = add_query_pagination(base_query, limit, offset, True, True) tracks = tracks.all() tracks = helpers.query_result_to_list(tracks) track_ids = list(map(lambda track: track["track_id"], tracks)) return (tracks, track_ids, count)
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_tracks(session, args): # Create initial query base_query = session.query(Track) base_query = base_query.filter(Track.is_current == True, Track.stem_of == None) if "routes" in args: routes = args.get("routes") # Join the routes table base_query = base_query.join(TrackRoute, TrackRoute.track_id == Track.track_id) # Add the query conditions for each route filter_cond = [] for route in routes: filter_cond.append( and_( TrackRoute.slug == route["slug"], TrackRoute.owner_id == route["owner_id"], )) base_query = base_query.filter(or_(*filter_cond)) else: # Only return unlisted tracks if either # - above case, routes are present (direct links to hidden tracks) # - the user is authenticated as the owner is_authed_user = ("user_id" in args and "authed_user_id" in args and args.get("user_id") == args.get("authed_user_id")) if not is_authed_user: base_query = base_query.filter(Track.is_unlisted == False) # Conditionally process an array of tracks if "id" in args: track_id_list = args.get("id") try: # 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 args: user_id = args.get("user_id") base_query = base_query.filter(Track.owner_id == user_id) # Allow filtering of deletes if "filter_deleted" in args: filter_deleted = args.get("filter_deleted") if filter_deleted: base_query = base_query.filter(Track.is_delete == False) if "min_block_number" in args: min_block_number = args.get("min_block_number") base_query = base_query.filter(Track.blocknumber >= min_block_number) if "sort" in args: if args["sort"] == "date": base_query = base_query.order_by( coalesce( # This func is defined in alembic migrations func.to_date_safe(Track.release_date, "Dy Mon DD YYYY HH24:MI:SS"), Track.created_at, ).desc(), Track.track_id.desc(), ) elif args["sort"] == "plays": base_query = base_query.join( AggregatePlays, AggregatePlays.play_item_id == Track.track_id).order_by( AggregatePlays.count.desc()) else: whitelist_params = [ "created_at", "create_date", "release_date", "blocknumber", "track_id", ] base_query = parse_sort_param(base_query, Track, whitelist_params) query_results = add_query_pagination(base_query, args["limit"], args["offset"]) tracks = helpers.query_result_to_list(query_results.all()) 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