def get_track_listen_milestones(limit=100): db = get_db_read_replica() with db.scoped_session() as session: results = (session.query( Play.play_item_id.distinct().label('play_item_id'), func.max(Play.created_at).label('max')).group_by( Play.play_item_id).order_by(desc("max")).limit(limit).all()) track_ids = [result[0] for result in results] track_id_play_counts = get_track_play_counts(session, track_ids) return track_id_play_counts
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 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 = (session.query(User).filter( User.is_current == True, User.user_id.in_(followed_user_ids)).all()) followed_users = helpers.query_result_to_list(followed_users) 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)