def get_playlist_repost_intersection_users(repost_playlist_id,
follower_user_id):
users = []
db = get_db()
with db.scoped_session() as session:
# ensure playlist_id exists
playlist_entry = session.query(Playlist).filter(
Playlist.playlist_id == repost_playlist_id,
Playlist.is_current == True).first()
if playlist_entry is None:
return api_helpers.error_response(
'Resource not found for provided playlist id', 404)
query = (session.query(User).filter(
User.is_current == True, User.is_ready == True,
User.user_id.in_(
session.query(Repost.user_id).filter(
Repost.repost_item_id == repost_playlist_id,
Repost.repost_type != RepostType.track,
Repost.is_current == True,
Repost.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)
return api_helpers.success_response(users)
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_playlist_repost_intersection_users(repost_playlist_id,
follower_user_id):
users = []
db = get_db_read_replica()
with db.scoped_session() as session:
# ensure playlist_id exists
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")
query = session.query(User).filter(
User.is_current == True,
User.user_id.in_(
session.query(Repost.user_id).filter(
Repost.repost_item_id == repost_playlist_id,
Repost.repost_type != RepostType.track,
Repost.is_current == True,
Repost.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)
return users
def get_follow_intersection_users(followee_user_id, follower_user_id):
users = []
db = get_db()
with db.scoped_session() as session:
query = (session.query(User).filter(
User.is_current == True, User.is_ready == True,
User.user_id.in_(
session.query(Follow.follower_user_id).filter(
Follow.followee_user_id == followee_user_id,
Follow.is_current == True,
Follow.is_delete == False).intersect(
session.query(Follow.followee_user_id).filter(
Follow.follower_user_id == follower_user_id,
Follow.is_current == True,
Follow.is_delete == False)))))
users = paginate_query(query).all()
users = helpers.query_result_to_list(users)
user_ids = [user[response_name_constants.user_id] for user in users]
current_user_id = get_current_user_id(required=False)
# bundle peripheral info into user results
users = populate_user_metadata(session, user_ids, users,
current_user_id)
# order by follower_count desc
users.sort(
key=lambda user: user[response_name_constants.follower_count],
reverse=True)
return api_helpers.success_response(users)
def 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_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_users_and_ids():
can_use_shared_cache = (
"id" in args and
"is_creator" not in args and
"wallet" not in args and
"min_block_number" not in args and
"handle" not in args
)
if can_use_shared_cache:
users = get_unpopulated_users(session, args.get("id"))
ids = list(map(lambda user: user["user_id"], users))
return (users, ids)
# Create initial query
base_query = session.query(User)
# Don't return the user if they have no wallet or handle (user creation did not finish properly on chain)
base_query = base_query.filter(
User.is_current == True, User.wallet != None, User.handle != None)
# Process filters
if "is_creator" in args:
base_query = base_query.filter(User.is_creator == args.get("is_creator"))
if "wallet" in args:
wallet = args.get("wallet")
wallet = wallet.lower()
if len(wallet) == 42:
base_query = base_query.filter_by(wallet=wallet)
base_query = base_query.order_by(asc(User.created_at))
else:
logger.warning("Invalid wallet length")
if "handle" in args:
handle = args.get("handle").lower()
base_query = base_query.filter_by(handle_lc=handle)
# Conditionally process an array of users
if "id" in args:
user_id_list = args.get("id")
try:
base_query = base_query.filter(User.user_id.in_(user_id_list))
except ValueError as e:
raise exceptions.ArgumentError(
"Invalid value found in user id list", e)
if "min_block_number" in args:
base_query = base_query.filter(
User.blocknumber >= args.get("min_block_number")
)
users = paginate_query(base_query).all()
users = helpers.query_result_to_list(users)
user_ids = list(map(lambda user: user["user_id"], users))
return (users, user_ids)
def get_reposters_for_track(repost_track_id):
user_results = []
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 = paginate_query(query).all()
# Fix format to return only Users objects with follower_count field.
if user_results:
users, follower_counts = zip(*user_results)
user_results = helpers.query_result_to_list(users)
for i, user in enumerate(user_results):
user[response_name_constants.
follower_count] = follower_counts[i]
return user_results
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_ursm_cnodes(owner_wallet):
db = get_db_read_replica()
with db.scoped_session() as session:
base_query = (session.query(URSMContentNode).filter(
URSMContentNode.is_current == True, ).order_by(
desc(URSMContentNode.cnode_sp_id)))
if owner_wallet is not None:
base_query = base_query.filter(
URSMContentNode.owner_wallet == owner_wallet)
query_results = paginate_query(base_query).all()
ursm_content_nodes = helpers.query_result_to_list(query_results)
return ursm_content_nodes
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_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)
def get_reposters_for_track(repost_track_id):
user_results = []
db = get_db()
with db.scoped_session() as session:
# ensure track_id exists
track_entry = session.query(Track).filter(
Track.track_id == repost_track_id,
Track.is_current == True
).first()
if track_entry is None:
return api_helpers.error_response('Resource not found for provided track id', 404)
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)
.order_by(response_name_constants.follower_count + " desc")
)
follower_count_subquery = paginate_query(follower_count_subquery).subquery()
query = (
session.query(User, follower_count_subquery.c.follower_count)
.join(Repost, User.user_id == Repost.user_id)
.outerjoin(follower_count_subquery, follower_count_subquery.c.followee_user_id == User.user_id)
.filter(
User.is_current == True,
User.is_ready == True,
Repost.repost_item_id == repost_track_id,
Repost.repost_type == RepostType.track,
Repost.is_current == True,
Repost.is_delete == False
)
)
user_results = query.all()
if user_results:
users, follower_counts = zip(*user_results)
user_results = helpers.query_result_to_list(users)
for i, user in enumerate(user_results):
user[response_name_constants.follower_count] = follower_counts[i] or 0
return api_helpers.success_response(user_results)
def get_saves(save_type, user_id):
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 = []
db = get_db_read_replica()
with db.scoped_session() as session:
query = session.query(Save).filter(
Save.user_id == 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 save_results
def get_unpopulated_track():
base_query = session.query(Track)
filter_cond = []
# Create filter conditions as a list of `and` clauses
for i in identifiers:
filter_cond.append(
and_(Track.is_current == True, Track.track_id == i["id"]))
# Pass array of `and` clauses into an `or` clause as destructured *args
base_query = base_query.filter(or_(*filter_cond))
# Allow filtering of deletes
# Note: There is no standard for boolean url parameters, and any value (including 'false')
# will be evaluated as true, so an explicit check is made for true
if "filter_deleted" in args:
filter_deleted = args.get("filter_deleted")
if filter_deleted:
base_query = base_query.filter(Track.is_delete == False)
# Perform the query
# TODO: pagination is broken with unlisted tracks
query_results = paginate_query(base_query).all()
tracks = helpers.query_result_to_list(query_results)
# Mapping of track_id -> track object from request;
# used to check route_id when iterating through identifiers
identifiers_map = {track["id"]: track for track in identifiers}
# If the track is unlisted and the generated route_id does not match the route_id in db,
# filter track out from response
def filter_fn(track):
input_track = identifiers_map[track["track_id"]]
route_id = helpers.create_track_route_id(
input_track["url_title"], input_track["handle"])
return not track["is_unlisted"] or track["route_id"] == route_id
tracks = list(filter(filter_fn, tracks))
track_ids = list(map(lambda track: track["track_id"], tracks))
return (tracks, track_ids)
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_tips(session: Session, args: GetTipsArgs):
UserTipAlias = aliased(UserTip)
query: Query = session.query(UserTipAlias)
has_pagination = False # Keeps track if we already paginated
if args.get("tx_signatures"):
query = query.filter(UserTipAlias.signature.in_(args["tx_signatures"]))
if args.get("receiver_min_followers", 0) > 0:
query = query.join(
AggregateUser,
AggregateUser.user_id == UserTipAlias.receiver_user_id).filter(
AggregateUser.follower_count >= args["receiver_min_followers"])
if args.get("receiver_is_verified", False):
query = query.join(
User, User.user_id == UserTipAlias.receiver_user_id).filter(
User.is_current == True, User.is_verified == True)
if args.get("min_slot", 0) > 0:
query = query.filter(UserTipAlias.slot >= args["min_slot"])
if args.get("max_slot", 0) > 0:
query = query.filter(UserTipAlias.slot <= args["max_slot"])
if args.get("unique_by"):
if args["unique_by"] == "sender":
distinct_inner = (query.order_by(
UserTipAlias.sender_user_id.asc(),
UserTipAlias.slot.desc()).distinct(
UserTipAlias.sender_user_id).subquery())
UserTipAlias = aliased(UserTip,
distinct_inner,
name="user_tips_uniqued")
query = session.query(UserTipAlias)
elif args["unique_by"] == "receiver":
distinct_inner = (query.order_by(
UserTipAlias.receiver_user_id.asc(),
UserTipAlias.slot.desc()).distinct(
UserTipAlias.receiver_user_id).subquery())
UserTipAlias = aliased(UserTip,
distinct_inner,
name="user_tips_uniqued")
query = session.query(UserTipAlias)
if args.get("user_id"):
# We have to get the other users that this user follows for three potential uses:
# 1) To filter tips to recipients the user follows (if necessary)
# 2) To filter tips to senders the user follows (if necessary)
# 3) To get the followees of the current user that have also tipped the receiver
followees_query = (session.query(Follow.followee_user_id).filter(
Follow.is_current == True,
Follow.is_delete == False,
Follow.follower_user_id == args["user_id"],
).cte("followees"))
# First, filter the senders/receivers as necessary
if args.get("current_user_follows"):
FolloweesSender = aliased(followees_query,
name="followees_for_sender")
FolloweesReceiver = aliased(followees_query,
name="followees_for_receiver")
if args["current_user_follows"] == "receiver":
query = query.join(
FolloweesReceiver,
UserTipAlias.receiver_user_id ==
FolloweesReceiver.c.followee_user_id,
)
elif args["current_user_follows"] == "sender":
query = query.join(
FolloweesSender,
UserTipAlias.receiver_user_id ==
FolloweesSender.c.followee_user_id,
)
elif args["current_user_follows"] == "sender_or_receiver":
query = query.outerjoin(
FolloweesSender,
UserTipAlias.sender_user_id ==
FolloweesSender.c.followee_user_id,
)
query = query.outerjoin(
FolloweesReceiver,
UserTipAlias.receiver_user_id ==
FolloweesReceiver.c.followee_user_id,
)
query = query.filter(
or_(
FolloweesSender.c.followee_user_id != None,
FolloweesReceiver.c.followee_user_id != None,
))
# Order and paginate before adding follower filters/aggregates
query = query.order_by(UserTipAlias.slot.desc())
query = paginate_query(query)
# Get the tips for the user as a subquery
# because now we need to get the other users that tipped that receiver
# and joining on this already paginated/limited result will be much faster
Tips = query.cte("tips")
FolloweesAggregate = aliased(followees_query,
name="followees_for_aggregate")
# Get all of the followees joined on their aggregate user tips first
# rather than joining each on the tips separately to help with speed
FolloweeTippers = (session.query(
AggregateUserTips.sender_user_id,
AggregateUserTips.receiver_user_id,
FolloweesAggregate.c.followee_user_id,
).select_from(FolloweesAggregate).outerjoin(
AggregateUserTips,
AggregateUserTips.sender_user_id ==
FolloweesAggregate.c.followee_user_id,
).cte("followee_tippers"))
# Now we have the tips listed multiple times, one for each followee sender.
# So group by the tip and aggregate up the followee sender IDs into a list
query = (session.query(
UserTip, func.array_agg(FolloweeTippers.c.sender_user_id)
).select_entity_from(Tips).outerjoin(
FolloweeTippers,
FolloweeTippers.c.receiver_user_id == Tips.c.receiver_user_id,
).group_by(Tips))
has_pagination = True
query = query.order_by(UserTipAlias.slot.desc())
if not has_pagination:
query = paginate_query(query)
tips_results: List[UserTip] = query.all()
return tips_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
def get_tracks(args):
tracks = []
db = get_db_read_replica()
with db.scoped_session() as session:
# 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)
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)
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_repost_feed_for_user(user_id):
feed_results = {}
db = get_db()
with db.scoped_session() as session:
# 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)))
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.track_id.in_(repost_track_ids)).order_by(
desc(Track.created_at)))
tracks = paginate_query(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_private == False,
Playlist.playlist_id.in_(repost_playlist_ids)).order_by(
desc(Playlist.created_at)))
playlists = paginate_query(playlist_query).all()
playlists = helpers.query_result_to_list(playlists)
# get playlist ids
playlist_ids = [playlist["playlist_id"] for playlist in playlists]
# get repost counts by track and playlist IDs
repost_counts = get_repost_counts(session, False, True,
track_ids + playlist_ids, None)
track_repost_counts = {
repost_item_id: repost_count
for (repost_item_id, repost_count, repost_type) in repost_counts
if repost_type == RepostType.track
}
playlist_repost_counts = {
repost_item_id: repost_count
for (repost_item_id, repost_count, repost_type) in repost_counts
if repost_type in (RepostType.playlist, RepostType.album)
}
# get save counts for tracks and playlists
save_counts = get_save_counts(session, False, True,
track_ids + playlist_ids, None)
track_save_counts = {
save_item_id: save_count
for (save_item_id, save_count, save_type) in save_counts
if save_type == SaveType.track
}
playlist_save_counts = {
save_item_id: save_count
for (save_item_id, save_count, save_type) in save_counts
if save_type in (SaveType.playlist, SaveType.album)
}
current_user_id = get_current_user_id(required=False)
requested_user_is_current_user = False
user_reposted_track_ids = {}
user_reposted_playlist_ids = {}
user_saved_track_dict = {}
user_saved_playlist_dict = {}
followees_track_repost_dict = {}
followees_playlist_repost_dict = {}
if current_user_id:
# if current user = user_id, skip current_user_reposted queries and default to true
if current_user_id == user_id:
requested_user_is_current_user = True
else:
user_reposted_query = (session.query(
Repost.repost_item_id, Repost.repost_type).filter(
Repost.is_current == True, Repost.is_delete == False,
Repost.user_id == current_user_id,
or_(Repost.repost_item_id.in_(track_ids),
Repost.repost_item_id.in_(playlist_ids))).all())
# generate dictionary of track id --> current user reposted status
user_reposted_track_ids = {
r[0]: True
for r in user_reposted_query if r[1] == RepostType.track
}
# generate dictionary of playlist id --> current user reposted status
user_reposted_playlist_ids = {
r[0]: True
for r in user_reposted_query
if r[1] == RepostType.album or r[1] == RepostType.playlist
}
# build dict of tracks and playlists that current user has saved
# - query saves by current user from relevant tracks/playlists
user_saved_query = (session.query(
Save.save_item_id, Save.save_type).filter(
Save.is_current == True, Save.is_delete == False,
Save.user_id == current_user_id,
or_(Save.save_item_id.in_(track_ids),
Save.save_item_id.in_(playlist_ids))).all())
# - build dict of track id --> current user save status
user_saved_track_dict = {
save[0]: True
for save in user_saved_query if save[1] == SaveType.track
}
# - build dict of playlist id --> current user save status
user_saved_playlist_dict = {
save[0]: True
for save in user_saved_query
if save[1] == SaveType.playlist or save[1] == SaveType.album
}
# query current user's 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 all followees' reposts
followee_repost_query = (session.query(Repost).filter(
Repost.is_current == True, Repost.is_delete == False,
Repost.user_id.in_(followee_user_ids),
or_(Repost.repost_item_id.in_(repost_track_ids),
Repost.repost_item_id.in_(repost_playlist_ids))).order_by(
desc(Repost.created_at)))
followee_reposts = paginate_query(followee_repost_query).all()
followee_reposts = helpers.query_result_to_list(followee_reposts)
# build dict of track id --> reposts from followee track reposts
for repost in followee_reposts:
if repost["repost_type"] == RepostType.track:
if repost[
"repost_item_id"] not in followees_track_repost_dict:
followees_track_repost_dict[
repost["repost_item_id"]] = []
followees_track_repost_dict[
repost["repost_item_id"]].append(repost)
# build dict of playlist id --> reposts from followee playlist reposts
for repost in followee_reposts:
if (repost["repost_type"] == RepostType.playlist
or repost["repost_type"] == RepostType.album):
if repost[
"repost_item_id"] not in followees_playlist_repost_dict:
followees_playlist_repost_dict[
repost["repost_item_id"]] = []
followees_playlist_repost_dict[
repost["repost_item_id"]].append(repost)
# populate metadata for track entries
for track in tracks:
track[response_name_constants.
repost_count] = track_repost_counts.get(
track["track_id"], 0)
track[response_name_constants.save_count] = track_save_counts.get(
track["track_id"], 0)
track[response_name_constants.has_current_user_reposted] = (
True if requested_user_is_current_user else
user_reposted_track_ids.get(track["track_id"], False))
track[response_name_constants.
has_current_user_saved] = user_saved_track_dict.get(
track["track_id"], False)
track[response_name_constants.
followee_reposts] = followees_track_repost_dict.get(
track["track_id"], [])
track[response_name_constants.
activity_timestamp] = track_repost_dict[
track["track_id"]]["created_at"]
for playlist in playlists:
playlist[response_name_constants.
repost_count] = playlist_repost_counts.get(
playlist["playlist_id"], 0)
playlist[
response_name_constants.save_count] = playlist_save_counts.get(
playlist["playlist_id"], 0)
playlist[response_name_constants.has_current_user_reposted] = (
True if requested_user_is_current_user else
user_reposted_playlist_ids.get(playlist["playlist_id"], False))
playlist[response_name_constants.has_current_user_saved] = \
user_saved_playlist_dict.get(playlist["playlist_id"], False)
playlist[response_name_constants.followee_reposts] = \
followees_playlist_repost_dict.get(playlist["playlist_id"], [])
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)
return api_helpers.success_response(feed_results)
def get_tracks_and_ids():
if "handle" in args:
handle = args.get("handle")
user_id = session.query(User.user_id).filter(
User.handle_lc == handle.lower()).first()
args["user_id"] = user_id
can_use_shared_cache = ("id" in args
and not "min_block_number" in args
and not "sort" in args
and not "user_id" in args)
if can_use_shared_cache:
should_filter_deleted = args.get("filter_deleted", False)
tracks = get_unpopulated_tracks(session, args["id"],
should_filter_deleted)
track_ids = list(map(lambda track: track["track_id"], tracks))
return (tracks, track_ids)
# 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(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 = paginate_query(base_query).all()
tracks = helpers.query_result_to_list(query_results)
track_ids = list(map(lambda track: track["track_id"], tracks))
return (tracks, track_ids)
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_top_genre_users(args):
genres = []
if "genre" in args:
genres = args.get("genre")
# If the with_users url arg is provided, then populate the user metadata else return user ids
with_users = args.get("with_users", False)
db = get_db_read_replica()
with db.scoped_session() as session:
with_genres = len(genres) != 0
# Associate the user w/ a genre by counting the total # of tracks per genre
# taking the genre w/ the most tracks (using genre name as secondary sort)
user_genre_count_query = (session.query(
User.user_id.label('user_id'), Track.genre.label('genre'),
func.row_number().over(
partition_by=User.user_id,
order_by=(desc(func.count(Track.genre)), asc(
Track.genre))).label("row_number")).join(
Track, Track.owner_id == User.user_id).filter(
User.is_current == True, User.is_creator == True,
Track.is_unlisted == False, Track.stem_of == None,
Track.is_current == True,
Track.is_delete == False).group_by(
User.user_id, Track.genre).order_by(
desc(func.count(Track.genre)),
asc(Track.genre)))
user_genre_count_query = user_genre_count_query.subquery(
'user_genre_count_query')
user_genre_query = (session.query(
user_genre_count_query.c.user_id.label('user_id'),
user_genre_count_query.c.genre.label('genre'),
).filter(user_genre_count_query.c.row_number == 1).subquery(
'user_genre_query'))
# Using the subquery of user to associated genre,
# filter by the requested genres and
# sort by user follower count
user_genre_followers_query = (
session.query(user_genre_query.c.user_id.label('user_id')).join(
Follow,
Follow.followee_user_id == user_genre_query.c.user_id).filter(
Follow.is_current == True,
Follow.is_delete == False).group_by(
user_genre_query.c.user_id,
user_genre_query.c.genre).order_by(
# desc('follower_count')
desc(func.count(Follow.follower_user_id))))
if with_genres:
user_genre_followers_query = user_genre_followers_query.filter(
user_genre_query.c.genre.in_(genres))
# If the with_users flag is not set, respond with the user_ids
users = paginate_query(user_genre_followers_query).all()
user_ids = list(map(lambda user: user[0], users))
# If the with_users flag is used, retrieve the user metadata
if with_users:
user_query = session.query(User).filter(User.user_id.in_(user_ids),
User.is_current == True)
users = user_query.all()
users = helpers.query_result_to_list(users)
queried_user_ids = list(map(lambda user: user["user_id"], users))
users = populate_user_metadata(session, queried_user_ids, users,
None)
# Sort the users so that it's in the same order as the previous query
user_map = {user['user_id']: user for user in users}
users = [user_map[user_id] for user_id in user_ids]
return {'users': users}
return {'user_ids': user_ids}
def 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 get_remixes_of(track_id, args):
db = get_db_read_replica()
with db.scoped_session() as session:
# Fetch the parent track to get the track's owner id
parent_track = session.query(Track).filter(
Track.is_current == True,
Track.track_id == track_id
).first()
if parent_track == None:
raise exceptions.ArgumentError("Invalid track_id provided")
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) = paginate_query(base_query, True, True)
tracks = tracks.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': tracks, 'count': count}
