Busca avançada com mysql

mau rs · Maio 7, 2014

Olá pessoal, gostaria de fazer uma busca em mysql, vou citar um exemplo do que estou tentando fazer da maneira mais otimizada possivel:

Tenho uma tabela com várias camisetas no banco de dados(Exemplo).

Quero filtrar por cor, tamanho, modelo e tipo de tecido, porém caso não há retorno com esses critérios, gostaria que ele retornasse as camisas com os critérios mais parecidos possíveis, até retornar qualquer camiseta caso não haja camisetas com nenhums dos critérios selecionados.

Eu até poderia fazer um loop com php e tudo mais mas acho que não é a solução mais inteligente, alguém pode ajudar me mostrando alguma lógica que eu poderia seguir.

lokaodomau · Maio 7, 2014

Veja se isso ajuda: Uso de rank no MySQL

Motta · Maio 7, 2014

Publique as descrições das tabelas envolvidas e os relacionamentos, mas creio que vai na ideia do lokaodomau.

mau rs · Maio 7, 2014

Lokaodomau, não achei muita lógica com o que eu preciso no link que me enviou, talvez eu não tenha achado lógica por burrisse, mas não achei kkk. Não publiquei as tabelas porque a tabela que estou em produção é extremamente complexa, por isso citei exemplos mais simples. Bom, fiz umas tabelas aqui que vou deixar pra ver se vocês me dão uma luz.

SQL das tabelas:

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tb_camisas
-- ----------------------------
DROP TABLE IF EXISTS `tb_camisas`;
CREATE TABLE `tb_camisas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(255) NOT NULL,
  `cor_id` int(11) NOT NULL,
  `modelo_id` int(11) NOT NULL,
  `estampa_id` int(11) NOT NULL,
  `tamanho_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_cor` (`cor_id`),
  KEY `fk_modelo` (`modelo_id`),
  KEY `fk_estampa` (`estampa_id`),
  KEY `fk_tamanho` (`tamanho_id`),
  CONSTRAINT `fk_tamanho` FOREIGN KEY (`tamanho_id`) REFERENCES `tb_tamanho` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_cor` FOREIGN KEY (`cor_id`) REFERENCES `tb_cor` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_estampa` FOREIGN KEY (`estampa_id`) REFERENCES `tb_estampa` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_modelo` FOREIGN KEY (`modelo_id`) REFERENCES `tb_modelo` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tb_camisas
-- ----------------------------
INSERT INTO `tb_camisas` VALUES ('1', 'Camisa1', '1', '1', '1', '1');
INSERT INTO `tb_camisas` VALUES ('3', 'Camisa2', '1', '1', '1', '2');
INSERT INTO `tb_camisas` VALUES ('5', 'Camisa3', '1', '1', '1', '3');
INSERT INTO `tb_camisas` VALUES ('6', 'Camisa4', '1', '1', '2', '1');
INSERT INTO `tb_camisas` VALUES ('8', 'Camisa5', '1', '1', '3', '1');
INSERT INTO `tb_camisas` VALUES ('10', 'Camisa6', '1', '2', '1', '1');

-- ----------------------------
-- Table structure for tb_cor
-- ----------------------------
DROP TABLE IF EXISTS `tb_cor`;
CREATE TABLE `tb_cor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cor` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tb_cor
-- ----------------------------
INSERT INTO `tb_cor` VALUES ('1', 'azul');
INSERT INTO `tb_cor` VALUES ('2', 'amarelo');
INSERT INTO `tb_cor` VALUES ('3', 'branco');

-- ----------------------------
-- Table structure for tb_estampa
-- ----------------------------
DROP TABLE IF EXISTS `tb_estampa`;
CREATE TABLE `tb_estampa` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `estampa` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tb_estampa
-- ----------------------------
INSERT INTO `tb_estampa` VALUES ('1', 'estampa1');
INSERT INTO `tb_estampa` VALUES ('2', 'estampa2');
INSERT INTO `tb_estampa` VALUES ('3', 'estampa3');

-- ----------------------------
-- Table structure for tb_modelo
-- ----------------------------
DROP TABLE IF EXISTS `tb_modelo`;
CREATE TABLE `tb_modelo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modelo` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tb_modelo
-- ----------------------------
INSERT INTO `tb_modelo` VALUES ('1', 'modelo1');
INSERT INTO `tb_modelo` VALUES ('2', 'modelo2');
INSERT INTO `tb_modelo` VALUES ('3', 'modelo3');

