ti-enxame.com

Método para criar uma subconsulta usando JDatabase

Em http://docs.joomla.org/Selecting_data_using_JDatabase , não há um método documentado para escrever uma subconsulta usando o JDatabase.

https://Gist.github.com/gunjanpatel/86633 exemplifica uma maneira de fazer isso com (alguns bits omitidos):

$subQuery = $db->getQuery(true);
$query    = $db->getQuery(true);

// Create the base subQuery select statement.
$subQuery->select('*')
    ->from($db->quoteName('#__sub_table'))
    ->where($db->quoteName('subTest') . ' = ' . $db->quote('1'));

// Create the base select statement.
$query->select('*')
    ->from($db->quoteName('#__table'))
    ->where($db->quoteName('state') . ' = ' . $db->quote('1'))
    ->where($db->quoteName('subCheckIn') . ' IN (' . $subQuery->__toString() . ')')
    ->order($db->quoteName('ordering') . ' ASC');

// Set the query and load the result.
$db->setQuery($query);

Parece uma abordagem boa e plausível, mas existe uma melhor?

31
betweenbrain

Sim, no que me diz respeito, a maneira como você construiu a subconsulta é a adotada pela maioria dos desenvolvedores de extensões do joomla.

Eu uso esse mesmo método em algumas das minhas extensões e extensões personalizadas feitas para clientes.

Não existe uma maneira "oficial" de fazer isso, mas, como você mostrou, você pode usar o construtor de consultas e ainda manter uma boa legibilidade

16
Skullbock

AFAIK não existe uma maneira integrada de realizar subconsultas fáceis, o que provavelmente é uma deficiência no sistema e deve ser corrigido via PR.

No entanto, não vejo problema com o seu exemplo - parece bastante razoável.

~~~

Aqui está um exemplo em resposta ao comentário de @ DavidFritsch abaixo. Quanto mais eu penso sobre isso, melhor eu gosto da abordagem mais simples exibida no OP. É mais claro o que está acontecendo.

$query = $this->db->getQuery(true)
  ->select('a.*')
  ->subQuery()
    ->select('b.*')
    ->from('#__table_b AS b')
    ->as('subQueryResult')
  ->endSubQuery()
  ->from('#__table_a AS a');
10
Don Gilbert

Há também uma maneira de executar consultas que contêm subconsultas usando a API da plataforma Joomla. A idéia básica de como usar subconsultas é baseada em gunjanpatel .

Aqui está um exemplo para executar consultas em modelos de conjuntos aninhados :

Consulta SQL:

