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:
rows = session.execute(
sql,
{
"followee_user_id": followee_user_id,
"limit": limit,
"offset": offset
},
)
user_ids = [r[0] for r in rows]
# 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)
return 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 = get_unpopulated_users(session, user_ids)
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_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_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_users_by_id(session, user_ids, current_user_id=None):
users = get_unpopulated_users(session, user_ids)
if not current_user_id:
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_tips(args: GetTipsArgs) -> List[TipResult]:
db = get_db_read_replica()
with db.scoped_session() as session:
results: Union[List[Tuple[UserTip, List[str]]],
List[UserTip]] = _get_tips(session, args)
tips_results: List[Tuple[UserTip, List[str]]] = []
# Wrap in tuple for consistency
if results and isinstance(results[0], UserTip):
tips_results = [(cast(UserTip, tip), []) for tip in results]
else:
# MyPy doesn't seem smart enough to figure this out, help it with a cast
tips_results = cast(List[Tuple[UserTip, List[str]]], results)
# Collect user IDs and fetch users
user_ids = set()
for result in tips_results:
user_ids.add(result[0].sender_user_id)
user_ids.add(result[0].receiver_user_id)
users = get_unpopulated_users(session, user_ids)
users = populate_user_metadata(
session, user_ids, users,
args["user_id"] if "user_id" in args else None)
users_map = {}
for user in users:
users_map[user["user_id"]] = user
# Not using model_to_dictionary() here because TypedDict complains about dynamic keys
tips: List[TipResult] = [{
"amount":
result[0].amount,
"sender":
users_map[result[0].sender_user_id],
"receiver":
users_map[result[0].receiver_user_id],
"followee_supporters":
list(filter(
lambda id: id is not None,
result[1],
)),
"slot":
result[0].slot,
"created_at":
result[0].created_at,
"tx_signature":
result[0].signature,
} for result in tips_results]
return tips
def track_search_query(
session,
search_str,
limit,
offset,
is_auto_complete,
current_user_id,
only_downloadable,
):
res = sqlalchemy.text(
# pylint: disable=C0301
f"""
select track_id, b.balance, b.associated_wallets_balance, u.is_saved from (
select distinct on (owner_id) track_id, owner_id, is_saved, total_score
from (
select track_id, owner_id, is_saved,
(
(:similarity_weight * sum(score)) +
(:title_weight * similarity(coalesce(title, ''), query)) +
(:user_name_weight * similarity(coalesce(user_name, ''), query)) +
(:repost_weight * log(case when (repost_count = 0) then 1 else repost_count end)) +
(case when (lower(query) = coalesce(title, '')) then :title_match_boost else 0 end) +
(case when (lower(query) = handle) then :handle_match_boost else 0 end) +
(case when (lower(query) = user_name) then :user_name_match_boost else 0 end)
{
'+ (case when (is_saved) then :current_user_saved_match_boost else 0 end)'
if current_user_id
else ""
}
) as total_score
from (
select
d."track_id" as track_id, d."word" as word, similarity(d."word", :query) as score,
d."track_title" as title, :query as query, d."user_name" as user_name, d."handle" as handle,
d."repost_count" as repost_count, d."owner_id" as owner_id
{
',s."user_id" is not null as is_saved'
if current_user_id
else ", false as is_saved"
}
from "track_lexeme_dict" d
{
"left outer join (select save_item_id, user_id from saves where saves.save_type = 'track' " +
"and saves.is_current = true " +
"and saves.is_delete = false and saves.user_id = :current_user_id )" +
" s on s.save_item_id = d.track_id"
if current_user_id
else ""
}
{
'inner join "tracks" t on t.track_id = d.track_id'
if only_downloadable
else ""
}
where (d."word" % lower(:query) or d."handle" = lower(:query) or d."user_name" % lower(:query))
{
"and (t.download->>'is_downloadable')::boolean is True"
if only_downloadable
else ""
}
) as results
group by track_id, title, query, user_name, handle, repost_count, owner_id, is_saved
) as results2
order by owner_id, total_score desc
) as u left join user_balances b on u.owner_id = b.user_id
order by total_score desc
limit :limit
offset :offset;
"""
)
track_result_proxy = session.execute(
res,
params={
"query": search_str,
"limit": limit,
"offset": offset,
"title_weight": search_title_weight,
"repost_weight": search_repost_weight,
"similarity_weight": search_similarity_weight,
"current_user_id": current_user_id,
"user_name_weight": search_user_name_weight,
"title_match_boost": search_title_exact_match_boost,
"handle_match_boost": search_handle_exact_match_boost,
"user_name_match_boost": search_user_name_exact_match_boost,
"current_user_saved_match_boost": current_user_saved_match_boost,
},
)
track_data = track_result_proxy.fetchall()
track_cols = track_result_proxy.keys()
# track_ids is list of tuples - simplify to 1-D list
track_ids = [track[track_cols.index("track_id")] for track in track_data]
saved_tracks = {
track[0] for track in track_data if track[track_cols.index("is_saved")]
}
tracks = get_unpopulated_tracks(session, track_ids, True)
# TODO: Populate track metadata should be sped up to be able to be
# used in search autocomplete as that'll give us better results.
if is_auto_complete:
# fetch users for tracks
track_owner_ids = list(map(lambda track: track["owner_id"], tracks))
users = get_unpopulated_users(session, track_owner_ids)
users_dict = {user["user_id"]: user for user in users}
# attach user objects to track objects
for i, track in enumerate(tracks):
user = users_dict[track["owner_id"]]
# Add user balance
balance = track_data[i][1]
associated_balance = track_data[i][2]
user[response_name_constants.balance] = balance
user[
response_name_constants.associated_wallets_balance
] = associated_balance
track["user"] = user
else:
# bundle peripheral info into track results
tracks = populate_track_metadata(session, track_ids, tracks, current_user_id)
# Preserve order from track_ids above
tracks_map = {}
for t in tracks:
tracks_map[t["track_id"]] = t
tracks = [tracks_map[track_id] for track_id in track_ids]
tracks_response = {
"all": tracks,
"saved": list(filter(lambda track: track["track_id"] in saved_tracks, tracks)),
}
return tracks_response
def playlist_search_query(session, searchStr, limit, offset, is_album,
personalized, is_auto_complete, current_user_id):
if personalized and not current_user_id:
return []
table_name = 'album_lexeme_dict' if is_album else 'playlist_lexeme_dict'
repost_type = RepostType.album if is_album else RepostType.playlist
save_type = SaveType.album if is_album else SaveType.playlist
# SQLAlchemy doesn't expose a way to escape a string with double-quotes instead of
# single-quotes, so we have to use traditional string substitution. This is safe
# because the value is not user-specified.
res = sqlalchemy.text(
# pylint: disable=C0301
f"""
select playlist_id from (
select
playlist_id,
(sum(score) + (:name_weight * similarity(coalesce(playlist_name, ''), query))) as total_score
from (
select
d."playlist_id" as playlist_id, d."word" as word, similarity(d."word", :query) as score,
d."playlist_name" as playlist_name, :query as query
from "{table_name}" d
{
'inner join "saves" s on s.save_item_id = d.playlist_id'
if personalized and current_user_id
else ""
}
where d."word" % :query
{
"and s.save_type='" + save_type +
"' and s.is_current=true and s.is_delete=false and s.user_id=:current_user_id"
if personalized and current_user_id
else ""
}
) as results
group by playlist_id, playlist_name, query
) as results2
order by total_score desc, playlist_id asc
limit :limit
offset :offset;
""")
playlist_ids = session.execute(
res,
{
"query": searchStr,
"limit": limit,
"offset": offset,
"name_weight": playlistNameWeight,
"current_user_id": current_user_id
},
).fetchall()
# playlist_ids is list of tuples - simplify to 1-D list
playlist_ids = [i[0] for i in playlist_ids]
playlists = get_unpopulated_playlists(session, playlist_ids, True)
if is_auto_complete:
# fetch users for playlists
playlist_owner_ids = list(
map(lambda playlist: playlist["playlist_owner_id"], playlists))
users = get_unpopulated_users(session, playlist_owner_ids)
users_dict = {user["user_id"]: user for user in users}
# attach user objects to playlist objects
for playlist in playlists:
playlist["user"] = users_dict[playlist["playlist_owner_id"]]
else:
# bundle peripheral info into playlist results
playlists = populate_playlist_metadata(session, playlist_ids,
playlists, [repost_type],
[save_type], current_user_id)
# preserve order from playlist_ids above
playlists = [
next((p for p in playlists if p["playlist_id"] == playlist_id), None)
for playlist_id in playlist_ids
]
return playlists
def track_search_query(session, searchStr, limit, offset, personalized,
is_auto_complete, current_user_id, only_downloadable):
if personalized and not current_user_id:
return []
res = sqlalchemy.text(
# pylint: disable=C0301
f"""
select track_id from (
select
track_id,
(sum(score) + (:title_weight * similarity(coalesce(title, ''), query))) as total_score
from (
select
d."track_id" as track_id, d."word" as word, similarity(d."word", :query) as score,
d."track_title" as title, :query as query
from "track_lexeme_dict" d
{
'inner join "saves" s on s.save_item_id = d.track_id'
if personalized and current_user_id
else ""
}
{
'inner join "tracks" t on t.track_id = d.track_id'
if only_downloadable
else ""
}
where d."word" % :query
{
"and s.save_type='track' and s.is_current=true and " +
"s.is_delete=false and s.user_id = :current_user_id"
if personalized and current_user_id
else ""
}
{
"and (t.download->>'is_downloadable')::boolean is True"
if only_downloadable
else ""
}
) as results
group by track_id, title, query
) as results2
order by total_score desc, track_id asc
limit :limit
offset :offset;
""")
track_ids = session.execute(
res,
{
"query": searchStr,
"limit": limit,
"offset": offset,
"title_weight": trackTitleWeight,
"current_user_id": current_user_id
},
).fetchall()
# track_ids is list of tuples - simplify to 1-D list
track_ids = [i[0] for i in track_ids]
tracks = get_unpopulated_tracks(session, track_ids, True)
if is_auto_complete == True:
# fetch users for tracks
track_owner_ids = list(map(lambda track: track["owner_id"], tracks))
users = get_unpopulated_users(session, track_owner_ids)
users_dict = {user["user_id"]: user for user in users}
# attach user objects to track objects
for track in tracks:
track["user"] = users_dict[track["owner_id"]]
else:
# bundle peripheral info into track results
tracks = populate_track_metadata(session, track_ids, tracks,
current_user_id)
# preserve order from track_ids above
tracks = [
next((t for t in tracks if t["track_id"] == track_id), None)
for track_id in track_ids
]
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
def get_top_genre_users(args):
genres = []
if "genre" in args:
genres = args.get("genre")
if isinstance(genres, str):
genres = [genres]
# 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:
users = get_unpopulated_users(session, user_ids)
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 search_tags():
search_str = request.args.get("query", type=str)
current_user_id = get_current_user_id(required=False)
if not search_str:
raise exceptions.ArgumentError("Invalid value for parameter 'query'")
user_tag_count = request.args.get("user_tag_count", type=str)
if not user_tag_count:
user_tag_count = "2"
kind = request.args.get("kind", type=str, default="all")
validSearchKinds = [SearchKind.all, SearchKind.tracks, SearchKind.users]
try:
searchKind = SearchKind[kind]
if searchKind not in validSearchKinds:
raise Exception
except Exception:
return api_helpers.error_response(
"Invalid value for parameter 'kind' must be in %s" %
[k.name for k in validSearchKinds], 400)
results = {}
(limit, offset) = get_pagination_vars()
db = get_db_read_replica()
with db.scoped_session() as session:
if (searchKind in [SearchKind.all, SearchKind.tracks]):
results['tracks'] = search_track_tags(
session, {
'search_str': search_str,
'current_user_id': current_user_id,
'limit': limit,
'offset': offset
})
if (searchKind in [SearchKind.all, SearchKind.users]):
results['users'] = search_user_tags(
session, {
'search_str': search_str,
'current_user_id': current_user_id,
"user_tag_count": user_tag_count,
'limit': limit,
'offset': offset
})
# Add personalized results for a given user
if current_user_id:
if (searchKind in [SearchKind.all, SearchKind.tracks]):
# Query saved tracks for the current user that contain this tag
track_ids = [track['track_id'] for track in results['tracks']]
track_play_counts = {
track['track_id']: track[response_name_constants.play_count]
for track in results['tracks']
}
saves_query = (session.query(Save.save_item_id).filter(
Save.is_current == True, Save.is_delete == False,
Save.save_type == SaveType.track,
Save.user_id == current_user_id,
Save.save_item_id.in_(track_ids)).all())
saved_track_ids = [i[0] for i in saves_query]
saved_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_(saved_track_ids),
).all())
saved_tracks = helpers.query_result_to_list(saved_tracks)
for saved_track in saved_tracks:
saved_track_id = saved_track["track_id"]
saved_track[response_name_constants.play_count] = \
track_play_counts.get(saved_track_id, 0)
saved_tracks = \
populate_track_metadata(
session, saved_track_ids, saved_tracks, current_user_id)
# Sort and paginate
play_count_sorted_saved_tracks = \
sorted(
saved_tracks, key=lambda i: i[response_name_constants.play_count], reverse=True)
play_count_sorted_saved_tracks = \
play_count_sorted_saved_tracks[slice(
offset, offset + limit, 1)]
results['saved_tracks'] = play_count_sorted_saved_tracks
if (searchKind in [SearchKind.all, SearchKind.users]):
# Query followed users that have referenced this tag
user_ids = [user['user_id'] for user in results['users']]
followed_user_query = (session.query(
Follow.followee_user_id).filter(
Follow.is_current == True, Follow.is_delete == False,
Follow.follower_user_id == current_user_id,
Follow.followee_user_id.in_(user_ids)).all())
followed_user_ids = [i[0] for i in followed_user_query]
followed_users = get_unpopulated_users(session, followed_user_ids)
followed_users = \
populate_user_metadata(
session,
followed_user_ids,
followed_users,
current_user_id
)
followed_users_followee_sorted = \
sorted(
followed_users,
key=lambda i: i[response_name_constants.follower_count],
reverse=True)
followed_users_followee_sorted = \
followed_users_followee_sorted[slice(
offset, offset + limit, 1)]
results['followed_users'] = followed_users_followee_sorted
return api_helpers.success_response(results)
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": max(limit, MIN_SEARCH_LEXEME_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 = get_unpopulated_users(session, user_ids)
# TODO: Populate user metadata should be sped up to be able to be
# used in search autocomplete as that'll give us better results.
if is_auto_complete:
# get follower information to improve search ordering
users = populate_user_follower_counts(session, user_ids, users)
else:
# bundle peripheral info into user results
users = populate_user_metadata(session, user_ids, users,
current_user_id)
# Preserve order from user_ids above
user_map = {}
for u in users:
user_map[u["user_id"]] = u
users = [user_map[user_id] for user_id in user_ids]
# Sort users by extra criteria for "best match"
users.sort(key=cmp_to_key(compare_users))
return users[0:limit]
def track_search_query(session, searchStr, limit, offset, personalized,
is_auto_complete, current_user_id, only_downloadable):
if personalized and not current_user_id:
return []
res = sqlalchemy.text(
# pylint: disable=C0301
f"""
select track_id from (
select
track_id,
(sum(score) + (:title_weight * similarity(coalesce(title, ''), query))) as total_score
from (
select
d."track_id" as track_id, d."word" as word, similarity(d."word", :query) as score,
d."track_title" as title, :query as query
from "track_lexeme_dict" d
{
'inner join "saves" s on s.save_item_id = d.track_id'
if personalized and current_user_id
else ""
}
{
'inner join "tracks" t on t.track_id = d.track_id'
if only_downloadable
else ""
}
where d."word" % :query
{
"and s.save_type='track' and s.is_current=true and " +
"s.is_delete=false and s.user_id = :current_user_id"
if personalized and current_user_id
else ""
}
{
"and (t.download->>'is_downloadable')::boolean is True"
if only_downloadable
else ""
}
) as results
group by track_id, title, query
) as results2
order by total_score desc, track_id asc
limit :limit
offset :offset;
""")
track_ids = session.execute(
res,
{
"query": searchStr,
"limit": max(limit, MIN_SEARCH_LEXEME_LIMIT),
"offset": offset,
"title_weight": trackTitleWeight,
"current_user_id": current_user_id
},
).fetchall()
# track_ids is list of tuples - simplify to 1-D list
track_ids = [i[0] for i in track_ids]
tracks = get_unpopulated_tracks(session, track_ids, True)
# TODO: Populate track metadata should be sped up to be able to be
# used in search autocomplete as that'll give us better results.
if is_auto_complete == True:
# fetch users for tracks
track_owner_ids = list(map(lambda track: track["owner_id"], tracks))
users = get_unpopulated_users(session, track_owner_ids)
users_dict = {user["user_id"]: user for user in users}
# attach user objects to track objects
for track in tracks:
track["user"] = users_dict[track["owner_id"]]
tracks = populate_track_repost_counts(session, track_ids, tracks)
else:
# bundle peripheral info into track results
tracks = populate_track_metadata(session, track_ids, tracks,
current_user_id)
# Preserve order from track_ids above
tracks_map = {}
for t in tracks:
tracks_map[t["track_id"]] = t
tracks = [tracks_map[track_id] for track_id in track_ids]
# Sort tracks by extra criteria for "best match"
tracks.sort(key=cmp_to_key(compare_tracks))
return tracks[0:limit]
def get_users_account(args):
db = get_db_read_replica()
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)
if "wallet" not in args:
raise exceptions.ArgumentError("Missing wallet param")
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:
raise exceptions.ArgumentError("Invalid wallet length")
# If user cannot be found, exit early and return empty response
user = base_query.first()
if not user:
return None
user = helpers.model_to_dictionary(user)
user_id = user["user_id"]
# bundle peripheral info into user results
users = populate_user_metadata(session, [user_id], [user], user_id,
True)
user = users[0]
# Get saved playlists / albums ids
saved_query = session.query(Save.save_item_id).filter(
Save.user_id == user_id,
Save.is_current == True,
Save.is_delete == False,
or_(Save.save_type == SaveType.playlist,
Save.save_type == SaveType.album),
)
saved_query_results = saved_query.all()
save_collection_ids = [item[0] for item in saved_query_results]
# Get Playlist/Albums saved or owned by the user
playlist_query = (session.query(Playlist).filter(
or_(
and_(
Playlist.is_current == True,
Playlist.is_delete == False,
Playlist.playlist_owner_id == user_id,
),
and_(
Playlist.is_current == True,
Playlist.is_delete == False,
Playlist.playlist_id.in_(save_collection_ids),
),
)).order_by(desc(Playlist.created_at)))
playlists = playlist_query.all()
playlists = helpers.query_result_to_list(playlists)
playlist_owner_ids = list(
{playlist["playlist_owner_id"]
for playlist in playlists})
# Get Users for the Playlist/Albums
users = get_unpopulated_users(session, playlist_owner_ids)
user_map = {}
stripped_playlists = []
# Map the users to the playlists/albums
for playlist_owner in users:
user_map[playlist_owner["user_id"]] = playlist_owner
for playlist in playlists:
playlist_owner = user_map[playlist["playlist_owner_id"]]
stripped_playlist = {
"id": playlist["playlist_id"],
"name": playlist["playlist_name"],
"is_album": playlist["is_album"],
"user": {
"id": playlist_owner["user_id"],
"handle": playlist_owner["handle"],
},
}
if playlist_owner["is_deactivated"]:
stripped_playlist["user"]["is_deactivated"] = True
stripped_playlists.append(stripped_playlist)
user["playlists"] = stripped_playlists
return user
def user_search_query(
session, search_str, limit, offset, is_auto_complete, current_user_id
):
res = sqlalchemy.text(
f"""
select u.user_id, b.balance, b.associated_wallets_balance, is_followed from (
select user_id, is_followed from (
select user_id, is_followed, (
sum(score) +
(:follower_weight * log(case when (follower_count = 0) then 1 else follower_count end)) +
(case when (handle=query) then :handle_match_boost else 0 end) +
(:name_weight * similarity(coalesce(name, ''), query))
{
"+ (case when (is_followed) " +
"then :current_user_saved_match_boost else 0 end)"
if current_user_id
else ""
}
) as total_score from (
select
d."user_id" as user_id,
d."word" as word,
d."handle" as handle,
similarity(d."word", :query) as score,
d."user_name" as name,
:query as query,
d."follower_count" as follower_count
{
', f."follower_user_id" is not null as is_followed'
if current_user_id
else ", false as is_followed"
}
from "user_lexeme_dict" d
{
"left outer join (select follower_user_id, followee_user_id from follows " +
"where follows.is_current = true " +
"and follows.is_delete = false " +
"and follows.follower_user_id = :current_user_id) f " +
"on f.followee_user_id = d.user_id"
if current_user_id
else ""
}
where
d."word" % :query OR
d."handle" = :query
) as results
group by user_id, name, query, handle, follower_count, is_followed
) as results2
order by total_score desc, user_id asc
limit :limit
offset :offset
) as u left join user_balances b on u.user_id = b.user_id
"""
)
user_result_proxy = session.execute(
res,
{
"query": search_str,
"limit": limit,
"offset": offset,
"name_weight": user_name_weight,
"follower_weight": user_follower_weight,
"current_user_id": current_user_id,
"handle_match_boost": user_handle_exact_match_boost,
"current_user_saved_match_boost": current_user_saved_match_boost,
},
)
user_info = user_result_proxy.fetchall()
user_cols = user_result_proxy.keys()
# user_ids is list of tuples - simplify to 1-D list
user_ids = [user[user_cols.index("user_id")] for user in user_info]
# if user has a follower_user_id, the current user has followed that user
followed_users = {
user[0] for user in user_info if user[user_cols.index("is_followed")]
}
users = get_unpopulated_users(session, user_ids)
if is_auto_complete:
for i, user in enumerate(users):
balance = user_info[i][1]
associated_wallets_balance = user_info[i][2]
user[response_name_constants.balance] = balance
user[
response_name_constants.associated_wallets_balance
] = associated_wallets_balance
else:
# bundle peripheral info into user results
users = populate_user_metadata(session, user_ids, users, current_user_id)
# Preserve order from user_ids above
user_map = {}
for u in users:
user_map[u["user_id"]] = u
users = [user_map[user_id] for user_id in user_ids]
# Sort users by extra criteria for "best match"
users.sort(key=cmp_to_key(compare_users))
users_response = {
"all": users,
"followed": list(filter(lambda user: user["user_id"] in followed_users, users)),
}
return users_response
def playlist_search_query(
session,
search_str,
limit,
offset,
is_album,
is_auto_complete,
current_user_id,
):
table_name = "album_lexeme_dict" if is_album else "playlist_lexeme_dict"
repost_type = RepostType.album if is_album else RepostType.playlist
save_type = SaveType.album if is_album else SaveType.playlist
# SQLAlchemy doesn't expose a way to escape a string with double-quotes instead of
# single-quotes, so we have to use traditional string substitution. This is safe
# because the value is not user-specified.
res = sqlalchemy.text(
# pylint: disable=C0301
f"""
select p.playlist_id, b.balance, b.associated_wallets_balance, is_saved from (
select distinct on (owner_id) playlist_id, owner_id, is_saved, total_score from (
select playlist_id, owner_id, is_saved, (
(:similarity_weight * sum(score)) +
(:title_weight * similarity(coalesce(playlist_name, ''), query)) +
(:user_name_weight * similarity(coalesce(user_name, ''), query)) +
(:repost_weight * log(case when (repost_count = 0) then 1 else repost_count end)) +
(case when (lower(query) = coalesce(playlist_name, '')) then :title_match_boost else 0 end) +
(case when (lower(query) = handle) then :handle_match_boost else 0 end) +
(case when (lower(query) = user_name) then :user_name_match_boost else 0 end)
{
'+ (case when (is_saved) then ' +
':current_user_saved_match_boost else 0 end)'
if current_user_id
else ""
}
) as total_score
from (
select
d."playlist_id" as playlist_id, d."word" as word, similarity(d."word", :query) as score,
d."playlist_name" as playlist_name, :query as query, d."repost_count" as repost_count,
d."handle" as handle, d."user_name" as user_name, d."owner_id" as owner_id
{
', s."user_id" is not null as is_saved'
if current_user_id
else ", false as is_saved"
}
from "{table_name}" d
{
"left outer join (select save_item_id, user_id from saves where saves.save_type = '"
+ save_type + "' and saves.is_current = true and " +
"saves.is_delete = false and saves.user_id = :current_user_id ) " +
"s on s.save_item_id = d.playlist_id"
if current_user_id
else ""
}
where (d."word" % lower(:query) or d."handle" = lower(:query) or d."user_name" % lower(:query))
) as results
group by playlist_id, playlist_name, query, repost_count, user_name, handle, owner_id, is_saved
) as results2
order by owner_id, total_score desc
) as p left join user_balances b on p.owner_id = b.user_id
order by total_score desc
limit :limit
offset :offset;
"""
)
playlist_result_proxy = session.execute(
res,
{
"query": search_str,
"limit": limit,
"offset": offset,
"title_weight": search_title_weight,
"repost_weight": search_repost_weight,
"similarity_weight": search_similarity_weight,
"current_user_id": current_user_id,
"user_name_weight": search_user_name_weight,
"title_match_boost": search_title_exact_match_boost,
"handle_match_boost": search_handle_exact_match_boost,
"user_name_match_boost": search_user_name_exact_match_boost,
"current_user_saved_match_boost": current_user_saved_match_boost,
},
)
playlist_data = playlist_result_proxy.fetchall()
playlist_cols = playlist_result_proxy.keys()
# playlist_ids is list of tuples - simplify to 1-D list
playlist_ids = [
playlist[playlist_cols.index("playlist_id")] for playlist in playlist_data
]
saved_playlists = {
playlist[0]
for playlist in playlist_data
if playlist[playlist_cols.index("is_saved")]
}
playlists = get_unpopulated_playlists(session, playlist_ids, True)
# TODO: Populate playlist metadata should be sped up to be able to be
# used in search autocomplete as that'll give us better results.
if is_auto_complete:
# fetch users for playlists
playlist_owner_ids = list(
map(lambda playlist: playlist["playlist_owner_id"], playlists)
)
users = get_unpopulated_users(session, playlist_owner_ids)
users_dict = {user["user_id"]: user for user in users}
# attach user objects to playlist objects
for i, playlist in enumerate(playlists):
user = users_dict[playlist["playlist_owner_id"]]
# Add user balance
balance = playlist_data[i][1]
associated_balance = playlist_data[i][2]
user[response_name_constants.balance] = balance
user[
response_name_constants.associated_wallets_balance
] = associated_balance
playlist["user"] = user
else:
# bundle peripheral info into playlist results
playlists = populate_playlist_metadata(
session,
playlist_ids,
playlists,
[repost_type],
[save_type],
current_user_id,
)
# Preserve order from playlist_ids above
playlists_map = {}
for p in playlists:
playlists_map[p["playlist_id"]] = p
playlists = [playlists_map[playlist_id] for playlist_id in playlist_ids]
playlists_resp = {
"all": playlists,
"saved": list(
filter(
lambda playlist: playlist["playlist_id"] in saved_playlists, playlists
)
),
}
return playlists_resp
def search_tags():
search_str = request.args.get("query", type=str)
current_user_id = get_current_user_id(required=False)
if not search_str:
raise exceptions.ArgumentError("Invalid value for parameter 'query'")
user_tag_count = request.args.get("user_tag_count", type=str)
if not user_tag_count:
user_tag_count = "2"
kind = request.args.get("kind", type=str, default="all")
validSearchKinds = [SearchKind.all, SearchKind.tracks, SearchKind.users]
try:
searchKind = SearchKind[kind]
if searchKind not in validSearchKinds:
raise Exception
except Exception:
return api_helpers.error_response(
"Invalid value for parameter 'kind' must be in %s" %
[k.name for k in validSearchKinds], 400)
results = {}
(limit, offset) = get_pagination_vars()
like_tags_str = str.format('%{}%', search_str)
db = get_db_read_replica()
with db.scoped_session() as session:
if (searchKind in [SearchKind.all, SearchKind.tracks]):
track_res = sqlalchemy.text(f"""
select distinct(track_id)
from
(
select
strip(to_tsvector(tracks.tags)) as tagstrip,
track_id
from
tracks
where
(tags like :like_tags_query)
and (is_current is true)
and (is_delete is false)
and (is_unlisted is false)
and (stem_of is NULL)
order by
updated_at desc
) as t
where
tagstrip @@ to_tsquery(:query);
""")
track_ids = session.execute(track_res, {
"query": search_str,
"like_tags_query": like_tags_str
}).fetchall()
# 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(track_ids)
tracks = populate_track_metadata(session, track_ids, tracks,
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(offset, offset + limit, 1)]
results['tracks'] = play_count_sorted_tracks
if (searchKind in [SearchKind.all, SearchKind.users]):
user_res = sqlalchemy.text(f"""
select * from
(
select
count(track_id),
owner_id
from
(
select
strip(to_tsvector(tracks.tags)) as tagstrip,
track_id,
owner_id
from
tracks
where
(tags like :like_tags_query)
and (is_current is true)
and (is_unlisted is false)
and (stem_of is NULL)
order by
updated_at desc
) as t
where
tagstrip @@ to_tsquery(:query)
group by
owner_id
order by
count desc
) as usr
where
usr.count >= :user_tag_count;
""")
user_ids = session.execute(
user_res, {
"query": search_str,
"like_tags_query": like_tags_str,
"user_tag_count": user_tag_count
}).fetchall()
# user_ids is list of tuples - simplify to 1-D list
user_ids = [i[1] 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)
users = populate_user_metadata(session, user_ids, users,
current_user_id)
followee_sorted_users = \
sorted(
users, key=lambda i: i[response_name_constants.follower_count], reverse=True)
followee_sorted_users = \
followee_sorted_users[slice(offset, offset + limit, 1)]
results['users'] = followee_sorted_users
# Add personalized results for a given user
if current_user_id:
if (searchKind in [SearchKind.all, SearchKind.tracks]):
# Query saved tracks for the current user that contain this tag
saves_query = (session.query(Save.save_item_id).filter(
Save.is_current == True, Save.is_delete == False,
Save.save_type == SaveType.track,
Save.user_id == current_user_id,
Save.save_item_id.in_(track_ids)).all())
saved_track_ids = [i[0] for i in saves_query]
saved_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_(saved_track_ids),
).all())
saved_tracks = helpers.query_result_to_list(saved_tracks)
for saved_track in saved_tracks:
saved_track_id = saved_track["track_id"]
saved_track[response_name_constants.play_count] = \
track_play_counts.get(saved_track_id, 0)
saved_tracks = \
populate_track_metadata(
session, saved_track_ids, saved_tracks, current_user_id)
# Sort and paginate
play_count_sorted_saved_tracks = \
sorted(
saved_tracks, key=lambda i: i[response_name_constants.play_count], reverse=True)
play_count_sorted_saved_tracks = \
play_count_sorted_saved_tracks[slice(
offset, offset + limit, 1)]
results['saved_tracks'] = play_count_sorted_saved_tracks
if (searchKind in [SearchKind.all, SearchKind.users]):
# Query followed users that have referenced this tag
followed_user_query = (session.query(
Follow.followee_user_id).filter(
Follow.is_current == True, Follow.is_delete == False,
Follow.follower_user_id == current_user_id,
Follow.followee_user_id.in_(user_ids)).all())
followed_user_ids = [i[0] for i in followed_user_query]
followed_users = get_unpopulated_users(session, followed_user_ids)
followed_users = \
populate_user_metadata(
session,
followed_user_ids,
followed_users,
current_user_id
)
followed_users_followee_sorted = \
sorted(
followed_users,
key=lambda i: i[response_name_constants.follower_count],
reverse=True)
followed_users_followee_sorted = \
followed_users_followee_sorted[slice(
offset, offset + limit, 1)]
results['followed_users'] = followed_users_followee_sorted
return api_helpers.success_response(results)
