Comment pivoter une base de données MySQL entité-attribut-valeur de schéma

J'ai besoin de concevoir des tableaux qui stocke toutes les métadonnées des fichiers (c'est à dire, nom de fichier, auteur, titre, date de création), et des métadonnées personnalisées (qui a été ajouté aux fichiers par les utilisateurs, par exemple CustUseBy, CustSendBy). Le nombre de champs de métadonnées personnalisés ne peuvent pas être définies à l'avance. En effet, le seul moyen de déterminer quels et combien de balises personnalisées ont été ajoutés sur les fichiers est d'examiner ce qui existe dans les tableaux.

Pour stocker cela, j'ai créé une table de base (avoir tous les métadonnées des fichiers), un Attributes table (la tenue des attributs supplémentaires facultatifs qui peuvent être définies sur les fichiers) et un FileAttributes table (qui attribue une valeur à un attribut d'un fichier).

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;

Des données de l'échantillon:

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');

Maintenant, le problème c'est que je veux afficher les données d'une manière comme ceci:

FileId, Title, Author, CustomAttri1, CustomAttr2, ...
F001    Dox    vinay   Akash         2009/03/02   ...
F002    Excel  Ajay

Ce que la requête va générer ce résultat?

Quel langage de programmation comptez-vous utiliser?
MYSQL , je suis de la planification pour obtenir le résultat souhaité par Requête MYSQL et ensuite rejoindre ce résultat à d'autres tables et passer le désiré resultto FrontEnd.
MySQL n'est pas un langage de programmation...

InformationsquelleAutor | 2009-03-16

21

La question mentionne MySQL, et en fait ce SGBD a une fonction spéciale pour ce genre de problème: GROUP_CONCAT(expr). Prendre un coup d'oeil dans le MySQL manuel de référence sur le groupe-par-fonctions. La fonction a été ajoutée en MySQL version 4.1. Vous serez en utilisant GROUP BY FileID dans la requête.

