get_rth_comments.py

def get_comments(article_id):
    """Returns the comments for an article"""
    
    # fetch comments from database
    query = sql.text("""
        select      c.comment_id, c.parent_id, c.article_id, c.username,
                    c.date_posted, c.comment, 
                    sum(v.vote) as comment_score
        from        comments c
        left join   comment_votes v
        on          c.comment_id = v.comment_id
        where       c.article_id = :article_id
        group by    c.comment_id, c.parent_id, c.article_id, c.username,
                    c.date_posted, c.comment
        order by    comment_id
        """, bind=sql.engine
    )
    rs = query.execute(article_id=article_id).fetchall()

    # create unsorted comments list
    comments_unsorted = []

    # populate the unsorted comments list
    # with a dict for each comment
    for row in rs:
        this_dict = {}
        for k, v in row.items():
            this_dict[k] = v
        this_dict['sorted'] = False
        this_dict['indent'] = 0
        this_dict['row'] = row # preserve original row object
        comments_unsorted.append(this_dict)

    # now create a sorted comments list
    comments_sorted = []

    # first pass, move OP comments into sorted list
    for comment in comments_unsorted:
        if comment['parent_id'] == 0:
            comment['sorted'] = True # mark it as sorted
            comment['thread_id'] = comment['comment_id'] # make the thread_id the comment_id
            commenets_sorted.append(comment)

    # now take the sorted OP comments out of the unsorted comments list
    comments_unsorted = [comment for comment in comments_unsorted if comment['sorted'] == False]

    # now move child comments into the sorted list
    for _unsorted in comments_unsorted:
        for _sorted in comments_sorted:
            if _sorted['comment_id'] == _unsorted['parent_id']:
                _unsorted['sorted'] = True
                _unsorted['indent'] = _sorted['indent'] + 1
                _unsorted['thread_id'] = _sorted['thread_id'] # get all comments in a thread under the OP
                comments_sorted.insert(comments_sorted.index(_sorted)+1, _unsorted)
                break

    return comments_sorted