Skip to content

Instantly share code, notes, and snippets.

@n1k0
Created December 29, 2011 15:14
Show Gist options
  • Save n1k0/1534510 to your computer and use it in GitHub Desktop.
Save n1k0/1534510 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment