68 lines
1.9 KiB
Python
68 lines
1.9 KiB
Python
#!/usr/bin/env python3
|
||
"""Модуль для работы с сериями"""
|
||
from db_utils import get_cursor
|
||
|
||
|
||
def get_series_by_letter(letter=''):
|
||
"""Возвращает серии по первой букве"""
|
||
conn, cur = get_cursor()
|
||
|
||
if letter:
|
||
cur.execute("""
|
||
SELECT s.id, s.name, s.name_hash,
|
||
COUNT(DISTINCT b.id) as books_count
|
||
FROM series s
|
||
LEFT JOIN books b ON b.series_id = s.id
|
||
WHERE s.name ILIKE %s
|
||
GROUP BY s.id, s.name, s.name_hash
|
||
ORDER BY s.name
|
||
LIMIT 100
|
||
""", (letter + '%',))
|
||
else:
|
||
cur.execute("""
|
||
SELECT DISTINCT lower(substring(name, 1, 1)) as letter,
|
||
COUNT(*) as cnt
|
||
FROM series
|
||
WHERE name ~ '^[а-яА-Яa-zA-Z]'
|
||
GROUP BY lower(substring(name, 1, 1))
|
||
ORDER BY letter
|
||
""")
|
||
|
||
result = cur.fetchall()
|
||
conn.close()
|
||
return result
|
||
|
||
|
||
def get_series_by_hash(series_hash):
|
||
"""Возвращает серию по хешу"""
|
||
conn, cur = get_cursor()
|
||
|
||
cur.execute("""
|
||
SELECT id, name, name_hash
|
||
FROM series
|
||
WHERE name_hash = %s
|
||
""", (series_hash,))
|
||
|
||
series = cur.fetchone()
|
||
conn.close()
|
||
return series
|
||
|
||
|
||
def get_series_books(series_id):
|
||
"""Возвращает все книги серии"""
|
||
conn, cur = get_cursor()
|
||
|
||
cur.execute("""
|
||
SELECT b.id, b.title, b.language, b.series_number,
|
||
string_agg(DISTINCT a.full_name, ', ' ORDER BY ba.position) as authors
|
||
FROM books b
|
||
JOIN book_authors ba ON ba.book_id = b.id
|
||
JOIN authors a ON a.id = ba.author_id
|
||
WHERE b.series_id = %s
|
||
GROUP BY b.id, b.title, b.language, b.series_number
|
||
ORDER BY b.series_number NULLS LAST, b.title
|
||
""", (series_id,))
|
||
|
||
books = cur.fetchall()
|
||
conn.close()
|
||
return books |