def print_table():
"""
Based on sorting priorities stored in the query string,
lists Questions with list.html
"""
pairs = {
'ID': 'id',
'Question': 'title',
'Date': 'submission_time',
'Number of Views': 'view_number',
'Votes': 'vote_number'
}
sortingcols = request.args
order = ','.join(
[pairs[col] + ' ' + sortingcols[col] for col in sortingcols][::-1])
sql_query = """SELECT q.id, q.title, q.submission_time, q.view_number, q.vote_number, q.image, u.name
FROM question q JOIN users u ON (q.users_id = u.id)"""
if order:
sql_query += '\nORDER BY q.' + order + ';'
questions = data_manager.run_query(sql_query)
headers = [
'question_id', 'title', 'submission_time', 'view_number',
'vote_number', 'image', 'user_name'
]
questions = data_manager.build_dict(questions, headers)
url = '&'.join([key + '=' + sortingcols[key] for key in sortingcols])
return render_template('list.html', questions=questions, url=url)
def get_question(id):
"""
Return a single question (dict) by its ID
"""
question = data_manager.run_query("SELECT * FROM question WHERE id={};".format(id))
question = data_manager.build_dict(
question, ["question_id", "submission_time", "view_number", "vote_number", "title", "message", "image"])
return question[0]
def read_tags(question_id):
list_of_keys_of_tag = ["tag_id", "name", "question_id", "color"]
query_tag = """SELECT tag.id, tag.name, question_tag.question_id, tag.color FROM tag JOIN question_tag
ON tag.id = question_tag.tag_id WHERE question_tag.question_id={0} ORDER BY tag_id""".format(question_id)
data = data_manager.run_query(query_tag)
tags = data_manager.build_dict(data, list_of_keys_of_tag)
return tags
def get_users_with_id():
"""
Return the list of users with their id from database
in the format of dictinoary (keywords: id, name)
"""
query = """SELECT id, name FROM users;"""
user_list = data_manager.run_query(query)
user_list = data_manager.build_dict(user_list, ["id", "name"])
return user_list
def group_by_question_with_tags():
query = """SELECT tag.name, COUNT(question_tag.tag_id), tag.color
FROM tag
LEFT JOIN question_tag
ON tag.id=question_tag.tag_id
GROUP BY tag.id
ORDER BY COUNT(question_tag.tag_id) DESC;"""
tags_number_of_question = data_manager.run_query(query)
list_of_tag_number_key = ["name", "question_number", "color"]
dict_of_tags = data_manager.build_dict(tags_number_of_question, list_of_tag_number_key)
return dict_of_tags
def search_questions(term):
"""search for answers and questions with the given search term and display it
# search for answers with search term in their message"""
searchterm = term.lower()
# select questions
question_query = """SELECT DISTINCT id, title, message, submission_time
FROM question
WHERE id = ANY (SELECT question_id
FROM answer
WHERE LOWER(title) LIKE '%{0}%' OR LOWER(message) LIKE '%{0}%')
OR LOWER(message) LIKE '%{0}%' OR LOWER(title) LIKE '%{0}%';""".format(searchterm)
table = data_manager.run_query(question_query)
if table:
keys = ("question_id", "title", "message", "submission_time")
questions = data_manager.build_dict(table, keys)
fancy_questions = make_fancy(term, questions, "title")
fancy_questions = make_fancy(term, questions, "message")
else:
return render_template('search_results.html', questions=[], answers=[])
# select related answers
answer_query = """SELECT DISTINCT id, message, question_id
FROM answer
WHERE question_id = ANY (SELECT id
FROM question
WHERE LOWER(title) LIKE '%{0}%' OR LOWER(message) LIKE '%{0}%')
OR LOWER(message) LIKE '%{0}%';""".format(searchterm)
table = data_manager.run_query(answer_query)
keys = ("answer_id", "message", "question_id")
answers = data_manager.build_dict(table, keys)
fancy_answers = make_fancy(term, answers, "message")
return render_template('search_results.html', questions=fancy_questions, answers=fancy_answers)
def get_answers_for_question(question_id, sort_by, direction):
query = """
SELECT answer.*, users.name
FROM answer
LEFT JOIN users ON answer.users_id = users.id
WHERE question_id={0}
ORDER BY {1} {2};
""".format(question_id, sort_by, direction)
rows = data_manager.run_query(query)
list_of_names = [
"answer_id", "submission_time", "vote_number", "question_id",
"message", "image", "accepted", "user_id", "user_name"
]
answers = data_manager.build_dict(rows, list_of_names)
return answers
def get_comments(comment_type, question_id):
if comment_type == "question":
query = """SELECT *
FROM comment
WHERE question_id = {};
""".format(question_id)
elif comment_type == "answer":
query = """SELECT comment.id, comment.question_id, comment.answer_id, comment.message, comment.submission_time, comment.edited_count
FROM comment
LEFT JOIN answer ON answer_id = answer.id
WHERE answer.question_id = {};
""".format(question_id)
rows = data_manager.run_query(query)
columns = ["id", "question_id", "answer_id", "message", "submission_time", "edited_count"]
comments = data_manager.build_dict(rows, columns)
return comments
def get_answer(id):
"""
Return a single answer (dict) by its ID with authors name
"""
print(id)
answer = data_manager.run_query(
"""
SELECT answer.*, users.name
FROM answer
LEFT JOIN users ON answer.users_id = users.id
WHERE answer.id={};
""".format(id))
answer = data_manager.build_dict(answer, [
"answer_id",
"submission_time",
"vote_number",
"question_id",
"message",
"image",
"accepted",
"user_id",
"user_name"
])
return answer[0]
def edit_question_route(question_id):
"""Find question details from id and redirect with the data to /new_question page"""
title = "Modify question"
action = "/modify/" + question_id
query = """SELECT q.title, q.message, u.name FROM question q JOIN users u
ON (q.users_id = u.id)
WHERE q.id = '{}';""".format(question_id)
table = data_manager.run_query(query)
titles = "title", "message", "username"
data = data_manager.build_dict(table, titles)[0]
tags = common.show_tags_type()
tag_query = """SELECT tag.name
FROM tag
LEFT JOIN question_tag
ON tag.id=question_tag.tag_id
WHERE question_id = {0};""".format(question_id)
selected_tag = [i[0] for i in data_manager.run_query(tag_query)]
return render_template("new-question.html",
question_id=question_id,
title=title,
data=data,
action=action,
tags=tags,
selected_tag=selected_tag)
def displays_a_single_question(question_id):
list_of_key_of_question = [
"id", "submission_time", "view_number", "vote_number", "title",
"message"
]
title_of_question = [
"ID", "Submisson time", "View number", "Vote number", "Title",
"Message"
]
list_of_key_and_title_of_question = list(
zip(list_of_key_of_question, title_of_question))
list_of_key_of_answer = [
"answer_id", "submission_time", "vote_number", "question_id",
"message", "user_name"
]
title_of_answer = [
"ID", "Submisson time", "Vote number", "Question id", "Message",
"Author"
]
list_of_key_and_title_of_answers = list(
zip(list_of_key_of_answer, title_of_answer))
query = "SELECT * FROM question"
rows = data_manager.run_query(query)
list_of_names = [
"id", "submission_time", "view_number", "vote_number", "title",
"message"
]
all_question = data_manager.build_dict(rows, list_of_names)
for question_ in all_question:
if question_id == question_["id"]:
question = question_
break
query = "SELECT * FROM answer"
rows = data_manager.run_query(query)
list_of_names = [
"id", "submission_time", "vote_number", "question_id", "message"
]
all_answers = data_manager.build_dict(rows, list_of_names)
answers = []
for answer in all_answers:
if question_id == answer["question_id"]:
answers.append(answer)
tags = common.read_tags(question_id)
question_with_answers = {
"tags": tags,
"question_id": question_id,
"question": question,
"answers": answers,
"list_of_key_of_question": list_of_key_of_question,
"list_of_key_of_answer": list_of_key_of_answer,
"title_of_question": title_of_question,
"title_of_answer": title_of_answer,
"list_of_key_and_title_of_question": list_of_key_and_title_of_question,
"list_of_key_and_title_of_answers": list_of_key_and_title_of_answers
}
return question_with_answers
def show_tags_type():
list_of_keys_of_tag = ["id", "name", "color"]
query_tag = "SELECT id,name,color FROM tag ORDER BY id"
data = data_manager.run_query(query_tag)
tags_type = data_manager.build_dict(data, list_of_keys_of_tag)
return tags_type