gistfile1.txt

def find_thread_for_proposal(self, proposal):
        """Finds comment thread for proposal"""
        sql = """
            with recursive path as (
                    select
                        id,
                        array[id] as path,
                        description,
                        profile_id,
                        created_at,
                        is_published,
                        votes,
                        0 as level
                    from
                        idcommunes_comment
                    where
                        proposal_id = %s and parent_id is null and is_published
                union all
                    select
                        idcommunes_comment.id,
                        path.path || idcommunes_comment.id as path,
                        idcommunes_comment.description,
                        idcommunes_comment.profile_id,
                        idcommunes_comment.created_at,
                        idcommunes_comment.is_published,
                        idcommunes_comment.votes,
                        path.level + 1 as level
                    from idcommunes_comment, path
                        where idcommunes_comment.parent_id = path.id and idcommunes_comment.is_published
            )
            select
                path.id as id,
                path.path as path,
                path.description as description,
                path.profile_id as profile_id,
                path.created_at as created_at,
                path.is_published as is_published,
                path.votes as votes,
                path.level as level,
                profile.name as profile_name,
                profile.is_mayor as profile_is_mayor,
                profile.town_id as profile_town_id
            from path left join idcommunes_profile profile on path.profile_id = profile.id
            order by path asc, created_at asc;
        """ % proposal.id
        cursor = connection.cursor()
        cursor.execute(sql)
        results = []
        for row in dict_cursor(cursor):
            results.append(row)
        nested = nestify(results)
        return nested