Como transformar linhas em colunas?

Eu tenho um database onde eu armazeno palavras-chave agrupadas em projetos e dados relacionados a cada palavra-chave, então eu exibo datagrids foreach projeto com uma linha por palavra-chave e várias colunas recuperadas da mesma tabela “dados”. Eu tenho 4 tabelas, palavras-chave, projetos, group_keywords e dados. “palavras-chave” apenas armazena a palavra-chave, “projeta” o nome do projeto, “group_keywords”, as palavras-chave ids para as palavras-chave atribuídas a esse projeto e “data” é onde todos os dados foreach palavra-chave são identificados por uma chave estrangeira para as palavras-chave .id e uma coluna de nome para identificar o nome do dado.

Agora, para recuperar as palavras-chave + todos os dados de um projeto, uso essa consulta:

SELECT * FROM `group_keywords` INNER JOIN keywords on keywords.id = keyword_id INNER JOIN data ON data.id = keywords.id WHERE `group_id` = (SELECT `id` FROM `projects` WHERE `name` = 'ProjectName' 

Isso me dá algo como

  id group_id keyword_id id keyword id name value 12 5 52 52 absorption food 52 data_name_x1 6 12 5 52 52 absorption food 52 data_name_x2 8 12 5 52 52 absorption food 52 data_name_x3 26 12 5 52 52 absorption food 52 data_name_x4 2 ... 

Mas o que eu quero é obter:

 id group_id keyword_id id keyword id data_name_x1 data_name_x2 data_name_x3 data_name_x4 12 5 52 52 absorption food 52 6 8 26 2 ... 

Então eu posso classificar e usar a paginação para os datagrídeos facilmente, caso contrário, eu não tenho idéia de como fazê-lo, porque ao usar grandes conjuntos de dados, não posso simplesmente despejar tudo em uma matriz, dados demais.

Este é o esquema:

 -- -------------------------------------------------------- -- Table structure for table `keywords` CREATE TABLE IF NOT EXISTS `keywords` ( `id` int(10) unsigned NOT NULL auto_increment, `keyword` varchar(255) NOT NULL, UNIQUE KEY `id` (`id`), UNIQUE KEY `keyword` (`keyword`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=115386 ; -- -------------------------------------------------------- -- Table structure for table `data` CREATE TABLE IF NOT EXISTS `data` ( `id` int(10) unsigned NOT NULL, `name` varchar(100) NOT NULL, `value` varchar(15) NOT NULL, UNIQUE KEY `id` (`id`,`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- Table structure for table `projects` -- CREATE TABLE IF NOT EXISTS `projects` ( `id` int(10) NOT NULL auto_increment, `name` varchar(100) NOT NULL, `parent` varchar(100) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ; -- -------------------------------------------------------- -- Table structure for table `group_keywords` CREATE TABLE IF NOT EXISTS `group_keywords` ( `id` int(10) NOT NULL auto_increment, `group_id` int(10) NOT NULL, `keyword_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `group_id` (`group_id`,`keyword_id`), KEY `keyword_id` (`keyword_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=119503 ; -- -------------------------------------------------------- -- Constraints for table `data` -- ALTER TABLE `data` ADD CONSTRAINT `data_ibfk_1` FOREIGN KEY (`id`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -------------------------------------------------------- -- Constraints for table `group_keywords` -- ALTER TABLE `group_keywords` ADD CONSTRAINT `group_keywords_ibfk_1` FOREIGN KEY (`keyword_id`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; 

Esta operação é tradicionalmente chamada “unpivot” e alguns RDBMSs o suportam, mas o MySQL não parece ser um deles. Você tem duas opções, faça isso em SQL ou faça isso em PHP. No MySQL parece algo assim com auto-junções (não sei qual campo se qualifica como um campo de identificação para você, então, me perdoe criando meu próprio exemplo). Do ponto de vista da performance, certifique-se de indexar o ID e o Nome da coluna, caso contrário, essas ligações serão rastreadas.

 shapes ID Name Value 1 Color Red 1 Shape Circle ... for more "columns" 2 Color Green 2 Shape Square ... for more "columns" SELECT A.ID, B.Value as Color, C.Value as Shape ... for more "columns" FROM shapes A LEFT JOIN shapes B ON B.ID = A.ID AND B.Name = 'Color' LEFT JOIN shapes C ON C.ID = A.ID AND C.Name = 'Shape' ... for more "columns" 

O que deve nos redefinir (a menos que minha cabeça-SQL-parser esteja passando a noite esta noite):

 ID Color Shape 1 Red Circle 2 Green Square 

Para a versão do PHP, você não precisa necessariamente carregar uma matriz, você pode transmiti-la. Classifique pelo PK e vá para baixo definindo as propriedades. Em pseudocódigo:

 Set X to undefined Get a Record Check the ID property, if it's different than X, create a new object, set X to the new ID, and yield the previous object Set the property of the object based on the "Name" column of our result 

Espero que isto ajude!

    Intereting Posts