db.py 文件源码

def get_results_from_user(user_id, contest_id):
    query = """
    select value, higher_ranked_play_count from descriptors 
    left outer join (
        select count(answers.id) as higher_ranked_play_count, descriptors.id as inner_descriptor_id 
        from descriptors 
        left outer join answers on descriptors.id=answers.higher_ranked_descriptor_id 
        where contest_id=? and answers.user_id=?
        group by descriptors.id 
    ) on descriptors.id=inner_descriptor_id 
    where descriptors.contest_id=? 
    order by higher_ranked_play_count desc
    """
    # REFACT get sql to get the zeros right
    cursor = g.db.execute(query, [contest_id, user_id, contest_id])
    return [(row[0], row[1] or 0) for row in cursor.fetchall()]