PHP/MySQL - Organizando melhor as subqueries

Em um artigo anterior tratei das Subqueries como sendo uma espécie de tabela virtual, utilizada para pré selecionar dados.

Hoje vou mostrar uma dica rápida para organizar melhor nossas Queries utilizando este conceito de Subquerie.

É sabido que uma Query pode virar um monstro rapidamente se tivermos de fazer algo muito específico como o intuito de evitar outras Queries. Explico melhor:

Em algumas situações temos de efetuar duas, três, quatro Queries separadas no banco de dados, juntar tudo isto via PHP ou seja lá o programa que se esteja usando, para retornar a informação completa de que necessitamos.

Porém, em grande parte dos casos, ao invés de usar este tipo de "gambi" podemos fazer um Select mais robusto, mas que envolve Subqueries, ou seja, uma Query dentro de outra na mesma consulta.

Este tipo de abordagem pode levar a "monstros" como o exemplo abaixo:

$query = "
SELECT
*
FROM
(
SELECT
*,
@var := @var + 1 AS sequencia
FROM
(
SELECT
ca.id,
id_escolas,
es.nome AS escola,
id_quesitos,
qe.nome AS quesito,
id_professores,
ju.nome AS juiz,
ordem_escolas,
ordem_quesitos,
ordem_professores,
no.valor AS nota
FROM
carnaval ca
INNER JOIN
escolas es
ON
ca.id_escolas = es.id
INNER JOIN
quesitos qe
ON
ca.id_quesitos = qe.id
INNER JOIN
professores ju
ON
ca.id_professores = ju.id
INNER JOIN
notas no
ON
ca.id = no.id_carnaval
WHERE
id_anos = ID_ANOS
AND id_cidades = ID_CIDADES
ORDER BY
ordem_quesitos DESC,
ordem_professores,
ordem_escolas
) t1,
(
SELECT
@var := 0
) t2,
(
SELECT
COUNT(*) AS quantidade
FROM
carnaval
WHERE
id_anos = ID_ANOS
AND id_cidades = ID_CIDADES
) t3
) t4
WHERE
nota IS NOT NULL
ORDER BY
sequencia DESC
LIMIT 1
";


E olha que tem coisa muito, muito pior.

Poderiamos adotar uma abordagem através das Views mas em alguns casos elas não são tão práticas quanto esperamos.

Sendo assim, a dica é separarmos a Query em pedaços e depois juntá-la antes de efetuar a chamada no banco de dados em sí.

Tomando como exemplo a query acima, vamos exemplificar.

//Seleciona toda a lista de professores e escolas
$sub_escolas = "
SELECT
ca.id,
id_escolas,
es.nome AS escola,
id_quesitos,
qe.nome AS quesito,
id_professores,
ju.nome AS professor,
ordem_escolas,
ordem_quesitos,
ordem_professores,
no.valor AS nota
FROM
carnaval ca
INNER JOIN
escolas es
ON
ca.id_escolas = es.id
INNER JOIN
quesitos qe
ON
ca.id_quesitos = qe.id
INNER JOIN
professores ju
ON
ca.id_professores = ju.id
INNER JOIN
notas no
ON
ca.id = no.id_carnaval
WHERE
id_anos = ID_ANOS
AND id_cidades = ID_CIDADES
ORDER BY
ordem_quesitos DESC,
ordem_professores,
ordem_escolas
";
// Retorna a quantidade de registros
$sub_quantidade = "
SELECT
COUNT(*) AS quantidade
FROM
carnaval
WHERE
id_anos = ID_ANOS
AND id_cidades = ID_CIDADES
";

// Query final com a junção das sub queries anteriores
// Retira das sub queries anteriores os registros que não possuem nota
// e cria uma sequência numérica para o resultado
$query = "
SELECT
*
FROM
(
SELECT
*,
@var := @var + 1 AS sequencia
FROM
(
{$sub_escolas}
) t1,
(
SELECT
@var := 0
) t2,
(
{$sub_quantidade}
) t3
) t4
WHERE
nota IS NOT NULL
ORDER BY
sequencia DESC
LIMIT 1
";



No exemplo acima não vai fazer diferença o que retorna a query, mas sim a idéia de "fatiá-la" em pedaços e comenta-los. Assim podemos ter uma melhor documentação do que está acontecendo.

Esta é a dica de hoje.
Abraços