-- ----------------------------
-- Table structure for tb_tamanho
-- ----------------------------
DROP TABLE IF EXISTS `tb_tamanho`;
CREATE TABLE `tb_tamanho` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tamanho` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tb_tamanho
-- ----------------------------
INSERT INTO `tb_tamanho` VALUES ('1', 'P');
INSERT INTO `tb_tamanho` VALUES ('2', 'M');
INSERT INTO `tb_tamanho` VALUES ('3', 'G');

Essa consulta abaixo retorna 1 resultado conforme critérios que coloquei na query. (Mas eu precisava de 2 itens como resultado)

SELECT
tb_camisas.nome,
tb_camisas.cor_id,
tb_camisas.modelo_id,
tb_camisas.estampa_id,
tb_camisas.tamanho_id,
tb_cor.cor,
tb_estampa.estampa,
tb_modelo.modelo,
tb_tamanho.tamanho
FROM
tb_camisas
INNER JOIN tb_cor ON tb_camisas.cor_id = tb_cor.id
INNER JOIN tb_estampa ON tb_camisas.estampa_id = tb_estampa.id
INNER JOIN tb_modelo ON tb_camisas.modelo_id = tb_modelo.id
INNER JOIN tb_tamanho ON tb_camisas.tamanho_id = tb_tamanho.id
WHERE tb_modelo.modelo= 'modelo1' AND
tb_tamanho.tamanho= 'P' AND
tb_estampa.estampa='estampa1' AND
tb_cor.cor = 'azul'
ORDER BY RAND() LIMIT 2

Essa outra aqui não retorna nada

SELECT
tb_camisas.nome,
tb_camisas.cor_id,
tb_camisas.modelo_id,
tb_camisas.estampa_id,
tb_camisas.tamanho_id,
tb_cor.cor,
tb_estampa.estampa,
tb_modelo.modelo,
tb_tamanho.tamanho
FROM
tb_camisas
INNER JOIN tb_cor ON tb_camisas.cor_id = tb_cor.id
INNER JOIN tb_estampa ON tb_camisas.estampa_id = tb_estampa.id
INNER JOIN tb_modelo ON tb_camisas.modelo_id = tb_modelo.id
INNER JOIN tb_tamanho ON tb_camisas.tamanho_id = tb_tamanho.id
WHERE tb_modelo.modelo= 'modelo3' AND
tb_tamanho.tamanho= 'P' AND
tb_estampa.estampa='estampa1' AND
tb_cor.cor = 'azul'
ORDER BY RAND() LIMIT 2

Então o que eu precisava:

Que retornasse nas duas queries 2 itens, se a minha condição não satisfez pra buscar 2 itens, que fosse encontrado o mais próximo.(Se não encontrar itens com os 4 criterios, procurasse por 3 criterios, se não achou com 3, procurasse por 2 criterios, se não achou, por um critério)

Deixe-me saber se ficou mais legivel minha idéia.

lokaodomau · Maio 7, 2014

Tente alterar os INNER JOIN por LEFT JOIN.

Ao usar INNER JOIN, o resultado da tabela tb_camisas é exibido somente se houver relacionamento, com LEFT JOIN, a tb_camisas retorna normalmente e os campos das tabelas relacionadas retornam NULL se não houver o relacionamento.

Motta · Maio 7, 2014

Uma ideia, mas existem formas melhores de fazer isto

SELECT
tb_camisas.nome,
tb_camisas.cor_id,
tb_camisas.modelo_id,
tb_camisas.estampa_id,
tb_camisas.tamanho_id,
tb_cor.cor,
tb_estampa.estampa,
tb_modelo.modelo,
tb_tamanho.tamanho
FROM
tb_camisas
INNER JOIN tb_cor ON tb_camisas.cor_id = tb_cor.id
INNER JOIN tb_estampa ON tb_camisas.estampa_id = tb_estampa.id
INNER JOIN tb_modelo ON tb_camisas.modelo_id = tb_modelo.id
inner join tb_tamanho on tb_camisas.tamanho_id = tb_tamanho.id
where (tb_modelo.modelo= 'modelo1' or
       tb_tamanho.tamanho= 'P' or
       tb_estampa.estampa='estampa1' or
       tb_cor.cor in ('azul','vermelho'))
       
order by ((case when tb_modelo.modelo= 'modelo1' then 1 else 0 end) +       
          (case when tb_tamanho.tamanho= 'P' then 1 else 0 end) +  
          (case when tb_estampa.estampa='estampa1'  then 1 else 0 end) +  
          (case when tb_cor.cor in ('azul','vermelho') then 1 else 0 end)) desc

mau rs · Maio 7, 2014

Tente alterar os INNER JOIN por LEFT JOIN.

Ao usar INNER JOIN, o resultado da tabela tb_camisas é exibido somente se houver relacionamento, com LEFT JOIN, a tb_camisas retorna normalmente e os campos das tabelas relacionadas retornam NULL se não houver o relacionamento.

Se eu colocar LEFT JOIN, neste exemplo, não alterou os resultados, retorna a mesma coisa do que com INNER.

Uma ideia, mas existem formas melhores de fazer isto

SELECT
tb_camisas.nome,
tb_camisas.cor_id,
tb_camisas.modelo_id,
tb_camisas.estampa_id,
tb_camisas.tamanho_id,
tb_cor.cor,
tb_estampa.estampa,
tb_modelo.modelo,
tb_tamanho.tamanho
FROM
tb_camisas
INNER JOIN tb_cor ON tb_camisas.cor_id = tb_cor.id
INNER JOIN tb_estampa ON tb_camisas.estampa_id = tb_estampa.id
INNER JOIN tb_modelo ON tb_camisas.modelo_id = tb_modelo.id
inner join tb_tamanho on tb_camisas.tamanho_id = tb_tamanho.id
where (tb_modelo.modelo= 'modelo1' or
       tb_tamanho.tamanho= 'P' or
       tb_estampa.estampa='estampa1' or
       tb_cor.cor in ('azul','vermelho'))
       
order by ((case when tb_modelo.modelo= 'modelo1' then 1 else 0 end) +       
          (case when tb_tamanho.tamanho= 'P' then 1 else 0 end) +  
          (case when tb_estampa.estampa='estampa1'  then 1 else 0 end) +  
          (case when tb_cor.cor in ('azul','vermelho') then 1 else 0 end)) desc

Fiz alguns testes e o funcionamento ficou como eu preciso, vou fazer mais uns testes e retorno.

mau rs · Maio 8, 2014

Uma ideia, mas existem formas melhores de fazer isto

SELECT
tb_camisas.nome,
tb_camisas.cor_id,
tb_camisas.modelo_id,
tb_camisas.estampa_id,
tb_camisas.tamanho_id,
tb_cor.cor,
tb_estampa.estampa,
tb_modelo.modelo,
tb_tamanho.tamanho
FROM
tb_camisas
INNER JOIN tb_cor ON tb_camisas.cor_id = tb_cor.id
INNER JOIN tb_estampa ON tb_camisas.estampa_id = tb_estampa.id
INNER JOIN tb_modelo ON tb_camisas.modelo_id = tb_modelo.id
inner join tb_tamanho on tb_camisas.tamanho_id = tb_tamanho.id
where (tb_modelo.modelo= 'modelo1' or
       tb_tamanho.tamanho= 'P' or
       tb_estampa.estampa='estampa1' or
       tb_cor.cor in ('azul','vermelho'))
       
order by ((case when tb_modelo.modelo= 'modelo1' then 1 else 0 end) +       
          (case when tb_tamanho.tamanho= 'P' then 1 else 0 end) +  
          (case when tb_estampa.estampa='estampa1'  then 1 else 0 end) +  
          (case when tb_cor.cor in ('azul','vermelho') then 1 else 0 end)) desc

É amigo, consegui fazer algo bem produtivo aqui com sua ajuda Motta, valew também ao lokaodomau.

Motta · Maio 8, 2014

Só atente que se for montar um sql dinâmico tomar cuidado com SQL Injection.

mau rs · Maio 8, 2014

Tranquilo, me cuido com isso, além de usar PDO :thumbsup:

Arquivado

Busca avançada com mysql

Recommended Posts

mau rs 37

Compartilhar este post

Link para o post

Compartilhar em outros sites

lokaodomau 61

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

mau rs 37

Compartilhar este post

Link para o post

Compartilhar em outros sites

lokaodomau 61

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

mau rs 37

Compartilhar este post

Link para o post

Compartilhar em outros sites

mau rs 37

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

mau rs 37

Compartilhar este post

Link para o post

Compartilhar em outros sites

Javascript - Calcular Valores Monetário em Tempo Real

JavaScript - Exibir logo dentro da Tabela html

PHP+Codeigniter - Implementar o Input CÓDIGO ou ID do Produto

Fóruns

Tempo Real

Informação importante