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_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_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()
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 user_search_query(session, searchStr, limit, offset, personalized,
is_auto_complete, current_user_id):
if personalized and not current_user_id:
return []
res = sqlalchemy.text(f"""
select user_id from (
select user_id, (sum(score) + (:name_weight * similarity(coalesce(name, ''), query))) as total_score from (
select
d."user_id" as user_id, d."word" as word, similarity(d."word", :query) as score,
d."user_name" as name, :query as query
from "user_lexeme_dict" d
{
'inner join "follows" f on f.followee_user_id=d.user_id'
if personalized and current_user_id
else ""
}
where d."word" % :query
{
"and f.is_current=true and f.is_delete=false and f.follower_user_id=:current_user_id"
if personalized and current_user_id
else ""
}
) as results
group by user_id, name, query
) as results2
order by total_score desc, user_id asc
limit :limit
offset :offset;
""")
user_ids = session.execute(
res,
{
"query": searchStr,
"limit": limit,
"offset": offset,
"name_weight": userNameWeight,
"current_user_id": current_user_id
},
).fetchall()
# user_ids is list of tuples - simplify to 1-D list
user_ids = [i[0] for i in user_ids]
users = (session.query(User).filter(User.is_current == True,
User.user_id.in_(user_ids)).all())
users = helpers.query_result_to_list(users)
if not is_auto_complete:
# bundle peripheral info into user results
users = populate_user_metadata(session, user_ids, users,
current_user_id)
# preserve order from user_ids above
users = [
next(u for u in users if u["user_id"] == user_id)
for user_id in user_ids
]
return users
def get_top_followee_windowed(type, window, args):
if type != "track":
raise exceptions.ArgumentError(
"Invalid type provided, must be one of 'track'")
valid_windows = ["week", "month", "year"]
if not window or window not in valid_windows:
raise exceptions.ArgumentError(
f"Invalid window provided, must be one of {valid_windows}")
limit = args.get("limit", 25)
current_user_id = args.get("user_id")
db = get_db_read_replica()
with db.scoped_session() as session:
followee_user_ids = session.query(Follow.followee_user_id).filter(
Follow.follower_user_id == current_user_id,
Follow.is_current == True,
Follow.is_delete == False,
)
followee_user_ids_subquery = followee_user_ids.subquery()
# Queries for tracks joined against followed users and counts
tracks_query = (
session.query(Track, ).join(
followee_user_ids_subquery,
Track.owner_id ==
followee_user_ids_subquery.c.followee_user_id,
).join(AggregateTrack, Track.track_id == AggregateTrack.track_id).
filter(
Track.is_current == True,
Track.is_delete == False,
Track.is_unlisted == False,
Track.stem_of == None,
# Query only tracks created `window` time ago (week, month, etc.)
Track.created_at >= text(f"NOW() - interval '1 {window}'"),
).order_by(
desc(AggregateTrack.repost_count + AggregateTrack.save_count),
desc(Track.track_id),
).limit(limit))
tracks_query_results = tracks_query.all()
tracks = helpers.query_result_to_list(tracks_query_results)
track_ids = list(map(lambda track: track["track_id"], tracks))
# Bundle peripheral info into track results
tracks = populate_track_metadata(session, track_ids, tracks,
current_user_id)
if args.get("with_users", False):
user_id_list = get_users_ids(tracks)
users = get_users_by_id(session, user_id_list)
for track in tracks:
user = users[track["owner_id"]]
if user:
track["user"] = user
return tracks
def get_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(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_unpopulated_playlists(session, playlist_ids, filter_deleted=False):
"""
Fetches playlists by checking the redis cache first then
going to DB and writes to cache if not present
Args:
session: DB session
playlist_ids: array A list of playlist ids
Returns:
Array of playlists
"""
# Check the cached playlists
cached_playlists_results = get_cached_playlists(playlist_ids)
has_all_playlists_cached = cached_playlists_results.count(None) == 0
if has_all_playlists_cached:
if filter_deleted:
return list(
filter(lambda playlist: not playlist["is_delete"],
cached_playlists_results))
return cached_playlists_results
# Create a dict of cached playlists
cached_playlists = {}
for cached_playlist in cached_playlists_results:
if cached_playlist:
cached_playlists[cached_playlist["playlist_id"]] = cached_playlist
playlist_ids_to_fetch = filter(
lambda playlist_id: playlist_id not in cached_playlists, playlist_ids)
playlists_query = (session.query(Playlist).filter(
Playlist.is_current == True).filter(
Playlist.playlist_id.in_(playlist_ids_to_fetch)))
if filter_deleted:
playlists_query = playlists_query.filter(Playlist.is_delete == False)
playlists = playlists_query.all()
playlists = helpers.query_result_to_list(playlists)
queried_playlists = {
playlist["playlist_id"]: playlist
for playlist in playlists
}
# cache playlists for future use
set_playlists_in_cache(playlists)
playlists_response = []
for playlist_id in playlist_ids:
if playlist_id in cached_playlists:
if not filter_deleted or not cached_playlists[playlist_id][
"is_delete"]:
playlists_response.append(cached_playlists[playlist_id])
elif playlist_id in queried_playlists:
playlists_response.append(queried_playlists[playlist_id])
return playlists_response
def get_top_followee_saves(saveType, args):
if saveType != 'track':
raise exceptions.ArgumentError(
"Invalid type provided, must be one of 'track'")
limit = args.get('limit', 25)
current_user_id = get_current_user_id()
db = get_db_read_replica()
with db.scoped_session() as session:
# Construct a subquery of all followees
followee_user_ids = (session.query(Follow.followee_user_id).filter(
Follow.follower_user_id == current_user_id,
Follow.is_current == True, Follow.is_delete == False))
followee_user_ids_subquery = followee_user_ids.subquery()
# Construct a subquery of all saves from followees aggregated by id
save_count = (session.query(
Save.save_item_id,
func.count(Save.save_item_id).label(
response_name_constants.save_count)).join(
followee_user_ids_subquery, Save.user_id ==
followee_user_ids_subquery.c.followee_user_id).filter(
Save.is_current == True,
Save.is_delete == False,
Save.save_type == saveType,
).group_by(Save.save_item_id).order_by(
desc(response_name_constants.save_count)).limit(limit))
save_count_subquery = save_count.subquery()
# Query for tracks joined against followee save counts
tracks_query = (session.query(Track, ).join(
save_count_subquery,
Track.track_id == save_count_subquery.c.save_item_id).filter(
Track.is_current == True,
Track.is_delete == False,
Track.is_unlisted == False,
Track.stem_of == None,
))
tracks_query_results = tracks_query.all()
tracks = helpers.query_result_to_list(tracks_query_results)
track_ids = list(map(lambda track: track['track_id'], tracks))
# bundle peripheral info into track results
tracks = populate_track_metadata(session, track_ids, tracks,
current_user_id)
if args.get('with_users', False):
user_id_list = get_users_ids(tracks)
users = get_users_by_id(session, user_id_list)
for track in tracks:
user = users[track['owner_id']]
if user:
track['user'] = user
return tracks
def get_playlist_tracks(args):
playlists = []
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:
try:
playlist_id = args.get("playlist_id")
playlist = (
session
.query(Playlist)
.filter(
Playlist.is_current == True,
Playlist.playlist_id == playlist_id
)
.first()
)
if playlist is None:
return None
playlist_track_ids = [track_id['track']
for track_id in playlist.playlist_contents['track_ids']]
if limit and offset:
playlist_track_ids = playlist_track_ids[offset:offset+limit]
playlist_tracks = (
session
.query(Track)
.filter(
Track.is_current == True,
Track.track_id.in_(playlist_track_ids)
)
.all()
)
tracks = helpers.query_result_to_list(playlist_tracks)
tracks = populate_track_metadata(
session, playlist_track_ids, tracks, current_user_id)
if args.get("with_users", False):
add_users_to_tracks(session, tracks, current_user_id)
tracks_dict = {track['track_id']: track for track in tracks}
playlist_tracks = []
for track_id in playlist_track_ids:
playlist_tracks.append(tracks_dict[track_id])
return playlist_tracks
except sqlalchemy.orm.exc.NoResultFound:
pass
return playlists
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_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_user_listening_history(session: Session,
args: GetUserListeningHistoryArgs):
user_id = args["user_id"]
current_user_id = args["current_user_id"]
limit = args["limit"]
offset = args["offset"]
if user_id != current_user_id:
return []
listening_history_results = (session.query(
UserListeningHistory.listening_history).filter(
UserListeningHistory.user_id == current_user_id)).scalar()
if not listening_history_results:
return []
# add query pagination
listening_history_results = listening_history_results[offset:offset +
limit]
track_ids = []
listen_dates = []
for listen in listening_history_results:
track_ids.append(listen["track_id"])
listen_dates.append(listen["timestamp"])
track_results = (session.query(Track).filter(
Track.track_id.in_(track_ids))).all()
track_results_dict = {
track_result.track_id: track_result
for track_result in track_results
}
# sort tracks in listening history order
sorted_track_results = []
for track_id in track_ids:
if track_id in track_results_dict:
sorted_track_results.append(track_results_dict[track_id])
tracks = helpers.query_result_to_list(sorted_track_results)
# bundle peripheral info into track results
tracks = populate_track_metadata(session, track_ids, tracks,
current_user_id)
add_users_to_tracks(session, tracks, current_user_id)
for idx, track in enumerate(tracks):
track[response_name_constants.activity_timestamp] = listen_dates[idx]
return tracks
def get_users():
users = []
db = get_db()
with db.scoped_session() as session:
# Create initial query
base_query = session.query(User)
# Don't return the user if they have no wallet or handle (user creation did not finish properly on chain)
base_query = base_query.filter(User.is_current == True,
User.wallet != None,
User.handle != None)
# Process filters
if "is_creator" in request.args:
is_creator_flag = request.args.get("is_creator") == "true"
base_query = base_query.filter(User.is_creator == is_creator_flag)
if "wallet" in request.args:
wallet = request.args.get("wallet")
wallet = wallet.lower()
if len(wallet) == 42:
base_query = base_query.filter_by(wallet=wallet)
else:
logger.warning("Invalid wallet length")
if "handle" in request.args:
handle = request.args.get("handle").lower()
base_query = base_query.filter_by(handle_lc=handle)
# Conditionally process an array of users
if "id" in request.args:
user_id_str_list = request.args.getlist("id")
user_id_list = []
try:
user_id_list = [int(y) for y in user_id_str_list]
base_query = base_query.filter(User.user_id.in_(user_id_list))
except ValueError as e:
raise exceptions.ArgumentError(
"Invalid value found in user id list", e)
if "min_block_number" in request.args:
min_block_number = request.args.get("min_block_number", type=int)
base_query = base_query.filter(
User.blocknumber >= min_block_number)
users = paginate_query(base_query).all()
users = helpers.query_result_to_list(users)
user_ids = list(map(lambda user: user["user_id"], users))
current_user_id = get_current_user_id(required=False)
# bundle peripheral info into user results
users = populate_user_metadata(session, user_ids, users,
current_user_id)
return api_helpers.success_response(users)
def get_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_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_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_tracks():
tracks = []
db = get_db()
with db.scoped_session() as session:
# Create initial query
base_query = session.query(Track)
base_query = base_query.filter(Track.is_current == True)
# Conditionally process an array of tracks
if "id" in request.args:
# Retrieve argument from flask request object
# Ensures empty parameters are not processed
track_id_str_list = request.args.getlist("id")
track_id_list = []
try:
track_id_list = [int(y) for y in track_id_str_list]
# Update query with track_id list
base_query = base_query.filter(
Track.track_id.in_(track_id_list))
except ValueError as e:
logger.error("Invalid value found in track id list",
exc_info=True)
raise e
# Allow filtering of tracks by a certain creator
if "user_id" in request.args:
user_id = request.args.get("user_id", type=int)
base_query = base_query.filter(Track.owner_id == user_id)
if "min_block_number" in request.args:
min_block_number = request.args.get("min_block_number", type=int)
base_query = base_query.filter(
Track.blocknumber >= min_block_number)
whitelist_params = [
'created_at', 'create_date', 'release_date', 'blocknumber',
'track_id'
]
base_query = parse_sort_param(base_query, Track, whitelist_params)
query_results = paginate_query(base_query).all()
tracks = helpers.query_result_to_list(query_results)
track_ids = list(map(lambda track: track["track_id"], tracks))
current_user_id = get_current_user_id(required=False)
# bundle peripheral info into track results
tracks = populate_track_metadata(session, track_ids, tracks,
current_user_id)
return api_helpers.success_response(tracks)
def get_savers_for_track(save_track_id):
user_results = []
db = get_db_read_replica()
with db.scoped_session() as session:
# Ensure Track exists for provided save_track_id.
track_entry = session.query(Track).filter(
Track.track_id == save_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 saved 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 saved given track.
User.user_id.in_(
session.query(Save.user_id).filter(
Save.save_item_id == save_track_id,
Save.save_type == SaveType.track,
Save.is_current == True, Save.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 search_track_tags(session, args):
"""
Gets the tracks with a given tag
Args:
session: sqlalchemy db session instance
args: dict of arguments
args.search_str: string the tag search string
args.current_user_id: id | null The user id making the query
args.limit: number the query limit of number of returns tracks
args.offset: number the query offset for results
Returns:
list of tracks sorted by play count
"""
track_ids = (session.query(TagTrackUserMatview.track_id).filter(
TagTrackUserMatview.tag == args["search_str"].lower()).all())
# track_ids is list of tuples - simplify to 1-D list
track_ids = [i[0] for i in track_ids]
tracks = (session.query(Track).filter(
Track.is_current == True,
Track.is_delete == False,
Track.is_unlisted == False,
Track.stem_of == None,
Track.track_id.in_(track_ids),
).all())
tracks = helpers.query_result_to_list(tracks)
track_play_counts = get_track_play_counts(session, track_ids)
tracks = populate_track_metadata(session, track_ids, tracks,
args["current_user_id"])
for track in tracks:
track_id = track["track_id"]
track[response_name_constants.play_count] = track_play_counts.get(
track_id, 0)
play_count_sorted_tracks = sorted(
tracks,
key=lambda i: i[response_name_constants.play_count],
reverse=True)
# Add pagination parameters to track and user results
play_count_sorted_tracks = play_count_sorted_tracks[slice(
args["offset"], args["offset"] + args["limit"], 1)]
return play_count_sorted_tracks
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_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_users_by_id(session, user_ids):
user_query = session.query(User).filter(
User.is_current == True, User.wallet != None, User.handle != None)
users_results = user_query.filter(User.user_id.in_(user_ids)).all()
users = helpers.query_result_to_list(users_results)
current_user_id = get_current_user_id(required=False)
# bundle peripheral info into user results
populated_users = populate_user_metadata(
session, user_ids, users, current_user_id)
user_map = {}
for user in populated_users:
user_map[user['user_id']] = user
return user_map
def get_latest_sol_plays(limit=10) -> Optional[List[PlayDict]]:
db = get_db_read_replica()
# Cap max returned db entries
limit = min(limit, 100)
sol_plays = None
with db.scoped_session() as session:
base_query = (session.query(Play).order_by(desc(
Play.slot)).filter(Play.slot != None).limit(limit))
query_results = base_query.all()
if query_results:
sol_plays = helpers.query_result_to_list(query_results)
return sol_plays
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_stems_of(track_id):
db = get_db_read_replica()
stems = []
with db.scoped_session() as session:
parent_not_deleted_subquery = (session.query(
Track.is_delete).filter(Track.track_id == track_id).subquery())
stem_results = (session.query(Track).join(
Stem,
Stem.child_track_id == Track.track_id,
).filter(Track.is_current == True, Track.is_delete == False).filter(
Stem.parent_track_id == track_id).filter(
parent_not_deleted_subquery.c.is_delete == False).all())
stems = helpers.query_result_to_list(stem_results)
return stems
def get_trending_tracks(args):
(limit, offset) = get_pagination_vars()
current_user_id = get_current_user_id(required=False)
db = get_db_read_replica()
time = args.get('time')
# Identity understands allTime as millennium.
# TODO: Change this in https://github.com/AudiusProject/audius-protocol/pull/768/files
query_time = time
if time == 'allTime':
query_time = 'millennium'
with db.scoped_session() as session:
trending_tracks = generate_trending(get_db_read_replica(), query_time,
args.get('genre', None), limit,
offset)
track_scores = [
z(time, track) for track in trending_tracks['listen_counts']
]
sorted_track_scores = sorted(track_scores,
key=lambda k: k['score'],
reverse=True)
track_ids = [track['track_id'] for track in sorted_track_scores]
tracks = session.query(Track).filter(
Track.is_current == True, Track.is_unlisted == False,
Track.stem_of == None, Track.track_id.in_(track_ids)).all()
tracks = helpers.query_result_to_list(tracks)
tracks = populate_track_metadata(session, track_ids, tracks,
current_user_id)
tracks_map = {track['track_id']: track for track in tracks}
# Re-sort the populated tracks b/c it loses sort order in sql query
sorted_tracks = [tracks_map[track_id] for track_id in track_ids]
if args.get("with_users", False):
user_id_list = get_users_ids(sorted_tracks)
users = get_users_by_id(session, user_id_list)
for track in sorted_tracks:
user = users[track['owner_id']]
if user:
track['user'] = user
return sorted_tracks