def get_posts_parent_tree(self, data):
root_posts = self.get_root_posts(data)
result = []
data.update({'parent': 0})
for parent in root_posts:
data['parent'] = parent['id']
cmd = """SELECT * FROM posts p
WHERE p.thread = {thread} and p.path[1] = {parent}
""".format(**data)
if data['since']:
cmd += ' AND p.path[1]'
cmd += ' < ' if data['desc'] else ' > '
cmd += '(SELECT path[1] FROM posts WHERE id = ' + data[
'since'].__str__() + ')'
# order = 'DESC' if data['desc'] else 'ASC'
cmd += ' ORDER BY p.path, p.id'
db_service.execute(cmd)
current_result = db_service.get_all()
for post in current_result:
post['created'] = time_to_str(post['created'])
result.extend(current_result)
return result
def get_thread_by_id(self, id):
cmd = """SELECT * FROM threads WHERE id = {id}
""".format(id=id)
db_service.execute(cmd)
result = db_service.get_one()
if 'created' in result.keys() and result['created'] is not None:
result['created'] = time_to_str(result['created'])
return result
def get_thread_by_slug(self, slug):
cmd = """SELECT * FROM threads WHERE slug = '{slug}'
""".format(slug=slug)
db_service.execute(cmd)
result = db_service.get_one()
if 'created' in result.keys() and result['created'] is not None:
result['created'] = time_to_str(result['created'])
return result
def details(self, data):
db_service.reconnect()
cmd = """SELECT * FROM posts p WHERE p.id = {id};
""".format(**data)
db_service.execute(cmd)
result = {"post": db_service.get_one()}
# if 'created' in result['post'].keys() and result['post']['created'] is not None:
# result['post']['created'] = time_to_str(result['post']['created'])
result['post']['isEdited'] = result['post']['isedited']
if 'user' in data['related']:
cmd += """SELECT u.email, u.about, u.nickname, u.fullname
FROM users u JOIN posts p ON p.author = u.nickname WHERE p.id = {id};
""".format(**data)
db_service.execute(cmd)
result.update({"author": db_service.get_one()})
if 'thread' in data['related']:
cmd += """SELECT t.author, t.created, t.forum, t.id, t.message, t.slug, t.title, t.votes
FROM threads t JOIN posts p ON p.thread = t.id WHERE p.id = {id};
""".format(**data)
db_service.execute(cmd)
result.update({"thread": db_service.get_one()})
if 'created' in result['thread'].keys(
) and result['thread']['created'] is not None:
result['thread']['created'] = time_to_str(
result['thread']['created'])
if 'forum' in data['related']:
cmd += """SELECT f.slug, f.posts, f.threads, f.title, f.author
FROM forums f JOIN posts p ON p.forum = f.slug WHERE p.id = {id};
""".format(**data)
db_service.execute(cmd)
result.update({"forum": db_service.get_one()})
if 'created' in result['forum'].keys(
) and result['forum']['created'] is not None:
result['forum']['created'] = time_to_str(
result['forum']['created'])
result['forum']['user'] = result['forum']['author']
result['forum'].pop('author')
return result
def get_posts_tree(self, data):
cmd = """SELECT * FROM posts p
WHERE p.thread = {thread}
""".format(**data)
if data['since']:
cmd += ' AND p.path'
cmd += ' < ' if data['desc'] else ' > '
cmd += '(SELECT path FROM posts WHERE id = ' + data[
'since'].__str__() + ')'
order = 'DESC' if data['desc'] else 'ASC'
cmd += ' ORDER BY p.path ' + order + ' LIMIT ' + data['limit']
db_service.execute(cmd)
result = db_service.get_all()
for post in result:
post['created'] = time_to_str(post['created'])
return result
def get_forum_threads(self, data):
cmd = """SELECT * FROM threads t
WHERE LOWER(t.forum) = LOWER('{slug}')
""".format(**data)
if data['since']:
cmd += ' AND t.created'
cmd += ' <= ' if data['desc'] else ' >= '
cmd += "'" + data['since'] + "'"
order = 'DESC' if data['desc'] else 'ASC'
cmd += ' ORDER BY t.created ' + order
cmd += ' LIMIT ' + data['limit'] if data['limit'] else ''
db_service.execute(cmd)
result = db_service.get_all()
for thread in result:
thread['created'] = time_to_str(thread['created'])
return result