def question_select_query(question_id): """Select question from question table and user from users table by question_id""" question_query = ("""SELECT question.id, question.submission_time,question.view_number,question.vote_number,question.title,question.message,users.username FROM question LEFT JOIN users ON question.user_id=users.id WHERE question.id = {};""".format(question_id)) return db_connection.db_request(str(question_query))
def answer_select_query(question_id): """Select answer from answer table and user from users table by question_id""" answer_query = ("""SELECT answer.id, answer.submission_time, answer.vote_number, answer.message, users.username FROM answer LEFT JOIN users ON answer.user_id = users.id WHERE answer.question_id = {}; """.format(question_id)) return db_connection.db_request(str(answer_query))
def user_question_comments_query(user_id): """Select Users's Comments (and the relevant Questions) from the "comment" and "question" tables""" user_question_comments_query = ("""SELECT question.title, comment.message, question.id FROM comment INNER JOIN question ON question.id=comment.question_id WHERE comment.user_id={};""".format(user_id)) return db_connection.db_request(user_question_comments_query)
def tag_select_query(question_id): """Select tag from question_tag table by question_id""" tag_query = ("""SELECT question_tag.question_id, tag.id, tag.name FROM question_tag INNER JOIN tag ON question_tag.tag_id=tag.id WHERE question_id = {};""".format(question_id)) return db_connection.db_request(str(tag_query))
def question_comment_select_query(question_id): """Select question comment from comment table and user from users table by question_id""" question_comment_query = ("""SELECT comment.id, comment.message, comment.submission_time, users.username FROM comment LEFT JOIN users ON comment.user_id = users.id WHERE comment.question_id = {}; """.format(question_id)) return db_connection.db_request(str(question_comment_query))
def search_query(): search_parameter = request.form['search'] search_query = """SELECT DISTINCT question.id, question.submission_time, question.view_number, question.vote_number, question.title, question.message, question.image FROM question LEFT JOIN answer ON question.id=answer.question_id WHERE question.title LIKE '%{}%' OR question.message LIKE '%{}%' OR answer.message LIKE '%{}%';""".format(search_parameter, search_parameter, search_parameter) return db_connection.db_request(str(search_query))
def user_answers_query(user_id): """Select Users's Answers (and the relevant Questions) from the "answer" and "question" tables""" user_answers_query = ("""SELECT question.title, answer.message, question.id FROM question INNER JOIN answer ON question.id = answer.question_id WHERE answer.user_id={};""".format(user_id)) return db_connection.db_request(user_answers_query)
def vote_update_sql_query(table, _id, direction): """The function increase or decrease the vote_number. Input parameters:table=given table(answer or question), _id = answer_id or question_id, direction= 'up' or 'down'""" select_query = ("SELECT vote_number FROM {} WHERE id={};".format(table, _id)) vote_number = db_connection.db_request(select_query) votes = int(vote_number[0][0]) if direction == 'up': votes += 1 elif direction == 'down': votes -= 1 sql_to_edit_vote = ("UPDATE {} SET vote_number= {} WHERE id= {};".format( table, votes, _id)) db_connection.db_update(str(sql_to_edit_vote))
def select_order_by_query(): question_table = ("""SELECT * FROM question ORDER BY id LIMIT 5;""") return db_connection.db_request(question_table)
def select_query(column, table): """SELECT row or rows from given table.Input parameters: column= column from given table table=given table""" select_table = ("SELECT {} FROM {};".format(column, table)) return db_connection.db_request(select_table)
def sort_query(): for key in request.args: criteria = key order = request.args.get(key) query = "SELECT * FROM question ORDER BY {} {};".format(criteria, order) return db_connection.db_request(query)
def select_query_where(column, table, criteria, condition): """SELECT row or rows from given table. Input parameters: table=given table, criteria= WHERE criteria, condition= WHERE condition""" question_query = ("SELECT {} FROM {} WHERE {}='{}';".format(column, table, criteria, condition)) return db_connection.db_request(question_query)
def select_user(): select_user_query = """SELECT id, username FROM users;""" return db_connection.db_request(select_user_query)