MySQL pegar o primeiro registro sem 2 filhoa (parent)

[arthursanno 0]

Possuo a seguinte tabela

CREATE TABLE IF NOT EXISTS `pool` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idPai` int(11) NOT NULL,
  `nome` varchar(100) NOT NULL,
  PRIMARY KEY (`idPool`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

INSERT INTO `pool` (`id`, `idPai`, `nome`) VALUES
(1, 0, 'José'),
(2, 1, 'Maria'),
(3, 1, 'Carlos'),
(4, 2, 'Renta'),
(5, 2, 'Pedro'),
(6, 3, 'Gustavo');

No caso desse diagrama:

1
:: 2
:::: 4
:::: 5
:: 3
:::: 6

Quero fazer uma consulta SQL nessa tabela cujo me retorne o primeiro registros que NÃO possui 2 filhos. (no caso do diagrama seria o registro 3... e ao acrescentar mais um filho de 3, a consulta ia retornar o registro 4 agora)

 

Não sei se tenho que fazer consultas dentro de consultas. Preciso de orientação pra continuar o caminho do estudo.

[Matheus Tavares 167]

Olá!

 

Caso você precise consultar quando não possui nenhum filho, no caso os IDS 4, 5 e 6, você poderia fazer assim:

SELECT id
FROM pool p1
WHERE NOT EXISTS (
  SELECT idPai FROM pool p2 WHERE p1.id = p2.idPai
)

Indo nessa lógica, para restringirmos para os registros que não possuem 2 filhos para casar com o ID 3, poderíamos fazer assim:

SELECT id
FROM pool p1
WHERE (
  SELECT COUNT(1) FROM pool p2 WHERE p1.id = p2.idPai
) <= 1
LIMIT 1

 

Mas eu não acho que resolver esse tipo de questão diretamente no banco seja a melhor solução. Depende muito do que você precisa fazer.

 

Outro detalhe é que do jeito que seu banco está estruturado nesse momento, problemas de performance futuros seriam inevitáveis. Para contornar isso você deve criar um índice/chave na coluna idPai. Há um exemplo de SQL aqui:

https://forum.imasters.com.br/topic/562468-resolvido-estrutura-de-um-projeto-de-marketing-multinível-com-php-e-mysql/

 

[arthursanno 0]

Olá Matheus. Obrigado por me responder.

 

Eu havia construido essa consulta, verifique:

SELECT *
FROM pool a
WHERE a.id NOT IN (select idPai from pool GROUP BY idPai ORDER BY idPai ASC)
OR a.id IN (SELECT idPai
				FROM pool
				WHERE id <> 1
				GROUP BY idPai
				HAVING COUNT(*) < 2);

O resultado seria a lista dos pais sem filhos e do pai com 1 filho.

Mas como a inserção é automática, ou seja, ele já faz a inserção no primeiro pai livre, então mudei para.

SELECT *
FROM pool a
WHERE a.id > (select idPai from pool ORDER BY idPai DESC LIMIT 1)
OR a.id IN (SELECT idPai
				FROM pool
				WHERE id <> 1
				GROUP BY idPai
				HAVING COUNT(*) < 2)
ORDER BY id ASC LIMIT 1;

Me sugere algo?

[Matheus Tavares 167]

Pelo que entendi você está solicitando ao banco os pais com menos de 2 filhos (através da sua segunda subquery) e caso não existam, o último pai criado no sistema (sua primeira subquery), é isso?

Dessa forma você teoricamente teria o pai apropriado para a inserção do próximo filho na árvore.

 

Mas você ainda não está livre de cair na situação de todos os pais terem 2 filhos, inclusive o último pai (sua primeira subquery). Caso sua intenção seja de não permitir mais que 2 filhos, aqui haveria uma exceção, que provavelmente seria criar um pai virtual, por exemplo.

 

Além disso, não tenho certeza, mas acredito que dessa forma sua consulta seria mais performática:

SELECT *
FROM pool p1
WHERE
  (SELECT COUNT(1) FROM pool p2 WHERE p1.id = p2.idPai) <= 1
  OR
  id > (select idPai from pool ORDER BY idPai DESC LIMIT 1)

ORDER BY id ASC
LIMIT 1

 

Outra coisa que poderia ser feita é manter em cache a lista dos pais que ainda não possuem 2 filhos. Imagine essa sua segunda SUBQUERY:

SELECT idPai
FROM pool
WHERE id <> 1
GROUP BY idPai
HAVING COUNT(*) < 2

Basicamente ela faz isso: para todos os pais que não possuam ID 1, agrupe-os e remova os que possuirem 2 filhos ou mais.

Perceba que essa consulta será repetida múltiplas vezes, sempre que você precisar consultar o banco.

 

Sua consulta ficaria assim, se estes registros estivessem em cache:

SELECT *
FROM pool a
WHERE a.id > (select idPai from pool ORDER BY idPai DESC LIMIT 1)
OR a.id IN ( 3 )
ORDER BY id ASC LIMIT 1;

Eu apenas copiei sua consulta e substitui a segunda SUBQUERY pelo id 3, que é o resultado da consulta quando executada.

 

Isso você poderia fazer com MATERIALIZED VIEW ou cache a nível de aplicação (APCu por exemplo, se estivermos falando de PHP).

 

De resto, acho que está tudo ok, mas iria sugerir de você popular melhor sua tabela para testar melhor sua consulta. Algo como uns 20 a 30 registros, pelo menos. Isso iria prover uma maior confiabilidade no desenvolvimento.

[arthursanno 0]

Citar

Pelo que entendi você está solicitando ao banco os pais com menos de 2 filhos (através da sua segunda subquery) e caso não existam, o último pai criado no sistema (sua primeira subquery), é isso?

Dessa forma você teoricamente teria o pai apropriado para a inserção do próximo filho na árvore.

A inserção do novo registros será sempre no primeiro pai disponível.

 

Ex:

INSERT INTO `pool` (`id`, `idPai`, `nome`) VALUES
(1, 0, 'José'),
(2, 1, 'Maria'),
(3, 1, 'Carlos'),
(4, 2, 'Renta'),
(5, 2, 'Pedro'),
(6, 3, 'Gustavo');

Nesse caso o primeiro pai disponível é o 3 (Carlos), porque ele não possui 2 filhos (somente 1). Ao acrescentar mais um filho nele, o primeiro pai disponível é o 4 (Renata), cujo não possui nenhum filho, ao acrescentar mais um filho, Renata ainda será o primeiro pai disponível pois ainda não tem 2 filhos...

INSERT INTO `pool` (`id`, `idPai`, `nome`) VALUES
(1, 0, 'José'),
(2, 1, 'Maria'),
(3, 1, 'Carlos'),
(4, 2, 'Renta'),
(5, 2, 'Pedro'),
(6, 3, 'Gustavo'),
// Acrescentando novo filho em 3 pq ele só tem 1 filho é o primeiro pai disponível
(7, 3, 'Igor'),
// Acrescentando novo filho agora em 4, pq 3 já possui 3 filhos e 4 nenhum filho 
(8, 4, 'Jerry'),
// Acrescentando novo filho, ainda em 4 pq 4 não possui 2 filhos
(9, 4, 'Bárbara'),
// Acrescentando novo filho, agora em 5 pq 4 já possui 2 filhos
(10, 5, 'Rebeca');
...

 

 

Nessa query que montei, ele me retorna sempre o PRIMEIRO PAI DISPONÍVEL

SELECT *
FROM pool a
WHERE a.id > (select idPai from pool ORDER BY idPai DESC LIMIT 1)
OR a.id IN (SELECT idPai
				FROM pool
				WHERE id <> 1
				GROUP BY idPai
				HAVING COUNT(*) < 2)
ORDER BY id ASC LIMIT 1;

Uso esse para pegar o ID do primeiro pai livre e adiciono automaticamente o novo registro nesse pai.

 

http://www.sqlfiddle.com/#!9/67fdf2/1

 

 

Sim, estarei usando PHP.

[Matheus Tavares 167]

37 minutos atrás, arthursanno disse:

Nessa query que montei, ele me retorna sempre o PRIMEIRO PAI DISPONÍVEL 

Aí que tá. Na primeira query que postei é exatamente isso que é feito também.

 

Então, ao menos que essa query não lhe atenda por alguma razão ou esteja funcionando incorretamente, acredito que ela seja a melhor opção para você, levando em consideração que possui menos código e performa um pouco melhor (no seu fiddle dá para notar a diferença em ms na execução):

SELECT *
FROM pool p1
WHERE (
  SELECT COUNT(1) FROM pool p2 WHERE p1.id = p2.idPai
) <= 1
LIMIT 1

Aqui retorna: #5 | 2 | "Pedro", assim como as demais consultas.

 

 

O mesmo serve para a consulta que você elaborou. Ela é uma boa opção se retirarmos a primeira condição do WHERE, que eu acredito que esteja ali sem necessidade.

Nesse caso teríamos isso, que é muito parecido com o que eu fiz, mas utilizando o IN/GROUP/HAVING como alternativas ao depenent subquery:

SELECT *
FROM pool a
WHERE a.id IN (
    SELECT idPai
    FROM pool
    WHERE id <> 1
    GROUP BY idPai
    HAVING COUNT(*) < 2)
ORDER BY id ASC LIMIT 1;

Essa consulta gera o mesmo resultado também. Tudo certo.

 

Entende onde eu quero chegar? Ao menos que eu não tenha entendido alguma coisa, acredito que essas consultas fazem o que você precisa, mas esse pequeno trecho é desnecessário e pode ser removido. Estou me referindo a isso aqui:

[...] a.id > (select idPai from pool ORDER BY idPai DESC LIMIT 1) OR [...]

 

Mas que bom que está resolvido. Você mesmo solucionou a questão :)

[arthursanno 0]

Entendi sim Matheus. Obrigado pela atenção, ajuda e a aula. Aprendi bastante, isso faz parte de um aprendizado que estou praticando.