Блог

Вот проблема / проблема SQL (MySQL или MariaDB), которую я должен решить. Мне удалось решить ее, однако она не оптимальна, и с большим количеством данных она не будет работать хорошо. Я не очень хорошо разбираюсь в SQL.

Вот почему, не найдя других альтернатив на данный момент, я решаю обратиться к коллективному разуму.

Презентация: у нас есть пользователи, категории элементов, элементы и подпункты. Каждый элемент, находящийся в категории и категории, может содержать несколько элементов (ManyToOne). Каждый подпункт соответствует элементу, а элемент может иметь несколько подпунктов (ManyToOne). И, наконец, у каждого пользователя может быть один или несколько подпунктов, и у каждого подпункта может быть один или несколько пользователей (ManyToMany). Для отношения ManyToMany между пользователями и подразделами существует таблица, имеющая в качестве первичного ключа идентификатор пользователя и идентификатор подраздела.

Вот диаграмма UML (проще): Диаграмма базы данных UML

/! Предупреждение /! Обратите внимание: этот дизайн может быть полностью изменен для решения этой проблемы.

Вот цель:

1. Отображение общего количества подпунктов по категориям и в целом между данным пользователем и другими.

Пример результатов (здесь мы показываем количество подпунктов, общих для пользователя 1):

  ---------- ------------ ------------ ------------ ------- 
| user_id  | category_1 | category_2 | category_3 | total |
 ---------- ------------ ------------ ------------ ------- 
|     2    |     2      |     3      |     0      |   5   |
|     3    |     0      |     1      |     1      |   2   |
 ---------- ------------ ------------ ------------ ------- 
 

Как мне получить этот результат? У вас есть идея? Другой способ сделать это?
Для решения этой проблемы хороши все средства, все, что позволяет MySQL или MariaDB (создайте другой дизайн, используйте представления, временные таблицы, хранимые процедуры, виртуальные столбцы и т. Д.).
Все идеи приветствуются. Цель состоит в том, чтобы найти решение, которое может обрабатывать большой объем информации.
Заранее большое спасибо, искренне.

PS: Если вы хотите, вот SQL, соответствующий приведенной выше диаграмме UML :

 CREATE TABLE IF NOT EXISTS `items_categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `label` VARCHAR(45) NOT NULL,
  `slug` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
  UNIQUE INDEX `label_UNIQUE` (`label` ASC) VISIBLE,
  UNIQUE INDEX `slug_UNIQUE` (`slug` ASC) VISIBLE);

CREATE TABLE IF NOT EXISTS `items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `label` VARCHAR(45) NOT NULL,
  `slug` VARCHAR(45) NOT NULL,
  `items_categories_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
  UNIQUE INDEX `label_UNIQUE` (`label` ASC) VISIBLE,
  UNIQUE INDEX `slug_UNIQUE` (`slug` ASC) VISIBLE,
  INDEX `fk_items_items_categories_idx` (`items_categories_id` ASC) VISIBLE,
  CONSTRAINT `fk_items_items_categories`
    FOREIGN KEY (`items_categories_id`)
    REFERENCES `items_categories` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE IF NOT EXISTS `subitems` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `label` VARCHAR(45) NOT NULL,
  `slug` VARCHAR(45) NOT NULL,
  `items_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
  UNIQUE INDEX `label_UNIQUE` (`label` ASC) VISIBLE,
  UNIQUE INDEX `slug_UNIQUE` (`slug` ASC) VISIBLE,
  INDEX `fk_subitems_items1_idx` (`items_id` ASC) VISIBLE,
  CONSTRAINT `fk_subitems_items1`
    FOREIGN KEY (`items_id`)
    REFERENCES `items` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
  UNIQUE INDEX `username_UNIQUE` (`username` ASC) VISIBLE,
  UNIQUE INDEX `email_UNIQUE` (`email` ASC) VISIBLE);

