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