-- Find the Immediate Subordinates of a Node
SELECT node.title, (COUNT(parent.id) - (sub_tree.depth + 1)) AS depth
FROM lubd3_usergroups AS node,
        lubd3_usergroups AS parent,
        lubd3_usergroups AS sub_parent,
        (
                SELECT node.id, (COUNT(parent.id) - 1) AS depth
                FROM lubd3_usergroups AS node,
                        lubd3_usergroups AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                        AND node.id = 1
                GROUP BY node.id
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.id = sub_tree.id
GROUP BY node.id
-- not showing the parent node
HAVING depth = 1
-- showing the parent node
-- HAVING depth <= 1
ORDER BY node.lft;

e a consulta transformada a ser executada pelo Joomla:

// Create the subQuery select statement.
// Nested Set Queries: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
// CROSS JOIN: http://www.informit.com/articles/article.aspx?p=30875&seqNum=5
$subQuery->select(array('node.id', '(COUNT(parent.id) - 1) AS depth'))
    ->from($db->quoteName('#__usergroups') . 'node')
    ->join('CROSS', $db->quoteName('#__usergroups', 'parent'))
    ->where($db->quoteName('node.lft') . ' BETWEEN  ' . $db->quoteName('parent.lft') . ' AND ' . $db->quoteName('parent.rgt') . ' AND ' . $db->quoteName('node.id') . ' = ' . $db->quote('1'))
    ->group($db->quoteName('node.id'))
    ->order($db->quoteName('node.lft'));

// Create the base select statement.
$query->select(array('node.title', '(COUNT(parent.id) - (sub_tree.depth + 1)) AS depth'))
    ->from($db->quoteName('#__usergroups') . 'node')
    ->join('CROSS', $db->quoteName('#__usergroups', 'parent'))
    ->join('CROSS', $db->quoteName('#__usergroups', 'sub_parent'))
    ->join('CROSS', '(' . $subQuery .') AS sub_tree')
    ->where($db->quoteName('node.lft') . ' BETWEEN  ' . $db->quoteName('parent.lft') . ' AND ' . $db->quoteName('parent.rgt')
    . ' AND ' . $db->quoteName('node.lft') . ' BETWEEN  ' . $db->quoteName('sub_parent.lft') . ' AND ' . $db->quoteName('sub_parent.rgt')
    . ' AND ' . $db->quoteName('sub_parent.id') . ' = ' . $db->quoteName('sub_tree.id'))
    ->group($db->quoteName('node.id'))
    ->having($db->quoteName('depth') . ' = ' . $db->quote('1'))
    ->order($db->quoteName('node.lft'));

// Set the query and load the result.
$db->setQuery($query);
$rowList = $db->loadAssocList();

echo "<pre>";
print_r($rowList);
echo "</pre>";
3
Mario Neubauer

Vou oferecer minha versão do snippet, depois explicar minha justificativa e incluir citações do manual de normas de codificação do Joomla (que será formatado como quoteblock).

$subquery = $db->getQuery(true)
    ->select("checkin")
    ->from("#__sub_table")
    ->where("subTest = 1");

$query = $db->getQuery(true)
    ->select("*")
    ->from("#__table")
    ->where([
        "state = 1",
        "subCheckIn IN ({$subQuery})"
    ])
    ->order("ordering");

$db->setQuery($query);

Use o encadeamento de consultas para conectar vários métodos de consulta, um após o outro, com cada método retornando um objeto que pode oferecer suporte ao próximo método. Isso melhora a legibilidade e simplifica o código resultante.

  • Escrevo primeiro as consultas mais internas e progredo para a consulta mais externa. Isso me permite encadear todos os métodos de criação de consultas diretamente para o método getQuery(). Efetivamente, o nome da variável é gravado apenas uma vez ao criar a consulta individual.
    Aqui está m excelente exemplo de aninhamento pesado de consultas (quando eu achei engraçado alinhar as setas do encadeamento).

  • Eu tento evitar fazer várias chamadas select() e/ou where() dentro da mesma consulta porque eu já a vi levar à confusão de desenvolvedores menos experientes . Como esses métodos aceitam matrizes, acho mais legível e melhor prática de codificação usá-los.

  • e finalmente o tópico mais controverso ...

    Os nomes das tabelas e os nomes das colunas das tabelas devem sempre estar entre o método quoteName () para escapar do nome e das colunas da tabela. Os valores dos campos marcados em uma consulta sempre devem ser colocados no método quote () para escapar do valor antes de passá-lo ao banco de dados. Os valores do campo inteiro marcados em uma consulta também devem ser do tipo convertido para (int).

    Estou muito em conflito com essa postura. Quando cheguei ao Joomla no ano passado, pensei: não faria chamadas inúteis (nenhum benefício para a estabilidade, segurança, legibilidade da consulta) sobre valores estáticos! No entanto, meu empregador gosta da ideia de acessar a linha do Joomla, e tenho que admitir que geralmente aprecio muito as regras, por isso tenho fornecido minhas consultas com quote(), (int) e quoteName(), que também significa montes de concatenação de strings (todos devidamente espaçados). Os resultados finais do meu trabalho são blocos de consulta terrivelmente inchados, que até eu tenho dificuldade em observar. As piores/linhas mais longas que não se prestam ao empilhamento vertical são as chamadas join() devido ao nome da tabela, o alias, ON, então uma ou mais condições que podem ou não exigir citando. Compreendo que esta política seja implementada tendo em mente a segurança de desenvolvedores iniciantes, mas com certeza gostaria que essa política fosse de alguma forma temperada com a sensibilidade de que nem todos os codificadores Joomla são ignorantes. . Quero dizer, veja como o código é limpo e breve, sem as chamadas desnecessárias.

  • Quanto à limpeza:

    • Eu quase nunca uso * Nas minhas cláusulas SELECT
    • Eu nunca chamo __toString()
    • Não cito números inteiros, os converto como números inteiros
    • Eu não escrevo ASC porque essa é a direção de classificação padrão
    • Esforço-me para não usar palavras-chave mysql ao criar novos nomes de tabelas e nomes de colunas
    • Por uma questão de preferência pessoal, costumo usar aspas duplas nos argumentos de string do meu método para manter a uniformidade, distinguir-me das aspas simples do mysql e para que eu possa desfrutar de interpolação de variáveis ​​que normalmente escrevo com " sintaxe complexa ".
    • Uso nomes de variáveis ​​informativos e comentários para ajudar na legibilidade de minhas consultas aninhadas e no meu código geralmente
    • Testo meu código antes que ele saia da minha custódia
1
mickmackusa