Je ne suis pas vraiment sûr de savoir comment vous voulez regarder. Si vous voulez tous les attributs répertoriés pour chaque élément (même si cela n'est pas), il sera plus difficile. Cependant, c'est ma suggestion sur la façon de le faire:
```
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;
```
Cela vous donne tous les attributs dans le même ordre, ce qui pourrait être utile. La sortie sera comme suit:
```
'F001', 'Dox', 'vinay', 'CustomAttr1:varchar(40):Akash, CustomUseDate:Datetime:2009/03/02'
```
De cette façon, vous avez seulement besoin d'un seul DB requête, et la sortie est facile à analyser. Si vous souhaitez stocker les attributs réels Datetime etc. dans la base de données, vous devez utiliser SQL dynamique, mais je préfère rester à l'écart de cela et de stocker les valeurs dans varchars.

InformationsquelleAutor nawroth
9

La forme générale d'une telle requête serait
```
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)
   ...
```
Donc, vous avez besoin pour créer de façon dynamique à votre requête dans les attributs dont vous avez besoin. En php-ish pseudocode
```
$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";
```
- Pour Atteindre le Résultat puis-je Créer une Procédure et d'Écrire un Curseur pour vous Déplacer dans les dossiers.. Si Possible, de M'Expliquer avec un exemple, je serais reconnaissant pour l'aide.
- Oui, si vous exécutez et parcourir les $pivotsql requête, chaque ligne d'un fichier et il y aura une colonne pour chaque attribut, qui sera la valeur NULL si l'attribut n'est pas présent pour ce fichier.
- Cela semble être ce que j'ai après pour les dernières semaines. Merci beaucoup
- Je me demandais... est-il un souci de performances avec l'aide de plusieurs LEFT join sur la même table de rassembler rejoint lignes que de colonnes dans la solution ci-dessus?
- Il dépendra de la table de définitions et de leur taille. Exécuter une EXPLIQUER sur le SQL pour évaluer l'impact sur les performances.
InformationsquelleAutor Paul Dixon

Si vous êtes à la recherche de quelque chose de plus utilisable (et jumelables) qu'un groupe-concat résultat, essayez cette solution ci-dessous. J'ai créé quelques tables très similaire à votre exemple, à faire de ce sens.

Cela fonctionne quand:

Vous voulez un pur SQL solution (pas de code, pas de boucles)
Vous avez un prévisible d'un ensemble d'attributs (par exemple, pas dynamique)
Vous êtes OK la mise à jour de la requête lorsque de nouveaux types d'attributs doivent être ajoutés
Vous préférez un résultat qui peut être Rejoint à, Réunies, ou imbriqués comme une sous-sélection

Tableau A (Fichiers)

FileID, Title, Author, CreatedOn

Tableau B (Attributs)

AttrID, AttrName, AttrType [not sure how you use type...]

Tableau C (Files_Attributes)

FileID, AttrID, AttrValue

Traditionnel requête serait tirer de nombreuses lignes redondantes:

SELECT * FROM 
Files F 
LEFT JOIN Files_Attributes FA USING (FileID)
LEFT JOIN Attributes A USING (AttributeID);

AttrID FileID Titre Auteur CreatedOn AttrValue AttrName AttrType 
50 1 TestFile Joe 2011-01-01 vrai ReadOnly bool 
60 1 TestFile Joe 2011-01-01 xls FileFormat texte 
70 1 TestFile Joe 2011-01-01 faux Private bool 
80 1 TestFile Joe 2011-01-01 2011-10-03 date de dernière modification 
60 2 LongNovel Marie 2011-02-01 json FileFormat texte 
80 2 LongNovel Marie 2011-02-01 2011-10-04 date de dernière modification 
70 2 LongNovel Marie 2011-02-01 vrai Private bool 
50 2 LongNovel Marie 2011-02-01 vrai ReadOnly bool 
50 3 ShortStory Susan 2011-03-01 faux ReadOnly bool 
60 3 ShortStory Susan 2011-03-01 ascii FileFormat texte 
70 3 ShortStory Susan 2011-03-01 faux Private bool 
80 3 ShortStory Susan 2011-03-01 2011-10-01 date de dernière modification 
50 4 ProfitLoss projet de Loi 2011-04-01 faux ReadOnly bool 
70 4 ProfitLoss projet de Loi 2011-04-01 vrai Private bool 
80 4 ProfitLoss projet de Loi 2011-04-01 2011-10-02 date de dernière modification 
60 4 ProfitLoss projet de Loi 2011-04-01 texte FileFormat texte 
50 5 MonthlyBudget George 2011-05-01 faux ReadOnly bool 
60 5 MonthlyBudget George 2011-05-01 binaire FileFormat texte 
70 5 MonthlyBudget George 2011-05-01 faux Private bool 
80 5 MonthlyBudget George 2011-05-01 2011-10-20 date de dernière modification

Cette coalescence de requête (approche à l'aide de MAX) peut fusionner les lignes:

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 Titre Auteur CreatedOn ReadOnly FileFormat Privé LastModified 
1 TestFile Joe 2011-01-01 vrai xls faux 2011-10-03 
2 LongNovel Marie 2011-02-01 vrai json vrai 2011-10-04 
3 ShortStory Susan 2011-03-01 faux ascii faux 2011-10-01 
4 ProfitLoss projet de Loi 2011-04-01 faux texte vrai 2011-10-02 
5 MonthlyBudget George 2011-05-01 faux binaire faux 2011-10-20

InformationsquelleAutor methai

6

C'est la norme "des lignes et des colonnes" problème dans SQL.

Il est plus facile à faire en dehors de SQL.

Dans votre application, procédez de la manière suivante:
1. Définir une classe simple pour contenir le fichier, les attributs de système, et une Collection d'attributs de l'utilisateur. Une liste est un bon choix pour cette collection d'attributs de clients. Appelons cette classe FileDescription.
2. Exécuter une simple jointure entre le fichier et tous les attributs de clients pour le fichier.
3. Écrire une boucle pour assembler FileDescriptions du résultat de la requête.
  - Récupérer la première ligne, créer un FileDescription et de la première à la clientèle de l'attribut.
  - Alors qu'il y a plus de lignes à récupérer:
    
    Chercher une ligne
    
    Si cette ligne de nom de fichier ne correspond pas à la FileDescription nous sommes bâtiment: finir la construction d'un FileDescription; ajouter à une Collection de résultat de Descriptions de Fichiers; créer un nouveau, vide FileDescription avec le prénom et le premier client de l'attribut.
    
    Si cette ligne de nom de fichier correspond à la FileDescription nous sommes bâtiment: ajouter un autre attribut client à l'actuel FileDescription
- Salut Merci.. mais je ne suis Pas Gud en PL, j'ai Expierienced en T-SQL, Pouvez-vous m'expliquer en obtenir de brèves comment parvenir à cet Exemple. Merci Beaucoup. –
InformationsquelleAutor S.Lott

J'ai fait des expériences avec les différentes réponses et Methai la réponse a été la plus pratique pour moi. Mon projet actuel, bien qu'il n'utilise pas de Doctrine, MySQL, a fait quelques lâche tables.

Ce qui suit est le résultat de mon expérience avec Methai la solution:

créer une entité tableau

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;

créer un attribut de la table

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;

créer attributevalue table

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;

créer entity_attributevalue table de jointure

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;

remplir table d'entité

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');

remplir la table attributaire

INSERT INTO attribute
    (name, type)
VALUES
    ('ReadOnly', 'bool'),
    ('FileFormat', 'text'),
    ('Private', 'bool'),
    ('LastModified', 'date');

remplir attributevalue table

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');

remplir entity_attributevalue table

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');

Montrant tous les enregistrements

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;

id titre auteur createdOn entity_id attributevalue_id valeur de l'id de attribute_id nom de l'id de type 
1 TestFile Joe 2011-01-01 00:00:00 1 1 1 vrai 1 1 ReadOnly bool 
1 TestFile Joe 2011-01-01 00:00:00 1 2 2 xls 2 2 FileFormat texte 
1 TestFile Joe 2011-01-01 00:00:00 1 3 3 faux 3 3 Private bool 
1 TestFile Joe 2011-01-01 00:00:00 1 4 4 2011-10-03 4 4 date de dernière modification 
2 LongNovel Marie 2011-02-01 00:00:00 2 5 5 vrai 1 1 ReadOnly bool 
2 LongNovel Marie 2011-02-01 00:00:00 2 6 6 json 2 2 FileFormat texte 
2 LongNovel Marie 2011-02-01 00:00:00 2 7 7 3 3 Private bool 
2 LongNovel Marie 2011-02-01 00:00:00 2 8 8 2011-10-04 4 4 date de dernière modification 
3 ShortStory Susan 2011-03-01 00:00:00 3 9 9 faux 1 1 ReadOnly bool 
3 ShortStory Susan 2011-03-01 00:00:00 3 10 10 ascii 2 2 FileFormat texte 
3 ShortStory Susan 2011-03-01 00:00:00 3 11 11 faux 3 3 Private bool 
3 ShortStory Susan 2011-03-01 00:00:00 3 12 12 2011-10-01 4 4 date de dernière modification 
4 ProfitLoss projet de Loi 2011-04-01 00:00:00 4 13 13 faux 1 1 ReadOnly bool 
4 ProfitLoss projet de Loi 2011-04-01 00:00:00 4 14 14 texte 2 2 FileFormat texte 
4 ProfitLoss projet de Loi 2011-04-01 00:00:00 4 15 15 vrai 3 3 Private bool 
4 ProfitLoss projet de Loi 2011-04-01 00:00:00 4 16 16 2011-10-02 4 4 date de dernière modification 
5 MonthlyBudget George 2011-05-01 00:00:00 5 17 17 faux 1 1 ReadOnly bool 
5 MonthlyBudget George 2011-05-01 00:00:00 5 18 18 binaire 2 2 FileFormat texte 
5 MonthlyBudget George 2011-05-01 00:00:00 5 19 19 faux 3 3 Private bool 
5 MonthlyBudget George 2011-05-01 00:00:00 5 20 20 2011-10-20 4 4 date de dernière modification 
6 Document de Jane 2012-04-01 00:00:00 6 21 21 binaire 2 2 FileFormat texte 
6 Document de Jane 2012-04-01 00:00:00 6 22 22 faux 3 3 Private bool 
6 Document de Jane 2012-04-01 00:00:00 6 23 23 2011-10-20 4 4 date de dernière modification 
7 Essai de John 2012-03-01 00:00:00 7 24 24 binaire 2 2 FileFormat texte 
7 Essai de John 2012-03-01 00:00:00 7 25 25 2011-10-20 4 4 date de dernière modification 
8 l'Article de Dan 2012-12-01 00:00:00 NULL NULL NULL NULL NULL NULL NULL NULL

tableau croisé dynamique

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 titre auteur createdOn ReadOnly FileFormat Privé LastModified 
1 TestFile Joe 2011-01-01 00:00:00 vrai xls faux 2011-10-03 
2 LongNovel Marie 2011-02-01 00:00:00 vrai json vrai 2011-10-04 
3 ShortStory Susan 2011-03-01 00:00:00 falsification ascii faux 2011-10-01 
4 ProfitLoss projet de Loi 2011-04-01 00:00:00 falsification du texte vrai 2011-10-02 
5 MonthlyBudget George 2011-05-01 00:00:00 falsification binaire faux 2011-10-20 
6 Document de Jane 2012-04-01 00:00:00 NULL binaire faux 2011-10-20 
7 Essai de John 2012-03-01 00:00:00 NULL binaire NULL 2011-10-20 
8 l'Article de Dan 2012-12-01 00:00:00 NULL NULL NULL NULL

InformationsquelleAutor thoroc

0

Cependant, il existe des solutions pour l'utilisation de lignes que de colonnes, aka transposer les données.
Il impliquer la requête de trucs à faire dans le plus pur SQL, ou vous devrez compter sur certaines fonctionnalités uniquement disponible dans certaines base de données, à l'aide de tableaux croisés dynamiques (ou la Croix tables).

Comme exemple, vous pouvez voir comment le faire ici dans Oracle (11g).

La programmation version sera plus simple à maintenir et à faire et en plus fonctionne avec n'importe quelle base de données.
- Expliquez moi si Stocker les valeurs d'Attributs dans une Colonne de la table avec un Séparateur, où en est-on stocker le Nom de l'attribut.. Si on conserve à la fois Nom de l'attribut et la Valeur dans la Colonne 2, avec un Séparateur de séparation (dans le Cas où plus d'un attribbute pour un fichier) alors comment faire pour Transposer ces valeurs
- Comme vous ne savez pas avant l'exécution, le nombre d'attributs personnalisés, j'irais la manière programmatique. Soit la création d'une requête dynamicaly avec votre programme, ou d'extraire toutes les données avec une jointure et lopp trought il. La construction d'une requête complexe va complexifier la maintenabilité et de peaufinage.
InformationsquelleAutor MarmouCorp
-2

Réponse partielle car je ne sais pas MySQL (bien). Dans MSSQL je regarde les tableaux croisés dynamiques ou serait de créer une table temporaire dans une procédure stockée. Il peut être un moment difficile ...
- Salut Merci.. mais je ne suis Pas Gud en PL, j'ai Expierienced en T-SQL, Pouvez-vous m'expliquer en obtenir de brèves comment parvenir à cet Exemple. Merci Beaucoup.
InformationsquelleAutor Sascha

Vous devez vous connecter pour publier un commentaire.