How to correctly implement a selection from a MySQL database in such a situation?

Question

MySQL

Three tables:
- Products (products)
- Favorite products (favourites)
- Unloved products (unfavourites)

tables

-- ----------------------------
-- Table structure for `products`
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kkal` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES ('1', 10, 'Кофе');
INSERT INTO `products` VALUES ('2', 20, 'Молоко');
INSERT INTO `products` VALUES ('3', 44, 'Папайя');
INSERT INTO `products` VALUES ('4', 15, 'Яблоко');
INSERT INTO `products` VALUES ('5', 11, 'Манго');
INSERT INTO `products` VALUES ('6', 50, 'Курица');
INSERT INTO `products` VALUES ('7', 70, 'Отбивная');

-- ----------------------------
-- Table structure for `favourites`
-- ----------------------------
DROP TABLE IF EXISTS `favourites`;
CREATE TABLE `favourites` (
  `user_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`user_id`, `product_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of favourites
-- ----------------------------
INSERT INTO `favourites` VALUES (150, 2);
INSERT INTO `favourites` VALUES (150, 4);
INSERT INTO `favourites` VALUES (2, 1);

-- ----------------------------
-- Table structure for `unfavourites`
-- ----------------------------
DROP TABLE IF EXISTS `unfavourites`;
CREATE TABLE `unfavourites` (
  `user_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`user_id`, `product_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of unfavourites
-- ----------------------------
INSERT INTO `unfavourites` VALUES (150, 1);

The product has a name (name), energy value (kkal), id.
Liked and disliked products is a many-to-many relationship with the users table, which I omitted in this example because it's not needed.
Task:
to implement a sample of products in which the sum of calories will not exceed a certain amount, while unfavorable products (unfavourites) should be excluded from the sample and favorite foods (favourites) should be more likely to fall. The example uses a user with id 150.
What has already been resolved:
- unloved foods are excluded from the selection
- the selection is limited by the maximum amount of calories
- foods added to favorites are marked with priority 1 (priority) in the
selection sqlfiddle.com/#!9/2c8af6/3

Developments

-- Выборка продуктов:
--  - с указанием максимальной суммы каллорий
--  - исключением нелюбимых
--  - указанием приоритета для любимых
SELECT 
  (CASE WHEN f.user_id IS NOT NULL THEN 1 ELSE 0 END) AS `priority`, pa.name, pa.kkal 
  
  FROM products pa
  
  JOIN products pb ON pa.kkal > pb.kkal or (pa.kkal = pb.kkal and pa.name >= pb.name)
  LEFT JOIN favourites f ON f.product_id = pa.id AND f.user_id = 150
  LEFT JOIN unfavourites uf ON uf.product_id = pa.id AND uf.user_id = 150
  
  WHERE uf.user_id IS NULL
  
  GROUP BY pa.name
  
  HAVING SUM(pb.kkal) <= 100.0;

Questions:
- whether it is possible to optimize somehow request in the given situation?
How can the sample be randomized? Let's suppose that on the first try milk + apple + chicken + coffee falls out, and on the second try chop + milk + coffee.
Maybe choose a double / triple norm of calories and mix the result on the client to achieve a random set?

Answer 1

SQL is not up to the task.
You know php - write in php, here is the pseudocode:

любимые = [a, b, c]
нелюбимые = [d, e, f]
function menu(лимит){
    result = []
    while питательность(result) < лимит{
        if random() > 0.3 // число взято наобум
            продукт = случайный(любимые)
        else
            продукт = случайный(нелюбимые)
        if  продукт не в result
            добавить продукт в result
    }
    выбросить из result последнее
    return result
}

Please note that if the total calorie content of all products is less than the limit, the code will loop