def get_boards(cursor: RealDictCursor):
query = """
SELECT *
FROM boards
ORDER BY id;
"""
cursor.execute(query)
boards = cursor.fetchall()
return boards
def get_cards_from_board(cursor: RealDictCursor, board_id):
query = """
SELECT *
FROM cards
WHERE cards.board_id = %(board_id)s
ORDER BY id;
"""
cursor.execute(query, {'board_id': board_id})
return cursor.fetchall()
def add_new_board(cursor: RealDictCursor, new_board_title):
query = """
INSERT INTO boards(title)
VALUES (%(new_board_title)s)
returning id, title;
"""
cursor.execute(query, {'new_board_title': new_board_title})
new_board_id = cursor.fetchall()
return new_board_id
def get_statuses(cursor: RealDictCursor):
query = """
SELECT *
FROM statuses
ORDER BY id;
"""
cursor.execute(query)
statuses = cursor.fetchall()
return statuses
def get_questions_of_user(cursor: RealDictCursor, user_id):
query = """
SELECT qst.* FROM question as qst
INNER JOIN users_activity as act
ON qst.id = act.question_id
WHERE act.user_id = %(user_id)s
"""
cursor.execute(query, {'user_id': user_id})
return cursor.fetchall()
def get_answers_of_user(cursor: RealDictCursor, user_id):
query = """
SELECT ans.* FROM answer as ans
INNER JOIN users_activity as act
ON ans.id = act.answer_id
WHERE act.user_id = %(user_id)s
"""
cursor.execute(query, {'user_id': user_id})
return cursor.fetchall()
def get_comments_of_user(cursor: RealDictCursor, user_id):
query = """
SELECT com.* FROM comment as com
INNER JOIN users_activity as act
ON com.id = act.comment_id
WHERE act.user_id = %(user_id)s
"""
cursor.execute(query, {'user_id': user_id})
return cursor.fetchall()
def get_mentors_by_city(cursor: RealDictCursor, city_name: str) -> list:
query = f"""
SELECT first_name, last_name, city
FROM mentor
WHERE city
LIKE '{city_name}'
ORDER BY first_name
"""
cursor.execute(query)
return cursor.fetchall()
def get_application_code_by_id(cursor: RealDictCursor,
applicant_id: int) -> list:
query = """
SELECT application_code
FROM applicant
WHERE id = %s
"""
args = applicant_id
cursor.execute(query, (args, ))
return cursor.fetchall()
def planets_votes(cursor: RealDictCursor, user_id: int) -> list:
query = """
SELECT pv.planet_name planet, COUNT(pv.planet_name) AS count, u.id, pv.user_id
FROM planet_votes AS pv
LEFT JOIN users u ON pv.user_id = u.id
WHERE u.id = %(user_id)s
GROUP BY planet, u.id, pv.user_id
"""
args = {'user_id': user_id}
cursor.execute(query, args)
return cursor.fetchall()
def greatest_answer_id(cursor: RealDictCursor):
query = """
SELECT id
FROM answer
ORDER BY id DESC
LIMIT 1
"""
cursor.execute(query)
result = cursor.fetchall()
return result[0]['id']
def get_boards_data(cursor: RealDictCursor) -> list:
"""Get id of last added record"""
query = """
SELECT *
FROM boards
"""
cursor.execute(query)
return cursor.fetchall()
def get_table_question(cursor: RealDictCursor) -> list:
"""
:return:
all data from table question
"""
query = """
SELECT *
FROM question
"""
cursor.execute(query)
return cursor.fetchall()
def comments_for_question_id(cursor: RealDictCursor, user_id: int) -> list:
query = """
SELECT comment.*, question.id, question.title
FROM comment
LEFT JOIN question
ON comment.question_id = question.id
WHERE comment.user_id = %(user_id)s AND comment.answer_id IS NULL
"""
args = {'user_id': user_id}
cursor.execute(query, args)
return cursor.fetchall()
def answers_for_question_id(cursor: RealDictCursor, user_id: int) -> list:
query = """
SELECT answer.*, question.id, question.title
FROM answer
LEFT JOIN question
ON answer.question_id = question.id
WHERE answer.user_id = %(user_id)s
"""
args = {'user_id': user_id}
cursor.execute(query, args)
return cursor.fetchall()
def rename_board(cursor: RealDictCursor, board_id, new_title):
query = """
UPDATE boards
SET title = %(new_title)s
WHERE boards.id = %(board_id)s
returning title
"""
cursor.execute(query, {'new_title': new_title, 'board_id': board_id})
new_board_title_content = cursor.fetchall()
return new_board_title_content
def get_applicant_data_by_email_ending(cursor: RealDictCursor,
email_ending: str) -> list:
query = """
SELECT first_name, last_name, phone_number
FROM applicant
WHERE email
LIKE %s
ORDER BY first_name"""
name_ap = "%" + email_ending
cursor.execute(query, (name_ap, ))
return cursor.fetchall()
def get_all_data(cursor: RealDictCursor) -> list:
query = """
SELECT id, submission_time as post_time, title, message, view_number as views, vote_number as votes, image
FROM question
ORDER BY submission_time DESC"""
cursor.execute(query)
questions = cursor.fetchall()
add_answer_snippets(questions)
return questions
def get_comments_by_question_id(cursor: RealDictCursor, question_id: int):
query = f"""
SELECT comment.*,
forum_user.id as forum_user_id, forum_user.mail as user_mail,
forum_user.reputation as reputation
FROM comment
LEFT JOIN forum_user ON comment.user_id = forum_user.id
WHERE comment.question_id = {question_id}
ORDER BY submission_time DESC"""
cursor.execute(query)
return cursor.fetchall()
def get_applicant_data_by_email(cursor: RealDictCursor,
email_ending: str) -> list:
query = """
SELECT first_name, last_name, phone_number
FROM applicant
WHERE email
LIKE %s
"""
args = '%' + email_ending
cursor.execute(query, (args, ))
return cursor.fetchall()
def get_mentors_by_last_name(cursor: RealDictCursor, last_name: str) -> list:
query = """
SELECT first_name, last_name, city
FROM mentor
WHERE last_name
LIKE %s
ORDER BY first_name
"""
args = last_name
cursor.execute(query, (args, ))
return cursor.fetchall()
def get_data_statuses(cursor: RealDictCursor) -> list:
"""
:return:
all data from statuses table
"""
query = """
SELECT *
FROM statuses
"""
cursor.execute(query)
return cursor.fetchall()
def get_questions_by_tag(cursor: RealDictCursor, tag_id):
query = f"""
select q.title, q.message, tag.name, q.id
from question as q
left join question_tag as qt on q.id = qt.question_id
join tag ON tag.id = qt.tag_id
where tag.id = {tag_id}
group by q.title, q.message, tag.name, q.id
"""
cursor.execute(query)
return cursor.fetchall()
def get_table_comment(cursor: RealDictCursor) -> list:
"""
:return:
all data from table comment
"""
query = """
SELECT *
FROM comment
"""
cursor.execute(query)
return cursor.fetchall()
def rename_card(cursor: RealDictCursor, card_id, new_title):
query = """
UPDATE cards
SET title = %(new_title)s
WHERE cards.id = %(card_id)s
returning title
"""
cursor.execute(query, {'card_id': card_id, 'new_title': new_title})
new_card_title = cursor.fetchall()
return new_card_title
def get_sorted_questions(cursor: RealDictCursor, criteria_and_direction):
if criteria_and_direction[
0] in QUESTION_HEADERS and criteria_and_direction[1] in [
'ASC', 'DESC'
]:
cursor.execute(f"""
SELECT *
FROM question
ORDER BY {criteria_and_direction[0]} {criteria_and_direction[1]};
""")
return cursor.fetchall()
def get_questions_with_specific_tag(cursor: RealDictCursor, tag: str):
cursor.execute(
"""
SELECT *
FROM question
LEFT JOIN question_tag
ON question.id = question_tag.question_id
LEFT JOIN tag
ON question_tag.tag_id = tag.id
WHERE tag.name = %(tag)s;
""", {'tag': tag})
return cursor.fetchall()
def search_for_phrase_questions(cursor: RealDictCursor, search_phrase: str):
cursor.execute(
f"""
SELECT DISTINCT question.*
FROM question
FULL OUTER JOIN answer
ON question.id = answer.question_id
WHERE question.message ILIKE %(phrase)s
OR question.title ILIKE %(phrase)s
OR answer.message ILIKE %(phrase)s;
""", {'phrase': '%' + search_phrase + '%'})
return cursor.fetchall()
def get_phrase_match_data(cursor: RealDictCursor, phrase: str) -> dict:
question_query = """
SELECT question.id, question.submission_time, question.view_number, question.vote_number, question.title,
question.message, question.image
FROM question
FULL JOIN answer ON answer.question_id = question.id
WHERE question.title LIKE %(phrase)s
OR question.message LIKE %(phrase)s
OR answer.message LIKE %(phrase)s"""
cursor.execute(question_query, {'phrase': '%' + phrase + '%'})
data = remove_duplicates(cursor.fetchall(), 'id')
return data
def get_statuses(cursor: RealDictCursor):
query = """
SELECT * FROM status
ORDER BY id
"""
cursor.execute(query)
dictStatusesList = []
for entry in cursor.fetchall():
dictStatusesList.append(dict(entry))
return dictStatusesList
