Правильный SQL запрос с динамическими полями и значениями

Question

Прошу сильно не ругать меня, но всё же помочь понять как сделать лучше.

И так представим что есть 2 некие компании. У каждой компании есть свои клиенты (таблица clients для клиентов). Эти таблицы с клиентами имеют динамические поля, к примеру у первой компании есть клиенты их таблица состоит из 5 полей(столбцы), а у второй компании таблица состоит из 4 полей(столбцы) таблица client_fields, так же у каждого поля своё значение таблица client_values.

Получается в каждой компании разная структура таблицы клиентов. я пробовал join но без успешно, у меня к примеру 50 тыс клиентов и запрос просто неистово долгий. Какой sql запрос нужно использовать, любая помощь будет оценина

CREATE TABLE `clients_basses` (
   `id` INT(10) NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    PRIMARY KEY (`id`) USING BTREE
)COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=2

INSERT INTO `clients_basses` VALUES (1, 'База клиентов 1');

CREATE TABLE `clients` (
   `id` INT(10) NOT NULL AUTO_INCREMENT,
   `base_id` INT(10) NOT NULL,
   `ctreated_at` TIMESTAMP NULL DEFAULT NULL,
   `updated_at` TIMESTAMP NULL DEFAULT NULL,
   PRIMARY KEY (`id`) USING BTREE,
   INDEX `FK_clients_clients_basses` (`base_id`) USING BTREE,
   CONSTRAINT `FK_clients_clients_basses` FOREIGN KEY (`base_id`) REFERENCES 
   `new-yaprofi`.`clients_basses` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) COLLATE='utf8mb4_0900_ai_ci' ENGINE=InnoDB AUTO_INCREMENT=2

INSERT INTO `clients` VALUES (1, 1, '2021-03-18 20:45:18', '2021-03-18 20:45:20');

CREATE TABLE `client_fields` (
   `id` INT(10) NOT NULL AUTO_INCREMENT,
   `base_id` INT(10) NOT NULL,
   `client_id` INT(10) NOT NULL,
   `label` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
   `key` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
   PRIMARY KEY (`id`) USING BTREE,
   INDEX `FK_client_fields_clients_basses` (`base_id`) USING BTREE,
   INDEX `FK_client_fields_clients` (`client_id`) USING BTREE,
   CONSTRAINT `FK_client_fields_clients` FOREIGN KEY (`client_id`) REFERENCES 
   `new-yaprofi`.`clients` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
   CONSTRAINT `FK_client_fields_clients_basses` FOREIGN KEY (`base_id`) 
   REFERENCES `new-yaprofi`.`clients_basses` (`id`) ON UPDATE CASCADE ON DELETE CASCADE) COLLATE='utf8mb4_0900_ai_ci' ENGINE=InnoDB AUTO_INCREMENT=4;

INSERT INTO `client_fields` VALUES (1, 1, 1, 'Имя', 'name');
INSERT INTO `client_fields` VALUES (2, 1, 1, 'Телефон', 'phone');
INSERT INTO `client_fields` VALUES (3, 1, 1, 'Возраст', 'age');

CREATE TABLE `client_values` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `field_id` INT(10) NOT NULL,
    `base_id` INT(10) NULL DEFAULT NULL,
    `value` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `FK__client_fields` (`field_id`) USING BTREE,
    INDEX `FK_client_values_clients_basses` (`base_id`) USING BTREE,
    CONSTRAINT `FK_client_values_clients_basses` FOREIGN KEY (`base_id`) REFERENCES `new-yaprofi`.`clients_basses` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `FK__client_fields` FOREIGN KEY (`field_id`) REFERENCES `new-yaprofi`.`client_fields` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
) COLLATE='utf8mb4_0900_ai_ci' ENGINE=InnoDB AUTO_INCREMENT=5;

INSERT INTO `client_values` VALUES (1, 1, 1, 'Роман');
INSERT INTO `client_values` VALUES (2, 2, 1, '991');
INSERT INTO `client_values` VALUES (3, 2, 1, '103');
INSERT INTO `client_values` VALUES (4, 3, 1, '26');

результат для клиента, так же фильтрация и сортировка данных
(есть догадки что не правильно создана таблица данных)

+---+-------+--------+--------+
|   |    Имя| Телефон| Возраст|
+---+-------+--------+--------+
|   |       |  911   |        | 
| 1 |  Роман|  103   |      26|
+---+-------+--------+--------+

Answer 1

версия 8.0.19 - MySQL – Роман Нерез

Можно сравнительно просто - одна строка в результате, в поле Телефон будет значение '991,103', устроит? – Akina

Да с телефонами вполне – Роман Нерез

Ок. Создаём представление (выполняется однократно), подготавливающее исходные данные:

CREATE VIEW complete_data AS
SELECT c.id, cf.label, GROUP_CONCAT(cv.value) value
FROM clients_basses cb
JOIN clients c ON c.base_id = cb.id
JOIN client_fields cf ON cf.client_id = c.id
JOIN client_values cv ON cv.field_id = cf.id
GROUP BY c.id, cf.label;

Создаём хранимую процедуру (выполняется однократно) формирования сводной таблицы:

CREATE PROCEDURE pivot (tablename VARCHAR(64),
                        groupname VARCHAR(64),
                        pivotname VARCHAR(64),
                        valuename VARCHAR(64))
BEGIN
SELECT CONCAT('CREATE VIEW to_columnslist AS\n',
              'SELECT DISTINCT CONCAT(\'`\', `', pivotname,'`, \'` VARCHAR(255) path \\\'$."\', ', pivotname,', \'"\\\'\') line\n',
              'FROM ', tablename)
INTO @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
SELECT CONCAT(
'SELECT to_json.`', groupname,'`, parsed.*', '\n',
'FROM (SELECT `', groupname,'`, JSON_OBJECTAGG(`', pivotname,'`, `', valuename,'`) json_data', '\n',
'      FROM `', tablename,'`', '\n',
'      GROUP BY `', groupname,'`) to_json', '\n',
'CROSS JOIN JSON_TABLE( json_data,', '\n',
'                       "$" COLUMNS ( ', 
GROUP_CONCAT(line SEPARATOR ',\n                                     '),
' ) ) parsed'
) sql_text
INTO @sql
FROM to_columnslist;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
DROP VIEW to_columnslist;
END

Получаем результат:

CALL pivot ('complete_data', 'id', 'label', 'value');

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4f93018d66cbdb92340dc1eb4a0a3815

PS. Имеются избыточные связи. Настоятельно рекомендую удалить поле base_id из таблиц client_fields и client_values, ну и соответственно удалить внешние ключи этих таблиц к таблице clients_basses.

БЛОГ НА HUSL

Правильный SQL запрос с динамическими полями и значениями

Ответы (1 шт):