Как повернуть схему значения атрибута сущности MySQL
Мне нужно спроектировать таблицы, в которых хранятся все метаданные файлов (например, имя файла, автор, заголовок, дата создания) и пользовательские метаданные (которые были добавлены в файлы пользователями, например CustUseBy, CustSendBy). Количество настраиваемых полей метаданных не может быть установлено заранее. Действительно, единственный способ определить, что и сколько пользовательских тегов было добавлено в файлы, - это изучить то, что существует в таблицах.
Чтобы сохранить это, я создал базовую таблицу (имеющую все общие метаданные файлов), Attributes таблица (содержит дополнительные, необязательные атрибуты, которые могут быть установлены для файлов) и FileAttributes таблица (которая присваивает значение атрибуту для файла).
CREAT TABLE FileBase (
id VARCHAR(32) PRIMARY KEY,
name VARCHAR(255) UNIQUE NOT NULL,
title VARCHAR(255),
author VARCHAR(255),
created DATETIME NOT NULL,
) Engine=InnoDB;
CREATE TABLE Attributes (
id VARCHAR(32) PRIMARY KEY,
name VARCHAR(255) NOT NULL,
type VARCHAR(255) NOT NULL
) Engine=InnoDB;
CREATE TABLE FileAttributes (
sNo INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
fileId VARCHAR(32) NOT NULL,
attributeId VARCHAR(32) NOT NULL,
attributeValue VARCHAR(255) NOT NULL,
FOREIGN KEY fileId REFERENCES FileBase (id),
FOREIGN KEY attributeId REFERENCES Attributes (id)
) Engine=InnoDB;
Пример данных:
INSERT INTO FileBase
(id, title, author, name, created)
VALUES
('F001', 'Dox', 'vinay', 'story.dox', '2009/01/02 15:04:05'),
('F002', 'Excel', 'Ajay', 'data.xls', '2009/02/03 01:02:03');
INSERT INTO Attributes
(id, name, type)
VALUES
('A001', 'CustomeAttt1', 'Varchar(40)'),
('A002', 'CustomUseDate', 'Datetime');
INSERT INTO FileAttributes
(fileId, attributeId, attributeValue)
VALUES
('F001', 'A001', 'Akash'),
('F001', 'A002', '2009/03/02');
Теперь проблема в том, что я хочу показать данные таким образом:
FileId, Title, Author, CustomAttri1, CustomAttr2, ...
F001 Dox vinay Akash 2009/03/02 ...
F002 Excel Ajay
Какой запрос будет генерировать этот результат?
7 ответов
В вопросе упоминается MySQL, и на самом деле эта СУБД имеет специальную функцию для решения этой проблемы: GROUP_CONCAT(expr), Посмотрите в справочном руководстве по MySQL групповые функции. Функция была добавлена в версии MySQL 4.1. Вы будете использовать GROUP BY FileID в запросе.
Я не совсем уверен, как вы хотите, чтобы результат выглядел. Если вы хотите, чтобы каждый атрибут был указан для каждого элемента (даже если он не установлен), это будет сложнее. Тем не менее, это мое предложение о том, как это сделать:
SELECT bt.FileID, Title, Author,
GROUP_CONCAT(
CONCAT_WS(':', at.AttributeName, at.AttributeType, avt.AttributeValue)
ORDER BY at.AttributeName SEPARATOR ', ')
FROM BaseTable bt JOIN AttributeValueTable avt ON avt.FileID=bt.FileID
JOIN AttributeTable at ON avt.AttributeId=at.AttributeId
GROUP BY bt.FileID;
Это дает вам все атрибуты в том же порядке, что может быть полезно. Вывод будет выглядеть следующим образом:
'F001', 'Dox', 'vinay', 'CustomAttr1:varchar(40):Akash, CustomUseDate:Datetime:2009/03/02'
Таким образом, вам нужен только один запрос к БД, и выходные данные легко анализировать. Если вы хотите сохранить атрибуты как реальные Datetime и т. Д. В БД, вам нужно будет использовать динамический SQL, но я бы оставил это в чистоте и сохранял значения в varchars.
Если вы ищете что-то более пригодное для использования (и объединяемое), чем результат группового объединения, попробуйте это решение ниже. Я создал несколько таблиц, очень похожих на ваш пример, чтобы это имело смысл.
Это работает, когда:
- Вы хотите чистое решение SQL (без кода, без циклов)
- У вас есть предсказуемый набор атрибутов (например, не динамический)
- Вы в порядке обновления запроса, когда необходимо добавить новые типы атрибутов.
- Вы бы предпочли результат, который может быть присоединен, объединен или вложен в качестве подвыбора
Таблица A (файлы)
FileID, Title, Author, CreatedOn
Таблица B (Атрибуты)
AttrID, AttrName, AttrType [not sure how you use type...]
Таблица C (Files_Attributes)
FileID, AttrID, AttrValue
Традиционный запрос извлекает много избыточных строк:
SELECT * FROM
Files F
LEFT JOIN Files_Attributes FA USING (FileID)
LEFT JOIN Attributes A USING (AttributeID);
AttrID FileID Заголовок Автор CreatedOn AttrValue AttrName AttrType 50 1 TestFile Joe 2011-01-01 true ReadOnly bool 60 1 TestFile Joe 2011-01-01 xls fileFormat text 70 1 TestFile Joe 2011-01-01 false Private Private bool 80 1 TestFile Joe 2011-01-01 2011-10-03 LastModified дата 60 2 LongNovel Mary 2011-02-01 json FileFormat text 80 2 LongNovel Mary 2011-02-01 2011-10-04 LastModified дата 70 2 LongNovel Mary 2011-02-01 true Private bool 50 2 LongNovel Mary 2011-02-01 true ReadOnly bool 50 3 ShortStory Susan 2011-03-01 false ReadOnly bool 60 3 ShortStory Susan 2011-03-01 ascii FileFormat text 70 3 ShortStory Susan 2011-03-01 false Private bool 80 3 ShortStory Susan 2011-03-01 2011-10-01 LastModified дата 50 4 Счет ProfitLoss 2011-04-01 false ReadOnly bool 70 4 Счет ProfitLoss 2011-04-01 true Private bool 80 4 Счет ProfitLoss 2011-04-01 2011-10 -02 LastModified date 60 4 ProfitLoss Bill 2011-04-01 text FileFormat text 50 5 MonthlyBudget George 2011-05-01 false ReadOnly bool 60 5 MonthlyBudget George 2011-05-01 двоичный текст FileFormat 70 5 MonthlyBudget George 2011-05-01 false Приватное bool 80 5 MonthlyBudget George 2011-05-01 2011-10-20 LastModified дата
Этот объединяющий запрос (подход с использованием MAX) может объединить строки:
SELECT
F.*,
MAX( IF(A.AttrName = 'ReadOnly', FA.AttrValue, NULL) ) as 'ReadOnly',
MAX( IF(A.AttrName = 'FileFormat', FA.AttrValue, NULL) ) as 'FileFormat',
MAX( IF(A.AttrName = 'Private', FA.AttrValue, NULL) ) as 'Private',
MAX( IF(A.AttrName = 'LastModified', FA.AttrValue, NULL) ) as 'LastModified'
FROM
Files F
LEFT JOIN Files_Attributes FA USING (FileID)
LEFT JOIN Attributes A USING (AttributeID)
GROUP BY
F.FileID;
FileID Заголовок Автор Создан On Только для чтения FileFormat Private LastModified 1 TestFile Joe 2011-01-01 true xls false 2011-10-03 2 LongNovel Mary 2011-02-01 true json true 2011-10-04 3 ShortStory Сьюзен 2011-03-01 false ascii false 2011-10-01 4 ProfitLoss Bill 2011-04-01 false текст true 2011-10-02 5 MonthlyBudget George 2011-05-01 ложный двоичный ложный 2011-10-20
Общая форма такого запроса будет
SELECT file.*,
attr1.value AS 'Attribute 1 Name',
attr2.value AS 'Attribute 2 Name',
...
FROM
file
LEFT JOIN attr AS attr1
ON(file.FileId=attr1.FileId and attr1.AttributeId=1)
LEFT JOIN attr AS attr2
ON(file.FileId=attr2.FileId and attr2.AttributeId=2)
...
Таким образом, вам нужно динамически построить ваш запрос из атрибутов, которые вам нужны. В псевдокоде php-ish
$cols="file";
$joins="";
$rows=$db->GetAll("select * from Attributes");
foreach($rows as $idx=>$row)
{
$alias="attr{$idx}";
$cols.=", {$alias}.value as '".mysql_escape_string($row['AttributeName'])."'";
$joins.="LEFT JOIN attr as {$alias} on ".
"(file.FileId={$alias}.FileId and ".
"{$alias}.AttributeId={$row['AttributeId']}) ";
}
$pivotsql="select $cols from file $joins";
Это стандартная проблема "строк в столбцы" в SQL.
Это проще всего сделать вне SQL.
В вашем приложении сделайте следующее:
Определите простой класс, содержащий файл, системные атрибуты и коллекцию пользовательских атрибутов. Список - хороший выбор для этой коллекции атрибутов клиента. Давайте назовем этот класс FileDescription.
Выполните простое соединение между файлом и всеми атрибутами клиента для файла.
Напишите цикл для сборки FileDescription из результата запроса.
Получите первую строку, создайте FileDescription и установите первый атрибут клиента.
Пока есть еще ряды для извлечения:
- Получить строку
- Если имя файла этой строки не соответствует описанию FileDescription, которое мы создаем: завершите сборку FileDescription; добавить это к результирующей коллекции описаний файлов; создайте новую пустую FileDescription с указанным именем и первым атрибутом клиента.
- Если имя файла этой строки соответствует описанию FileDescription, которое мы создаем: добавьте еще один атрибут customer в текущую FileDescription
Я экспериментировал с разными ответами, и ответ Метая был для меня наиболее удобным. Мой текущий проект, хотя он использует Doctrine с MySQL, имеет довольно много свободных таблиц.
Следующее является результатом моего опыта с решением Methai:
создать таблицу сущностей
DROP TABLE IF EXISTS entity;
CREATE TABLE entity (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255),
author VARCHAR(255),
createdOn DATETIME NOT NULL
) Engine = InnoDB;
создать таблицу атрибутов
DROP TABLE IF EXISTS attribute;
CREATE TABLE attribute (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
type VARCHAR(255) NOT NULL
) Engine = InnoDB;
создать таблицу значений атрибутов
DROP TABLE IF EXISTS attributevalue;
CREATE TABLE attributevalue (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
value VARCHAR(255) NOT NULL,
attribute_id INT UNSIGNED NOT NULL,
FOREIGN KEY(attribute_id) REFERENCES attribute(id)
) Engine = InnoDB;
создать таблицу соединений entity_attributevalue
DROP TABLE IF EXISTS entity_attributevalue;
CREATE TABLE entity_attributevalue (
entity_id INT UNSIGNED NOT NULL,
attributevalue_id INT UNSIGNED NOT NULL,
FOREIGN KEY(entity_id) REFERENCES entity(id),
FOREIGN KEY(attributevalue_id) REFERENCES attributevalue(id)
) Engine = InnoDB;
заполнить таблицу сущностей
INSERT INTO entity
(title, author, createdOn)
VALUES
('TestFile', 'Joe', '2011-01-01'),
('LongNovel', 'Mary', '2011-02-01'),
('ShortStory', 'Susan', '2011-03-01'),
('ProfitLoss', 'Bill', '2011-04-01'),
('MonthlyBudget', 'George', '2011-05-01'),
('Paper', 'Jane', '2012-04-01'),
('Essay', 'John', '2012-03-01'),
('Article', 'Dan', '2012-12-01');
заполнить таблицу атрибутов
INSERT INTO attribute
(name, type)
VALUES
('ReadOnly', 'bool'),
('FileFormat', 'text'),
('Private', 'bool'),
('LastModified', 'date');
заполнить таблицу значений атрибутов
INSERT INTO attributevalue
(value, attribute_id)
VALUES
('true', '1'),
('xls', '2'),
('false', '3'),
('2011-10-03', '4'),
('true', '1'),
('json', '2'),
('true', '3'),
('2011-10-04', '4'),
('false', '1'),
('ascii', '2'),
('false', '3'),
('2011-10-01', '4'),
('false', '1'),
('text', '2'),
('true', '3'),
('2011-10-02', '4'),
('false', '1'),
('binary', '2'),
('false', '3'),
('2011-10-20', '4'),
('doc', '2'),
('false', '3'),
('2011-10-20', '4'),
('rtf', '2'),
('2011-10-20', '4');
заполнить таблицу entity_attributevalue
INSERT INTO entity_attributevalue
(entity_id, attributevalue_id)
VALUES
('1', '1'),
('1', '2'),
('1', '3'),
('1', '4'),
('2', '5'),
('2', '6'),
('2', '7'),
('2', '8'),
('3', '9'),
('3', '10'),
('3', '11'),
('3', '12'),
('4', '13'),
('4', '14'),
('4', '15'),
('4', '16'),
('5', '17'),
('5', '18'),
('5', '19'),
('5', '20'),
('6', '21'),
('6', '22'),
('6', '23'),
('7', '24'),
('7', '25');
Показаны все записи
SELECT *
FROM `entity` e
LEFT JOIN `entity_attributevalue` ea ON ea.entity_id = e.id
LEFT JOIN `attributevalue` av ON ea.attributevalue_id = av.id
LEFT JOIN `attribute` a ON av.attribute_id = a.id;
идентификатор заголовка автор создан On entity_id attributevalue_id значение id атрибут_id идентификатор имя типа 1 TestFile Joe 2011-01-01 00:00:00 1 1 1 true 1 1 ReadOnly bool 1 TestFile Joe 2011-01-01 00:00:00 1 2 2 xls 2 2 FileFormat text 1 TestFile Joe 2011-01-01 00:00:00 1 3 3 false 3 3 Private bool 1 TestFile Joe 2011-01-01 00:00:00 1 4 4 2011-10-03 4 4 LastModified дата 2 LongNovel Mary 2011-02-01 00:00:00 2 5 5 true 1 1 ReadOnly bool 2 LongNovel Mary 2011-02-01 00:00:00 2 6 6 json 2 2 FileFormat text 2 LongNovel Mary 2011-02-01 00:00:00 2 7 7 true 3 3 Private bool 2 LongNovel Mary 2011-02-01 00:00:00 2 8 8 2011-10-04 4 4 LastModified дата 3 ShortStory Susan 2011-03-01 00:00:00 3 9 9 false 1 1 ReadOnly bool 3 ShortStory Susan 2011-03-01 00:00:00 3 10 10 ascii 2 2 ФайлФормат текста 3 ShortStory Susan 2011-03-01 00:00:00 3 11 11 false 3 3 Private bool 3 ShortStory Susan 2011-03-01 00:00:00 3 12 12 2011-10-01 4 4 LastModified дата 4 ProfitLoss Bill 2011-04-01 00:00:00 4 13 13 false 1 1 ReadOnly bool 4 ProfitLoss Bill 2011-04-01 00:00:00 4 14 14 text 2 2 FileFormat text 4 ProfitLoss Bill 2011-04-01 00:00:00 4 15 15 true 3 3 Private bool 4 ProfitLoss Bill 2011-04-01 00:00:00 4 16 16 2011-10-02 4 4 LastModified дата 5 MonthlyBudget George 2011-05-01 00:00:00 5 17 17 false 1 1 ReadOnly bool 5 MonthlyBudget George 2011-05-01 00:00:00 5 18 18 двоичный 2 2 FileFormat text 5 MonthlyBudget George 2011-05-01 00:00:00 5 19 19 false 3 3 Private bool 5 MonthlyBudget George 2011-05-01 00:00:00 5 20 20 2011-10-20 4 4 LastModified дата 6 Paper Jane 2012-04-01 00:00:00 6 21 21 двоичный файл 2 2 FileFormat text 6 Paper Jane 2012-04-01 00:00:00 6 22 22 false 3 3 Private bool 6 Paper Jane 2012-04-01 00:00:00 6 23 23 2011-10-20 4 4 LastModified дата 7 Эссе Джон 2012-03-01 00:00:00 7 24 24 двоичный 2 2 FileFormat text 7 Эссе Джон 2012-03-01 00:00:00 7 25 25 2011-10-20 4 4 LastModified дата 8 Статья Дан 2012-12-01 00:00:00 NULL NULL NULL NULL NULL NULL NULL
сводная таблица
SELECT e.*,
MAX( IF(a.name = 'ReadOnly', av.value, NULL) ) as 'ReadOnly',
MAX( IF(a.name = 'FileFormat', av.value, NULL) ) as 'FileFormat',
MAX( IF(a.name = 'Private', av.value, NULL) ) as 'Private',
MAX( IF(a.name = 'LastModified', av.value, NULL) ) as 'LastModified'
FROM `entity` e
LEFT JOIN `entity_attributevalue` ea ON ea.entity_id = e.id
LEFT JOIN `attributevalue` av ON ea.attributevalue_id = av.id
LEFT JOIN `attribute` a ON av.attribute_id = a.id
GROUP BY e.id;
id название автор создан On ReadOnly FileFormat Private LastModified 1 TestFile Joe 2011-01-01 00:00:00 true xls false 2011-10-03 2 LongNovel Mary 2011-02-01 00:00:00 правда json правда 2011-10-04 3 ShortStory Susan 2011-03-01 00:00:00 false ascii false 2011-10-01 4 ProfitLoss Bill 2011-04-01 00:00:00 неверный текст верный 2011-10-02 5 MonthlyBudget George 2011-05-01 00:00:00 ложный двоичный код ложный 2011-10-20 6 Paper Jane 2012-04-01 00:00:00 NULL двоичный неверный 2011-10-20 7 эссе Джон 2012-03-01 00:00:00 NULL двоичный NULL 2011-10-20 8 Статья Дан 2012-12-01 00:00:00 NULL NULL NULL NULL
Однако есть решения использовать строки в качестве столбцов, иначе говоря, транспонировать данные. Это включает в себя приемы запросов, чтобы сделать это в чистом SQL, или вам придется полагаться на определенные функции, доступные только в определенной базе данных, используя сводные таблицы (или кросс-таблицы).
В качестве примера вы можете увидеть, как это сделать здесь, в Oracle (11g).
Программную версию будет проще поддерживать и создавать, а также она будет работать с любой базой данных.
Частичный ответ, так как я не знаю MySQL (хорошо). В MSSQL я бы посмотрел таблицы Pivot или создал бы временную таблицу в хранимой процедуре. Это может быть трудное время...