klawdyo 0 Denunciar post Postado Setembro 23, 2009 Bom dia. Tenho uma tabela Imoveis, uma tabela Caracteristicas, e uma tabela de relacionamento entre essas duas, chamada RelCaracImoveis Um imóvel possui várias características, e uma característica pertence vários imóveis. RelCaracImoveis só guarda o id do imóvel e o id da caracteristica, bem como seu valor. Exemplo da estrutura de RelCaracImoveis: id|imoveis_id|carac_id|valor 1| 38 | 2 | 2 2| 38 | 3 | 1 Nesse caso, eu estou dizendo que o imóvel 38 possui 2 quartos e 1 suíte. Bom.. Pela minha estrutura, quero fazer um select na tabela de imoveis, juntamente com as caracteristicas, onde eu traga esses dados relacionados, dessa forma, semelhante a uma consulta de referência cruzada: id|rua|salas|quartos|suites|vagas_garagem 38|xxx| 1 | 2 | 1 | 1 39|xxx| 1 | 3 | 2 | 2 Os campos id e rua, referem-se ao imóvel, já os demais, estão vindo da tabela de caracteristicas, através do relacionamento com o RelCaracImoveis e Imoveis. Eu consegui fazer assim: SELECT rel_carac_imoveis.valor_carac , rel_carac_imoveis.imoveis_id , (case caracteristicas.id when 1 then true else false end) as sala_estar , (case rel_carac_imoveis.caracteristicas_id when 2 then true end) as quartos , (case rel_carac_imoveis.caracteristicas_id when 3 then true end) as suites , (case rel_carac_imoveis.caracteristicas_id when 4 then true end) as vagas_garagem FROM rel_carac_imoveis, caracteristicas Só que ele retorna os dados assim: id|rua|salas|quartos |suites|vagas_garagem 38|xxx| 1 | null | null | null 38|xxx| null| null | nulL | null 38|xxx| null| null | nulL | null 38|xxx| null| null | nulL | null 38|xxx| null| null | nulL | null 38|xxx| null| null | nulL | null 38|xxx| null| null | nulL | null 38|xxx| null| null | nulL | null 38|xxx| null| null | nulL | null Para cada valor de RelCaracImoveis relacionado ao imovel 38, ela retorna uma linha, mas tudo vem com null, exceto o primeiro. Se eu tentar agrupar pelo valor do imovel, ele também não retorna da forma esperada. É isso. Alguma dica? Como refaço essa consulta? Alguma sugestão de alteração na estrutura? Compartilhar este post Link para o post Compartilhar em outros sites
Eclesiastes 2 Denunciar post Postado Setembro 23, 2009 Olá, o processo que deseja realizar, é criar uma "pivot table" ou "crosstab report". Como você tem características com ID fixo, você consegue fazer isso na query por meio de uma checagem dentro de uma função de agregação, onde internalmente é verificado cada registro relacionado à agregação, que no caso, seria pelo imoveis_id. Exemplo: mysql> SELECT imoveis_id, -> SUM(IF(caracteristicas_id = 2, valor, 0)) as quartos, -> SUM(IF(caracteristicas_id = 3, valor, 0)) as suites, -> SUM(IF(caracteristicas_id = 4, valor, 0)) as vagas_garagem -> FROM rel_carac_imoveis -> GROUP BY imoveis_id; +------------+---------+--------+---------------+ | imoveis_id | quartos | suites | vagas_garagem | +------------+---------+--------+---------------+ | 1 | 2 | 1 | 0 | +------------+---------+--------+---------------+ 1 row in set (0.02 sec) Compartilhar este post Link para o post Compartilhar em outros sites
klawdyo 0 Denunciar post Postado Setembro 23, 2009 Valeu, macho. Funcionou perfeitamente. Ainda neste ensejo, estamos criando aliases pra cada uma das verificações. No entanto, se eu tentar acrescentar "where quartos>3", o mysql vai dizer que não existe nenhuma coluna com esse nome. Eu resolvi isso colocando sua consulta como uma subquery, mais ou menos assim select * from ( SELECT imoveis_id, SUM(IF(caracteristicas_id = 2, valor, 0)) as quartos, SUM(IF(caracteristicas_id = 3, valor, 0)) as suites, SUM(IF(caracteristicas_id = 4, valor, 0)) as vagas_garagem FROM rel_carac_imoveis GROUP BY imoveis_id) as e where quartos>3 Você sugere outra forma? E quanto a modificações? Você me sugere alguma que tornasse a criação dessa pivot table mais automatizada, ou alguma modificação nas tabelas? Valeu pela resposta, por mim esse tópico já está resolvido. :) Compartilhar este post Link para o post Compartilhar em outros sites
Eclesiastes 2 Denunciar post Postado Setembro 23, 2009 Essa forma acredito que atenda bem. Mas claro, sempre há jeito de fazer de forma mais otimizada. Que no caso, seria pegar somente os imoveis_id que possuirem > 3 e fazer o join com a mesma tabela, pra computar as agregações. Obviamente, diminuiria a quantidade de processamento das agregações para somente os imoveis desejados. Compreende? Quanto a criação automatizada, não há como via SQL. Somente definindo manualmente mesmo. E eu acho que essa modelagem está boa para este fim. Compartilhar este post Link para o post Compartilhar em outros sites
klawdyo 0 Denunciar post Postado Setembro 24, 2009 Compreendidíssimo!!!! Eu só não consigo visualizar onde eu colocaria esse where, de forma a fazer o filtro antes que as agregações fossem processadas. Será que eu deveria colocar mais uma sub-query inline? Assim ficariam 3 níveis de recursão.Correto? Compartilhar este post Link para o post Compartilhar em outros sites
Eclesiastes 2 Denunciar post Postado Setembro 24, 2009 Você teria que fazer apenas um self-join. Exemplo: SELECT r.imoveis_id, SUM(IF(r2.caracteristicas_id = 2, r2.valor, 0)) as quartos, SUM(IF(r2.caracteristicas_id = 3, r2.valor, 0)) as suites, SUM(IF(r2.caracteristicas_id = 4, r2.valor, 0)) as vagas_garagem FROM rel_carac_imoveis r INNER JOIN rel_carac_imoveis r2 ON r2.imoveis_id = r.imoveis_id WHERE r.caracteristicas_id = 2 AND r.valor > 3 GROUP BY r.imoveis_id Compartilhar este post Link para o post Compartilhar em outros sites
klawdyo 0 Denunciar post Postado Setembro 24, 2009 Valeu, assim que chegar em casa farei as adaptações. Obrigado. Tópico resolvido! Compartilhar este post Link para o post Compartilhar em outros sites
klawdyo 0 Denunciar post Postado Setembro 27, 2009 Ali onde tem rel_carac_imoveis r2, depois, ele não acha o r2, diz que não existe. Compartilhar este post Link para o post Compartilhar em outros sites
klawdyo 0 Denunciar post Postado Setembro 27, 2009 Eu vou fazer assim: Vou configurar o cron para ser executado a cada 2 horas, e criar uma tabela baseada no sql acima. algo assim create table full_data as (select ..........) agora, todas as buscas serão feitas a partir da tabela full_data, que será atualizada a cada 2 horas. Pensei em criar uma view. Mas a view é apenas uma "consulta embalada", né? Ou ela oferece alguma vantagem gritante de performance em uma consulta pesada como essa? Compartilhar este post Link para o post Compartilhar em outros sites
Eclesiastes 2 Denunciar post Postado Setembro 27, 2009 Ali onde tem rel_carac_imoveis r2, depois, ele não acha o r2, diz que não existe. Ahm? Eu testei essa query antes de postar. Cole exatamente a mensagem de erro, por favor. Eu vou fazer assim: Vou configurar o cron para ser executado a cada 2 horas, e criar uma tabela baseada no sql acima. algo assim create table full_data as (select ..........) agora, todas as buscas serão feitas a partir da tabela full_data, que será atualizada a cada 2 horas. Pensei em criar uma view. Mas a view é apenas uma "consulta embalada", né? Ou ela oferece alguma vantagem gritante de performance em uma consulta pesada como essa? Não há vantagem em performance não. No Oracle que obtemos uma vantagem com o recurso de materializar uma view. Que no caso, ele armazena os dados obtidos da query em outra storage. Compartilhar este post Link para o post Compartilhar em outros sites
klawdyo 0 Denunciar post Postado Setembro 27, 2009 Não está havendo erros. Tá perfeito. O problema era o campo "valor", que na realidade se chama "valor_carac". As mensagens do mysql não ajudam muito as vezes. Só vi esse erro agora. Valeu Compartilhar este post Link para o post Compartilhar em outros sites