[Resolvido] Lógica básica para consulta entre tabelas

[Maykel-ctba 233]

Fala povo!

 

Estou com a seguinte dúvida:

 

Tenho as seguintes tabelas:

 

• sistema_produto
  
• sistema_categoria
  
• sistema_produto_categoria
  

 

Não estou conseguindo fazer com que retorne um produto com 2 categorias... na verdade, estou com dificuldade em criar uma lógica para isso (onde usar AND ou OR, sei lá).

 

Esta consulta retornam os produtos, mas não filtrados por nenhuma categoria. Quando a pessoa seleciona uma categoria, ainda dá certo, o problema é quando escolho duas ou mais:

 

SELECT pro.*, cat.catNome
FROM sistema_produto pro, sistema_categoria cat, sistema_produto_categoria pca
WHERE pro.proAtivo = 'S' 
AND pro.proExcluido = 'N' 
AND pro.proId = pca.proId 
AND cat.catId = pca.catId 
GROUP BY pro.proId

 

Para um teste, tentem selecionar um produto que tenha como categorias o Kevlar e Frigorífico.

 

Segue um DUMP simples...

 

--

-- Table structure for table `sistema_categoria`

--

 

CREATE TABLE IF NOT EXISTS `sistema_categoria` (

`catId` int(10) NOT NULL AUTO_INCREMENT,

`catTipo` enum('SEG','MAT','APL') NOT NULL,

`catNome` varchar(255) NOT NULL,

`catAtivo` enum('S','N') NOT NULL,

`catExcluido` enum('S','N') NOT NULL,

PRIMARY KEY (`catId`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

 

--

-- Dumping data for table `sistema_categoria`

--

 

INSERT INTO `sistema_categoria` (`catId`, `catTipo`, `catNome`, `catAtivo`, `catExcluido`) VALUES

(1, 'SEG', 'Frigorífico', 'S', 'N'),

(2, 'MAT', 'Kevlar', 'S', 'N'),

(3, 'APL', 'Corte', 'S', 'N');

 

-- --------------------------------------------------------

 

--

-- Table structure for table `sistema_produto`

--

 

CREATE TABLE IF NOT EXISTS `sistema_produto` (

`proId` int(10) NOT NULL AUTO_INCREMENT,

`proCodigo` varchar(255) NOT NULL,

`proNome` varchar(255) NOT NULL,

`proDescricao` text NOT NULL,

`proDestaque` enum('S','N') NOT NULL,

`proAtivo` enum('S','N') NOT NULL,

`proExcluido` enum('S','N') NOT NULL,

PRIMARY KEY (`proId`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

 

--

-- Dumping data for table `sistema_produto`

--

 

INSERT INTO `sistema_produto` (`proId`, `proCodigo`, `proNome`, `proDescricao`, `proDestaque`, `proAtivo`, `proExcluido`) VALUES

(1, '041', 'Produto 1', '<p>sdas21321321</p>', 'S', 'S', 'N'),

(2, '001', 'Produto 2', '<p>Teste de produto 2</p>\r\n<p> </p>\r\n<p><img src="http://25.media.tumblr.com/tumblr_m5i46t7tOs1qdlh1io1_250.gif" alt="" width="208" height="213" /></p>', 'S', 'S', 'N'),

(3, 'Teste01', 'Produto 3', '<p>Teste03</p>', 'S', 'S', 'N');

 

-- --------------------------------------------------------

 

--

-- Table structure for table `sistema_produto_categoria`

--

 

CREATE TABLE IF NOT EXISTS `sistema_produto_categoria` (

`proId` int(10) NOT NULL,

`catId` int(10) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

--

-- Dumping data for table `sistema_produto_categoria`

--

 

INSERT INTO `sistema_produto_categoria` (`proId`, `catId`) VALUES

(1, 2),

(2, 2),

(3, 1),

(1, 1);

 

Existe algum jeito simples de resolver essa questão?

[Motta 645]

algo assim ...

 

SELECT pro.*, cat.catNome
FROM sistema_produto pro
WHERE pro.proAtivo = 'S' 
AND pro.proExcluido = 'N'
and 2 = (SELECT count(*)
        FROM sistema_categoria cat, sistema_produto_categoria pca
        WHERE pro.proId = pca.proId 
        AND cat.catId = pca.catId  
        and (cat.catnome like '%Kevlar%' 
             or
             cat.catnome like '%Frigorífico%')

 

A ideia é se achou 2 categorias e pq tem ambas, +ou- isto.

 

Já vi um problema parecido aqui mas não achei no busca.

[Maykel-ctba 233]

Olá Motta, não deu certo, infelizmente...

 

Vou tentar explicar melhor.

 

O sistema que estou desenvolvendo é para uma empresa que comercializa luvas industriais.

 

Cada luva que é cadastrada no sistema recebem determinados segmentos que podem ser utilizadas (ex: industria, hospitais, setor quimico, etc.) e além disso, também recebem aplicações (ex: altas temperaturas, baixas temperaturas, cortes, etc.) e materia prima que é desenvolvida (aço, fibra de vidro, etc.)

 

No momento do cadastro, criei uma tabela CATEGORIA, onde o próprio usuario cadastra as diversas categorias, e que tipo ela é (se é segmento, aplicação ou matéria prima.)

 

Sendo assim, temos alguns exemplos:

 

Tabela categoria (resumo)

 

ID > Tipo > Titulo

01 > Segmento > Cosmética

02 > Segmento > Industrias em geral

28 > Aplicação > Salões de beleza

30 > Aplicação > Implementos agrícolas

 

Quando vou cadastrar o produto, cadastro quais categorias quero que o produto receba em uma tabela intermediária. O produto de ID 01 (Vamos chamar de Luva Latex) possui vinculo com as categorias 01 e 28.

 

Tabela Produto-Categoria

 

proID > catID

01 > 01

02 > 28

 

Até aí, tudo lindo e funcionando. Parte de cadastros está OK. Agora o bicho pega: :pinch:

 

Na página inicial do site que desenvolvemos, o cliente gostaria que tivessem 3 campos de busca...

- Escolha um segmento (option-list com as categorias cadastradas que sejam = SEG)

- Escolha uma aplicação (idem acima, porém = APL)

- Escolha uma matéria-prima (idem acima, porém = MAT).

 

Desta maneira, o usuário poderá pesquisar uma luva mais específica. Se eu sou do setor de Estética, gostaria de luvas para salões de beleza. Logo, teria que procurar por luvas que tenham ID de categoria = 1 e 28. Porém, não basta só eu colocar isso na consulta, ele está dando pau porque estou procurando com tipo = APL E tipo = SEG. Como não tem como existir estes 2 tipos em um mesmo registro, estou me embananando.

 

Poderia me ajudar?

 

Consulta usada (sem definir nenhuma categoria):

SELECT prc.*, pro.proNome, cat.catNome, cat.catTipo
FROM sistema_produto_categoria prc, sistema_produto pro, sistema_categoria cat
WHERE pro.proId = prc.proId
AND cat.catId = prc.catId
AND pro.proAtivo = 'S'
AND pro.proExcluido = 'N'
ORDER BY prc.catId ASC

[Motta 645]

... Como não tem como existir estes 2 tipos em um mesmo registro, estou me embananando. ...

 

SELECT pro.*, cat.catNome
FROM sistema_produto pro
WHERE pro.proAtivo = 'S' 
AND pro.proExcluido = 'N'
and 2 = (SELECT count(*)
        FROM sistema_categoria cat, sistema_produto_categoria pca
        WHERE pro.proId = pca.proId 
        AND cat.catId = pca.catId  
        and (cat.catId = 1
             or
             cat.catId = 28)

 

Por isto o count(*), ele terá de localizar os dois registros.

[Maykel-ctba 233]

Entendi, Motta!

 

Parece ter funcionado, a principio! Apenas retirei o catNome da primeira consulta e adicionei mais um ")" que estava faltando, e retornou certo... Vou fazer mais testes e qualquer coisa eu retorno ainda hoje! ;-)

 

EDIT!

 

---------

 

Opa, apareceu um probleminha...

 

Quando eu escolho apenas um ID de categoria, ele não traz... só quando tem mais de 1...

 

Funciona:

SELECT pro.* FROM sistema_produto pro WHERE pro.proAtivo = 'S' AND pro.proExcluido = 'N' AND 2 = ( SELECT COUNT(*) FROM sistema_categoria cat, sistema_produto_categoria pca WHERE pro.proId = pca.proId AND cat.catId = pca.catId AND (cat.catId = 2 OR cat.catId = 28))

 

Não funciona

SELECT pro.* FROM sistema_produto pro WHERE pro.proAtivo = 'S' AND pro.proExcluido = 'N' AND 2 = ( SELECT COUNT(*) FROM sistema_categoria cat, sistema_produto_categoria pca WHERE pro.proId = pca.proId AND cat.catId = pca.catId AND (cat.catId = 2))

[Motta 645]

Quando eu escolho apenas um ID de categoria, ele não traz... só quando tem mais de 1...

 

Esta se selecionando 2 categorias, por isto o OR e por isto o 2, com 3 (2,28,45) ...

 

SELECT pro.* FROM sistema_produto pro WHERE pro.proAtivo = 'S' AND pro.proExcluido = 'N' AND 3 = ( SELECT COUNT(*) FROM sistema_categoria cat, sistema_produto_categoria pca WHERE pro.proId = pca.proId AND cat.catId = pca.catId AND (cat.catId in (2,28,45))

[Maykel-ctba 233]

Oi Motta!

 

Cara, na real não estou entendendo.

 

Na consulta que eu colei acima, eu faço a pesquisa só com uma, e não retorna. Não tem OR ali. A lógica não seria de que deveria retornar apenas os resultados com aquele catId?

 

Eu preciso encontrar um jeito de consultar onde eu possa tanto escolher um catId só, ou 3, eu tenho um sistema onde não posso ficar mudando as consultas quando preciso, hehe...

 

Por via das dúvidas, onde eu colocaria este trecho de consulta que você me passou? Eu substituo pela minha inteira?

[Prog 183]

Acho que não entendemos sua dúvida corretamente.

 

Isto apenas não resolve?

SELECT pro.proNome, group_concat( cat.catNome )
FROM sistema_produto_categoria prc, sistema_produto pro, sistema_categoria cat
WHERE pro.proId = prc.proId
AND cat.catId = prc.catId
AND cat.catId IN ( 2, 1 )
AND pro.proAtivo = 'S'
AND pro.proExcluido = 'N'
GROUP BY proNome
ORDER BY prc.catId
LIMIT 0 , 30

[Maykel-ctba 233]

Fala Prog, tranquilo?

 

Cara, acho que resolve sim! A principio, coloquei a consulta no myadmin e trouxe certo! Vou fazer mais testes e retorno, mas acredito que seja isso mesmo =)

[Motta 645]

Atente que você queria um produto que tivesse TODAS as categorias pedidas, ao menos eu entendi isto.

[Maykel-ctba 233]

Isso mesmo, cada produto tem diversas categorias... nos filtros, gostaria que fossem listados os produtos que tenham categoria X ou Y! Acredito que deu certinho ;-)

[Motta 645]

categoria X ou Y

então não são todase sim qualquer uma das categorias.

 

Aí fica bem mais simples.

[Maykel-ctba 233]

Funcionou, galerinha! Perfeito like a boss! :thumbsup:

[Maykel-ctba 233]

Galera, um pepino.

 

SELECT pro.proId, pro.proNome, pro.proDescricao, pro.proArquivo, pro.proImagem, pro.proDestaque, pro.proMt11, pro.proEn388, pro.proAtivo, pro.proExcluido, group_concat(cat.catNome) FROM sistema_produto pro, sistema_produto_categoria prc, sistema_categoria cat WHERE pro.proAtivo = 'S' AND pro.proExcluido = 'N' AND pro.proId = prc.proId AND cat.catId = prc.catId AND cat.catId IN ('17', '64') GROUP BY proNome

 

Pq ele está retornando todas os produtos com categorias 17 OU 64? Na realidade, era para ver os produtos que tivessem as 2 categorias, mas nao produtos de uma ou outra, e sim produtos que conteriam as 2 categorias. Agora que cadastrei todos os produtos me dei conta ao fazer um teste...

 

Exemplificando o que acontece:

 

Ao selecionar os IDs 17 (Latex) e 64 (Salões de beleza), ele deveria me retornar apenas um produto, que contém estas 2 categorias juntas. Porém, estão retornando todos os produtos da categoria latex MAIS os produtos da categoria SALOES DE BELEZA, apenas agrupados pelo proNome.

[Prog 183]

A clausula IN, por definição, tem o mesmo comportamento de OR.

 

Você pode tentar uma coisa:

SELECT pro.proNome, group_concat(cat.catNome)

FROM sistema_produto pro, sistema_produto_categoria prc, sistema_categoria cat

WHERE pro.proAtivo = 'S' AND pro.proExcluido = 'N' AND pro.proId = prc.proId AND cat.catId = prc.catId AND cat.catId IN ('1','2')

GROUP BY proNome

HAVING count(cat.catId) = 2

 

Esse valor 2 precisa ser dinâmico, de acordo com a quantidade de categorias que foram selecionadas.

 

Um outro problema que observo no seu comando SELECT, o GROUP BY deve estar relacionado com cada campo que se faz necessário criar o agrupamento, neste caso os campos pro.proId, pro.proNome, pro.proDescricao, pro.proArquivo, pro.proImagem, pro.proDestaque, pro.proMt11, pro.proEn388, pro.proAtivo, pro.proExcluido precisam estar no GROUP BY.

 

Como ficaria um possível comando "final":

SELECT pro.proId, pro.proNome, pro.proDescricao, pro.proArquivo, pro.proImagem, pro.proDestaque, pro.proMt11, pro.proEn388, pro.proAtivo, pro.proExcluido, group_concat(cat.catNome) 
FROM sistema_produto pro, sistema_produto_categoria prc, sistema_categoria cat 
WHERE pro.proAtivo = 'S' AND pro.proExcluido = 'N' AND pro.proId = prc.proId AND cat.catId = prc.catId AND cat.catId IN ('17', '64') 
GROUP BY pro.proId, pro.proNome, pro.proDescricao, pro.proArquivo, pro.proImagem, pro.proDestaque, pro.proMt11, pro.proEn388, pro.proAtivo, pro.proExcluido 
HAVING count(cat.catId) = 2

 

Veja ae se resolve.

[Maykel-ctba 233]

Cara, a principio resolveu, @Prog! Vou fazer mais testes, mas de antemão já agradeço! Tantos recursos de SQL que eu jamais saberia que existem. Tive que fazer umas adaptaçoes no meu framework aqui para suportar o group_concat e o Having, mas deu tudo certinho! ;-)