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
