def get_entity(entity_id: int):
q = """
SELECT *
FROM poet_entity e
WHERE e.id = %s AND e.release_state = %s"""
return u.query(q, [entity_id, PUBLISHED])[0]
def get_work(work_id: int):
q = """
SELECT {}
FROM poet_work w
WHERE w.id = %s AND w.release_state = %s""".format(REQUIRED_WORK_FIELDS)
return u.query(q, [work_id, PUBLISHED])[0]
def get_associated_collections(work_id: int):
q = """
SELECT c.id, c.collection_name
FROM poet_work_to_collection_rel crel
JOIN poet_work_collection c ON crel.to_collection = c.id
WHERE crel.from_work = %s"""
return u.query(q, [work_id])
def get_recordings(collection_id: int):
q = """
SELECT {}
FROM poet_work w
JOIN poet_work_to_collection_rel crel ON crel.from_work = w.id
JOIN poet_work_collection c ON c.id = crel.to_collection
WHERE crel.to_collection = %s
AND w.release_state = %s""".format(work.REQUIRED_WORK_FIELDS)
recordings = u.query(q, [collection_id, PUBLISHED])
return list(map(work.enrich_work, recordings))
def get_recordings(entity_id: int):
q = """
SELECT DISTINCT {}
FROM poet_entity_to_work_rel erel
JOIN poet_work w ON erel.to_work = w.id
JOIN poet_work_to_collection_rel crel ON crel.from_work = w.id
JOIN poet_work_collection c ON c.id = crel.to_collection
AND erel.from_entity = %s
AND w.release_state = %s""".format(work.REQUIRED_WORK_FIELDS)
recordings = u.query(q, [entity_id, PUBLISHED])
return list(map(work.enrich_work, recordings))
def get_entities(work_id: int) -> List[Dict[str, str]]:
q = """
SELECT DISTINCT
en.id entity_id,
join_words(en.full_name, en.alt_name) entity_name,
rel.relationship,
rel.instrument
FROM poet_entity_to_work_rel rel
JOIN poet_entity en ON en.id = rel.from_entity
WHERE rel.to_work = %s
AND release_state = %s
"""
return u.sort_entities(u.query(q, [work_id, PUBLISHED]))
def get_entities(collection_id: int) -> List[Dict[str, str]]:
q = """
SELECT DISTINCT
en.id entity_id,
join_words(en.full_name, en.alt_name) entity_name,
rel.relationship,
rel.instrument
FROM poet_entity_to_work_rel rel
JOIN poet_work_to_collection_rel crel ON crel.from_work = rel.to_work
JOIN poet_entity en ON en.id = rel.from_entity
JOIN poet_work pw ON rel.to_work = pw.id
WHERE crel.to_collection = %s
AND en.release_state = %s
"""
return u.sort_entities(u.query(q, [collection_id, PUBLISHED]))
def make_query(base_string: str, field_list: List[Tuple[str, str]], term: str):
"""
:param base_string: The string to query
:param field_list: list of fields to search and their respective search ranking
:param term: the string you're searching for
:return: List of matching dicts from search
"""
if term.strip() == '':
return []
terms = term.split()
query = make_field_search(len(terms))
text = make_tsvector(field_list)
q = base_string.format(fields=work.REQUIRED_WORK_FIELDS,
query=query,
text=text)
return u.query(q, terms)
def test_query(self):
res = u.query('SELECT * FROM poet_work WHERE id = %s',
[1])[0]['full_name']
self.assertEqual('La guerra es una locura extrema', res)
def get_collection(collection_id: int):
q = """SELECT * FROM poet_work_collection WHERE id = %s AND release_state = %s"""
return u.query(q, [collection_id, PUBLISHED])[0]