47 lines
1.8 KiB
Python
47 lines
1.8 KiB
Python
class ToyModel:
|
|
def __init__(self, database: "Database"):
|
|
self.db = database
|
|
|
|
def get_all(self):
|
|
sql = """
|
|
SELECT t.toy_name, c.category_name, m.manufacturer_name,
|
|
STRING_AGG(DISTINCT ag.age_label, ', ') AS ages,
|
|
STRING_AGG(DISTINCT s.supplier_name, ', ') AS suppliers,
|
|
t.price, t.discount, st.quantity
|
|
FROM toys t
|
|
JOIN categories c ON t.category_id = c.category_id
|
|
JOIN manufacturers m ON t.manufacturer_id = m.manufacturer_id
|
|
LEFT JOIN toy_age_groups tag ON t.toy_id = tag.toy_id
|
|
LEFT JOIN age_groups ag ON tag.age_group_id = ag.age_group_id
|
|
LEFT JOIN toy_suppliers ts ON t.toy_id = ts.toy_id
|
|
LEFT JOIN suppliers s ON ts.supplier_id = s.supplier_id
|
|
LEFT JOIN stock st ON t.toy_id = st.toy_id
|
|
GROUP BY t.toy_id, c.category_name, m.manufacturer_name, t.price, t.discount, st.quantity
|
|
ORDER BY t.toy_id
|
|
"""
|
|
rows = self.db.query(sql)
|
|
toys = []
|
|
for r in rows:
|
|
toys.append({
|
|
"toy_name": r[0],
|
|
"category_name": r[1],
|
|
"manufacturer_name": r[2],
|
|
"ages": r[3] or "",
|
|
"suppliers": r[4] or "",
|
|
"price": float(r[5]),
|
|
"discount": int(r[6]),
|
|
"quantity": int(r[7] or 0),
|
|
})
|
|
return toys
|
|
|
|
def sort_by_price(self, ascending=True):
|
|
toys = self.get_all()
|
|
return sorted(toys, key=lambda x: x["price"], reverse=not ascending)
|
|
|
|
def get_age_groups(self):
|
|
rows = self.db.query("SELECT age_label FROM age_groups ORDER BY age_group_id")
|
|
return [r[0] for r in rows]
|
|
|
|
def get_suppliers(self):
|
|
rows = self.db.query("SELECT supplier_name FROM suppliers ORDER BY supplier_id")
|
|
return [r[0] for r in rows]
|