Блог

У меня есть 2 таблицы

 Item (ID, name, logo, description, categoryID)
Review (ID, item_id, name, body, rating)
 

Я хочу добавить производный атрибут overall_ratinq в item таблицу

Я знаю, что мне нужно получить среднюю оценку по идентификатору, но каждый синтаксис, который я делаю, не работает.

Я уже создал таблицы.

Кто-нибудь может помочь?

Функция сгенерированного столбца MySQL не поддерживает подзапросы или вызовы хранимых функций. Я не думаю, что есть какой-либо способ заставить сгенерированный столбец включать значения из другой таблицы.

Вы могли бы сделать это с помощью триггера. То есть добавьте Item в таблицу реальный столбец:

 ALTER TABLE Item ADD COLUMN overall_rating INT NOT NULL DEFAULT 0;
 

Затем используйте триггеры для ВСТАВКИ и ОБНОВЛЕНИЯ Review таблицы, чтобы постепенно корректировать сохраненное значение в таблице элементов.

 CREATE TRIGGER review_ins AFTER INSERT ON Review
FOR EACH ROW
  UPDATE Item SET overall_rating = overall_rating   NEW.rating
  WHERE ID = NEW.item_id;

CREATE TRIGGER review_upd AFTER UPDATE ON Review
FOR EACH ROW
  UPDATE Item SET overall_rating = overall_rating - OLD.rating   NEW.rating
  WHERE ID = NEW.item_id;
 

Я не проверял эти примеры. Я оставлю это на ваше усмотрение, чтобы вы протестировали и модифицировали его в соответствии с вашими потребностями. Вероятно, есть крайние случаи, которые он не обрабатывает, например, что, если ОБНОВЛЕНИЕ изменило item_id ?

Обратите также внимание, что это приведет к блокировке строк в Item таблице при публикации отзывов.

Если вы не хотите этого делать, то используйте представление, чтобы вычислить общую оценку по требованию:

 CREATE VIEW ItemsWithRatings AS
  SELECT ID, name, logo, description, categoryID, 
    SUM(Review.rating) AS overall_rating
  FROM Item JOIN Review ON Review.item_id=Item.ID
  GROUP BY Item.ID;