CREATE TABLE IF NOT EXISTS `users_has_subitems` (
  `users_id` INT UNSIGNED NOT NULL,
  `subitems_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`users_id`, `subitems_id`),
  INDEX `fk_users_has_subitems_subitems1_idx` (`subitems_id` ASC) VISIBLE,
  INDEX `fk_users_has_subitems_users1_idx` (`users_id` ASC) VISIBLE,
  CONSTRAINT `fk_users_has_subitems_users1`
    FOREIGN KEY (`users_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_users_has_subitems_subitems1`
    FOREIGN KEY (`subitems_id`)
    REFERENCES `subitems` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
 

Edit :

I’ve been asked to show my SQL queries, I’ve done a lot of different ones, but here is the one that gives the result closest to what I need :

I started by creating a view that adds the id categories to the users subitems :

 CREATE VIEW 'users_has_subitems_categories' AS
SELECT 
    'uhs'.'user_id',
    'uhs'.'subitems_id',
    'ic'.'id' AS 'items_categories_id'
FROM
    ((('users_has_subitems' 'uhs'
    LEFT JOIN 'subitems' 'si' ON (('si'.'id' = 'uhs'.'subitems_id')))
    LEFT JOIN 'items' 'i' ON (('i'.'id' = 'si'.'items_id')))
    LEFT JOIN 'items_categories' 'ic' ON (('ic'.'id' = 'i'.'items_categories_id')))
 

Then I wrote SQL queries allowing me to count the total number of subitems in common for a given user (here user 1). Then the same query with the view to count the total number of subitems for the category and the given user (here category 2, user 1).

 SELECT u.id AS user_id, COUNT(uhs.subitems_id) AS total
FROM 'user'AS u
LEFT JOIN 'users_has_subitems' AS uhs
    ON uhs.user_id = u.id
    AND uhs.subitems_id IN (
        SELECT uhs2.subitems_id FROM 'users_has_subitems' AS uhs2 WHERE uhs2.user_id = 1
    )
WHERE u.id <> 1
GROUP BY u.id;

SELECT u.id AS user_id, COUNT(uhs.subitems_id) AS in_common
FROM 'user'AS u
LEFT JOIN 'users_has_subitems_categories' AS uhs
    ON uhs.user_id = u.id
    AND uhs.items_categories_id = 2
    AND uhs.subitems_id IN (
        SELECT uhs2.subitems_id FROM 'users_has_subitems' AS uhs2 WHERE uhs2.user_id = 1
    )
WHERE u.id <> 1
GROUP BY u.id;
 

Finally I used his SQL queries to join them with the user list.

 SELECT
    u.id AS user_id,
    uhst1.in_common AS 'category_1',
    uhst2.in_common AS 'category_2',
    uhst3.in_common AS 'category_3',
    uhst.total AS total
FROM 'user' AS u
LEFT JOIN (
    SELECT u.id AS user_id, COUNT(uhs.subitems_id) AS total
    FROM 'user'AS u
    LEFT JOIN 'users_has_subitems' AS uhs
        ON uhs.user_id = u.id
        AND uhs.subitems_id IN (
            SELECT uhs2.subitems_id FROM 'users_has_subitems' AS uhs2 WHERE uhs2.user_id = 1
        )
    WHERE u.id <> 1
    GROUP BY u.id
) uhst ON uhst.user_id = u.id
LEFT JOIN (
    SELECT u.id AS user_id, COUNT(uhs.subitems_id) AS in_common
    FROM 'user'AS u
    LEFT JOIN 'users_has_subitems_categories' AS uhs
        ON uhs.user_id = u.id
        AND uhs.items_categories_id = 1
        AND uhs.subitems_id IN (
            SELECT uhs2.subitems_id FROM 'users_has_subitems' AS uhs2 WHERE uhs2.user_id = 1
        )
    WHERE u.id <> 1
    GROUP BY u.id
) uhst1 ON uhst1.user_id = u.id
LEFT JOIN (
    SELECT u.id AS user_id, COUNT(uhs.subitems_id) AS in_common
    FROM 'user'AS u
    LEFT JOIN 'users_has_subitems_categories' AS uhs
        ON uhs.user_id = u.id
        AND uhs.items_categories_id = 2
        AND uhs.subitems_id IN (
            SELECT uhs2.subitems_id FROM 'users_has_subitems' AS uhs2 WHERE uhs2.user_id = 1
        )
    WHERE u.id <> 1
    GROUP BY u.id
) uhst2 ON uhst2.user_id = u.id
LEFT JOIN (
    SELECT u.id AS user_id, COUNT(uhs.subitems_id) AS in_common
    FROM 'user'AS u
    LEFT JOIN 'users_has_subitems_categories' AS uhs
        ON uhs.user_id = u.id
        AND uhs.items_categories_id = 3
        AND uhs.subitems_id IN (
            SELECT uhs2.subitems_id FROM 'users_has_subitems' AS uhs2 WHERE uhs2.user_id = 1
        )
    WHERE u.id <> 1
    GROUP BY u.id
) uhst3 ON uhst3.user_id = u.id
WHERE u.id <> 1
ORDER BY total DESC;
 

Это дает мне следующие результаты:

  ---------- ------------ ------------ ------------ ------- 
| user_id  | category_1 | category_2 | category_3 | total |
 ---------- ------------ ------------ ------------ ------- 
|     2    |     2      |     1      |     1      |   4   |
|     5    |     1      |     0      |     1      |   2   |
|     3    |     0      |     0      |     0      |   0   |
|     4    |     0      |     0      |     0      |   0   |
|     6    |     0      |     0      |     0      |   0   |
 ---------- ------------ ------------ ------------ ------- 
 

Но у этого запроса уже есть большие недостатки, если вы добавляете категории, вам нужно делать это вручную в запросе (ну, с кодом это выполнимо), но, прежде всего, я боюсь, что со многими пользователями, подпунктами, элементами и категориями это становится очень медленным.

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

Мне нужно иметь возможность ранжировать и возвращать результаты в порядке убывания общих подпунктов в целом и / или в соответствии с выбранной категорией, а также иметь возможность разбивать на страницы (ОГРАНИЧИВАТЬ). Я не собираюсь извлекать список всех пользователей в переменной, а затем выполнять вычисления, а затем сортировать и отображать их, это невообразимо. Должно быть другое решение, другой способ? Реляционные базы данных — это мощные инструменты для любого, кто знает, как их правильно использовать.