Как грамотно реализовать выборку из MySQL базы в такой ситуации?

Question

[Дмитрий]

Три таблицы:
- Продукты (products)
- Любимые продукты (favourites)
- Нелюбимые продукты (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);

Продукт имеет название(name), энергетическую ценность (kkal), id.
Любимые и нелюбимые продукты - связь многие-ко-многим с таблицей users, которую в данном примере опустил, так как в ней нет необходимости.

Задача:
реализовать выборку из продуктов, в которой сумма калорий не будет превышать определённое количество, при этом из выборки должны быть исключены нелюбимые продукты (unfavourites) и с большей вероятностью должны попасть любимые продукты (favourites). В примере используется юзер с id 150.

Что уже решено:
- из выборки исключаются нелюбимые продукты
- выборка ограниченна максимальной суммой калорий
- продукты, добавленные в избранное, в выборке помечаются приоритетом 1 (priority)
sqlfiddle.com/#!9/2c8af6/3

Наработки

-- Выборка продуктов:
--  - с указанием максимальной суммы каллорий
--  - исключением нелюбимых
--  - указанием приоритета для любимых
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;

Вопросы:
- можно ли как-то оптимизировать запрос в данной ситуации?
- как можно рандомизировать выборку? Предположим, пусть при первой попытке выпадает молоко + яблоко + курица + кофе, а при второй отбивная + молоко + кофе.

Может быть выбирать двойную/тройную норму калорий и на клиенте перемешивать результат для достижения случайного набора?

Answer 1

[longclaps]

SQL не годится для этой задачи.
Знаете пхп - пишите на пхп, вот псевдокод:

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

Учтите, если суммарная калорийность всех продуктов меньше лимита, код зациклится

Comments

[Дмитрий]

longclaps Что же в таком случае использовать?
Частично задача решилась на SQL, вынести оставшуюся логику в клиент (PHP)?

[longclaps]

Дмитрий: смотри ответ

[Дмитрий]

longclaps: Направление понятно. Спасибо.