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)
