How to use GENERATED from SELECT with JOIN?

Question

This is my database. FoodItems is just normal table but I want to use virtual columns to generate all the related data, e.g. Servings.kcal is equal to related (FieldItem.kcal*unit)/100 (the data is specified in kilocalories per 100g/100ml):

CREATE TABLE IF NOT EXISTS `DietDB`.`Servings` (
      `id` INT UNSIGNED NOT NULL,
      `FoodItem_id` INT UNSIGNED NOT NULL,
      `name` VARCHAR(255) NOT NULL,
      `units` DOUBLE UNSIGNED NULL,
      `kcal` INT GENERATED ALWAYS AS (SELECT (FoodItems.kcal*units)/100 FROM Servings JOIN FoodItems ON Servings.FoodItem_id = FoodItems.id) VIRTUAL,
      `carbohydrate` DOUBLE UNSIGNED NULL,
      `carbohydrate_sugar` DOUBLE UNSIGNED NULL,
      `fat` DOUBLE UNSIGNED NULL,
      `fat_saturated` DOUBLE UNSIGNED NULL,
      `fat_trans` DOUBLE UNSIGNED NULL,
      `protein` DOUBLE UNSIGNED NULL,
      `fibre` DOUBLE UNSIGNED NULL,
      PRIMARY KEY (`id`, `FoodItem_id`),
      INDEX `fk_Servings_FoodItems1_idx` (`FoodItem_id` ASC),
      UNIQUE INDEX `name_UNIQUE` (`name` ASC),
      CONSTRAINT `fk_Servings_FoodItems1`
        FOREIGN KEY (`FoodItem_id`)
        REFERENCES `DietDB`.`FoodItems` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB

This is obviously wrong as the query returns the whole table, not single value and it yells an error... but it presents what I'm trying to achieve there. How could it be done?

Answer 1

According to the MySQL documentation, GENERATED columns cannot have subqueries:

Generated column expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.

• Literals, deterministic built-in functions, and operators are permitted. A function is deterministic if, given the same data in tables, multiple invocations produce the same result, independently of the connected user. Examples of functions that fail this definition: CONNECTION_ID(), CURRENT_USER(), NOW().

• Subqueries, parameters, variables, stored functions, and user-defined functions are not permitted.

• ...

---

What you could do, is use a view:

CREATE VIEW DietDB.Servings_All        -- pick an appropriate name
AS 
SELECT s.*,
       CAST((fi.kcal * s.units) / 100 AS UNSIGNED INT) AS kcal 
FROM DietDB.Servings AS s 
  LEFT JOIN DietDB.FoodItems AS fi 
    ON s.FoodItem_id = fi.id ;