def get_all_artists_list(sid): return db.fetch_all( "SELECT artist_name, artist_id " "FROM r4_artists JOIN r4_song_artist USING (artist_id) JOIN r4_song_sid using (song_id) " "WHERE r4_song_sid.sid = %s AND song_exists = TRUE " "GROUP BY artist_id, artist_name " "ORDER BY artist_name", (user.sid,))
def get_listeners_dict(sid): guests = db.c.fetch_var("SELECT COUNT(*) FROM r4_listeners WHERE sid = %s AND user_id = 1 AND listener_purge = FALSE", (sid,)) # SLOW QUERY clist = db.fetch_all( "SELECT r4_listeners.user_id, username, COUNT(vote_time) AS radio_2wkvotes " "FROM r4_listeners JOIN phpbb_users USING (user_id) " "LEFT JOIN r4_vote_history ON (phpbb_users.user_id == r4_vote_history.user_id AND vote_time < %s) " "WHERE r4_listeners.sid = %s AND r4_listeners.user_id > 1 " "GROUP BY r4_listeners.user_id, username " "ORDER BY radio_2wkvotes DESC, username", ((time.time() - 1209600), sid)) # 1209600 is 2 weeks in seconds return { "guests": guests, "users": clist })