def user_sort(condition, user_id):
condition_list = ["submission_time ASC", "submission_time DESC", "view_number ASC",
"view_number DESC", "vote_number ASC", "vote_number DESC"]
if condition in condition_list:
return common.query_handler("SELECT * FROM question WHERE users_id=%s ORDER BY " + condition, (user_id,))
else:
return common.query_handler("SELECT * FROM question WHERE users_id=%s ORDER BY submission_time ASC", (user_id,))
def sort_by_condition(condition):
condition_list = ["submission_time ASC", "submission_time DESC", "view_number ASC",
"view_number DESC", "vote_number ASC", "vote_number DESC"]
if condition in condition_list:
return common.query_handler("""SELECT question.id, title, message, user_name, question.submission_time,
view_number, vote_number, image FROM question LEFT JOIN users
ON users.id=users_id ORDER BY """ + condition)
else:
return common.query_handler("""SELECT question.id, title, message, user_name, question.submission_time,
view_number, vote_number, image
FROM question LEFT JOIN users ON users.id=users_id
ORDER BY submission_time ASC""")
def comment_question_answer_to_userid(user_id):
return common.query_handler("""SELECT comment.question_id AS questid, comment.submission_time, comment.message,
answer.message AS ansme, question.title AS quest, answer.question_id
FROM comment
LEFT JOIN question ON comment.question_id=question.id
LEFT JOIN answer ON comment.answer_id=answer.id
WHERE comment.users_id=%s""", (user_id,))
def search(replacement):
return common.query_handler("""SELECT DISTINCT question.id, question.users_id, user_name,
question.submission_time, question.view_number,
question.vote_number, question.image, answer.question_id,
Replace(question.title, %(search)s, %(marks)s) AS title,
Replace(question.message, %(search)s, %(marks)s) AS message
FROM question
LEFT JOIN users ON question.users_id=users.id
FULL JOIN answer ON question.id = answer.question_id
WHERE question.title ILIKE '%%' || %(search)s || '%%'
OR answer.message ILIKE '%%' || %(search)s || '%%'
OR question.message ILIKE '%%' || %(search)s || '%%' ;""",replacement)
def question_to_userid(user_id):
return common.query_handler("""SELECT * FROM question
WHERE users_id=%s""", (user_id,))
def sort_users(condition):
condition_list = ["user_name ASC", "user_name DESC"]
if condition in condition_list:
return common.query_handler("SELECT * FROM users ORDER BY " + condition)
else:
return common.query_handler("""SELECT * FROM users ORDER BY user_name ASC;""")
def question_to_comment(comment_id):
return common.query_handler("SELECT question_id, answer_id FROM comment WHERE id=%s", (int(comment_id),))
def index():
return common.query_handler("""SELECT question.id, question.submission_time, view_number,
vote_number, title, message, image, users_id, user_name, reputation
FROM question
LEFT JOIN users ON users.id=users_id
ORDER BY question.submission_time DESC LIMIT 5;""")
def update_question(formdata):
common.query_handler("""UPDATE question SET title=%s, message=%s, image=%s WHERE id=%s;""",
(formdata["title"], formdata["Question"], formdata["image"],
formdata["question_id"]))
def insert_users(current_user):
common.query_handler("INSERT INTO users (submission_time, user_name, reputation) \
VALUES (%s, %s, %s)",
(datetime.now().replace(microsecond=0), current_user, 0))
def questionid_to_answer_from_dict(question):
return common.query_handler("SELECT question_id FROM answer WHERE id=%s", (question[0]["answer_id"],))
def delete_tag(questionid, tag_id):
common.query_handler("DELETE FROM question_tag WHERE question_id=%s AND tag_id=%s", (questionid, tag_id))
def insert_tag_to_question(formdata, tagid):
common.query_handler("""INSERT INTO question_tag (question_id, tag_id)
VALUES(%s, %s)""", (formdata['question_id'], tagid[0]['id']))
def insert_tag_name(formdata):
common.query_handler("""INSERT INTO tag (name) VALUES(%s)""", (formdata['Tag'],))
def tag_by_id(formdata, tagkey):
return common.query_handler('SELECT * FROM tag WHERE name=%s', (formdata[tagkey],))
def all_users():
return common.query_handler("SELECT * FROM users;")
def select_all_tags():
return common.query_handler("SELECT * FROM tag")
def answer_question_to_userid(user_id):
return common.query_handler("""SELECT answer.submission_time, answer.vote_number,answer.question_id,
answer.message, answer.image, question.title AS quest
FROM answer
JOIN question ON question_id=question.id
WHERE answer.users_id=%s""", (user_id,))
def tags():
return common.query_handler("SELECT name, COUNT(question_id) AS question_number \
FROM tag JOIN question_tag ON tag.id=question_tag.tag_id \
GROUP BY name")
def delete_comment(comment_id):
common.query_handler("DELETE FROM comment WHERE id=%s", (comment_id,))
def question_user():
return common.query_handler("""SELECT question.id, title, message, user_name, question.submission_time, view_number,
vote_number, image FROM question LEFT JOIN users ON users.id=users_id;""")
def answer_to_userid(user_id):
return common.query_handler("""SELECT * FROM answer
WHERE users_id=%s""", (user_id,))
def usernames():
return common.query_handler("SELECT user_name FROM users")
def viewcount(questionid):
return common.query_handler("UPDATE question SET view_number = view_number + 1 WHERE id=%s", (questionid,))
def username_id():
return common.query_handler("""SELECT id, user_name, id FROM users;""")
def comment_to_userid(user_id):
return common.query_handler("""SELECT * FROM comment
WHERE users_id=%s""", (user_id,))
def new_question(formdata):
common.query_handler("""INSERT INTO question (submission_time, view_number, vote_number, title, message, image, users_id)
VALUES(%s,%s,%s,%s,%s,%s,%s);""",
(datetime.now().replace(microsecond=0), 0, 0, formdata["title"], formdata["Question"],
formdata["image"], formdata["user"]))
def user_by_id(user_id):
return common.query_handler("""SELECT * FROM users
WHERE id=%s""", (user_id,))
def select_question(questionid):
return common.query_handler("""SELECT question.id, question.submission_time, view_number, vote_number, title, message, image, user_name
FROM question LEFT JOIN users ON users.id=users_id
WHERE question.id=%s""", (questionid,))
def comment_user(comment_id):
return common.query_handler("""SELECT comment.id, question_id, answer_id, message, comment.submission_time, edited_count, users_id, user_name
FROM comment LEFT JOIN users
ON users_id=users.id
WHERE comment.id=%s""", (comment_id,))
