Django - Создание модели, хранящей агрегированные значения внешних ключей
У меня есть Django Model, определенный как ServiceEvent, и другой, определенный как Part.
class ServiceEvent(Model):
date = DateTimeField(null=False, blank=False, db_column='date',
default=datetime.now())
vehicle = ForeignKey(Vehicle, on_delete=models.CASCADE)
description = TextField(null=True, blank=False, db_column='description')
# The contents of the notifications to create for this event.
notification_contents = TextField(null=True, blank=False,
db_column='notification_contents')
# The mileage the event should occur.
target_mileage = DecimalField(max_digits=10, decimal_places=2,
null=True, blank=False,
db_column='target_mileage')
# The mileage at the time of the event.
event_mileage = DecimalField(max_digits=10, decimal_places=2,
null=True, blank=False,
db_column='event_mileage')
class Part(Model):
part_number = CharField(max_length=100, null=False, blank=False,
db_column='part_number')
description = CharField(max_length=100, null=False, blank=False,
db_column='description')
price = DecimalField(max_digits=10, decimal_places=2, null=False,
db_column='price')
service_event = ForeignKey(ServiceEvent, on_delete=models.CASCADE)
На одного ServiceEvent может приходиться несколько Parts.
Каким образом можно сохранить в таблице ServiceEvent столбец, содержащий цену всех сложенных деталей, относящихся к данному событию?
Я хотел бы сделать это для эффективности.
Какой подход лучше (например, использование таблицы отображения, что может привести к тому же вопросу о столбце)?
Я использую SQLite.
Каков способ хранения столбца в таблице ServiceEvent, содержащего цену всех сложенных деталей, относящихся к данному событию?
Вы могли бы сделать это с помощью TRIGGER НО скорее всего в этом нет необходимости, так как sum цены могут быть сгенерированы/вычислены/выведены из существующих данных при извлечении данных.
Демонстрация
Вот демонстрация, основанная на схеме в соответствии с вашим кодом:-
/* Cleanup test environment (just in case)*/
DROP TABLE IF EXISTS part;
DROP TABLE IF EXISTS serviceevent;
DROP TABLE IF EXISTS vehicle;
CREATE TABLE IF NOT EXISTS vehicle (
description TEXT PRIMARY KEY,
otherData TEXT DEFAULT 'blah'
);
CREATE TABLE IF NOT EXISTS serviceevent (
date TEXT PRIMARY KEY DEFAULT CURRENT_TIMESTAMP,
description TEXT REFERENCES vehicle(description) ON DELETE CASCADE ON UPDATE CASCADE,
notification_contents TEXT,
target_mileage REAL,
event_mileage REAL
);
CREATE TABLE IF NOT EXISTS part (
part_number TEXT PRIMARY KEY,
description TEXT,
price REAL,
service_event TEXT REFERENCES serviceevent ON DELETE CASCADE ON UPDATE CASCADE
);
/* Add some vehicles */
INSERT INTO vehicle (description) VALUES ('CAR001'),('CAR002'),('CAR003');
/* Add some serviceevents for the vehicles */
INSERT INTO serviceevent VALUES
('2023-03-14 10:20:00','CAR001','10k Service',10000.00,9100.01),
('2024-03-13 11:00:00','CAR001','20k Service',20000.00,19100.01),
('2023-03-15 14:20:00','CAR002','10k Service',10000.00,9100.02),
('2024-03-16 15:00:00','CAR002','20k Service',20000.00,19100.02),
('2023-03-17 18:20:00','CAR003','10k Service',10000.00,9100.01),
('2024-03-18 19:00:00','CAR003','20k Service',20000.00,19100.01)
;
/* add some parts to service events (not all for brevity)*/
INSERT INTO part VALUES
/* 10k service for Car001 (i.e. serviceevent '2023-03-14 10:20:00')*/
('p001','Air Filter',25.68,'2023-03-14 10:20:00'),
('p002','Spark Plug Set',15.20,'2023-03-14 10:20:00'),
('p003','OIL (5L)',32.32,'2023-03-14 10:20:00'),
/* 10k service for Car002 (i.e. serviceevent '2023-03-15 14:20:00)*/
/* Note just the 2 parts */
('p004','Air Filter',25.68,'2023-03-15 14:20:00'),
('p005','Spark Plug Set',15.20,'2023-03-15 14:20:00')
;
SELECT
vehicle.*, /* include ALL of the vehicle columns in the output */
serviceevent.*, /* include ALL of the serviceevent columns in the output */
/* Use Aggregate functions to show all the parts and the sum of the prices */
group_concat(part.description),sum(part.price) AS total_parts_price
FROM vehicle
JOIN serviceevent ON vehicle.description = serviceevent.description
JOIN part ON part.service_event = serviceevent.date
GROUP BY service_event /* group the output insto sets per service event (to which the aggregate functions apply) */
;
/* Cleanup test environment (After run)*/
DROP TABLE IF EXISTS part;
DROP TABLE IF EXISTS serviceevent;
DROP TABLE IF EXISTS vehicle;
В результате получается:-
- При обслуживании 10k для CAR001, выполненном 14/03/2023 @ 10:20, были использованы детали (воздушный фильтр, масляный фильтр и масло) на общую сумму $73.20 .
- в 10-тысячном сервисе для Car002, выполненном 15/03/2023 @ 14:20, были/использованы детали (воздушный фильтр и масляный фильтр) на общую сумму $40.88
- т.е. ожидаемые результаты БЕЗ необходимости дополнительной колонки
Экстра
Вы даже можете сделать что-то вроде следующего:-
WITH cte AS (
SELECT
'Service for '||vehicle.description||
' done on '||serviceevent.date||
' re '||serviceevent.notification_contents||' ('||serviceevent.target_mileage||' miles)'||
' @ '||serviceevent.event_mileage||' miles.' AS printline,
1 AS printorder,
serviceevent.date
FROM vehicle
JOIN serviceevent ON vehicle.description = serviceevent.description
UNION ALL SELECT
'\t'||'Part '||part.description||' Cost $'||part.price,
2,
serviceevent.date
FROM serviceevent JOIN part ON part.service_event = serviceevent.date
UNION ALL SELECT
'\t\tTotal Parts Cost $'||sum(part.price),3,part.service_event
FROM serviceevent JOIN PART ON part.service_event = serviceevent.date
GROUP BY service_event
)
SELECT printline FROM cte ORDER BY date,printorder
;
Который создаст грубый счет-фактуру с подробным указанием деталей и общей суммы:-
- снова показывает, что столбцу не нужно хранить значение, которое может быть получено из базовых данных.