Jaime Neto | desenvolvimento para web

nov/10

10

Melhorando o Zend_Db_Table – Parte 4: Fazendo JOIN automático com tabelas relacionadas


Uma das coisas chatas em se usar o Zend_Db_Table é quando você precisa de informações que se encontram em tabelas associadas à tabela que você está usando. Para isso, o Zend_Db_Table oferece a opção de você configurar a variável $_referenceMap para definir os relacionamentos entre as tabelas, e usar métodos como findDependentRowset(), ou algo do tipo find<tableclass>By<rule>(), que são na verdade métodos de Zend_Db_Table_Row (Saber mais).

A utilização desses métodos pode ser boa no caso de poucos dados, mas se você estiver querendo pegar as informações completas das tabelas associadas de uma lista muito grande de registros, a quantidade de acessos ao banco para pegar essas informações vai ser proporcional à quantidade registros. Veja o seguinte cenário como exemplo:

Você quer pegar de uma tabela Usuarios a lista dos usuários com o nome da cidade em que vivem. Sendo que em Usuarios Você só tem o código da cidade, que é chave estrangeira para a tabela Cidades, onde está o nome.

Agora imagine que você quer pegar essa informação de todos os 300 usuários da sua lista. Você teria que fazer uma consulta ao banco para pegar a lista de usuários, iterar sobre a lista e fazer uma consulta para cada usuário para pegar o nome da cidade usando os métodos de Zend_Db_Table_Row, totalizando 301 acessos ao banco de dados e muito trabalho de código.

Por esse motivo, eu sempre me perguntei porque a classe Zend_Db_Table não tinha métodos para trazer as informações das tabelas associadas juntamente com a tabela em questão. E isso é o que vai ser mostrado neste post como ser feito.

A idéia é aproveitar a estrutura original da Zend_Db_Table, sem alterá-la, para que se necessário possa ser usada da forma usual. Então, para começar, vamos criar o método principal, que vai montar o select com as joins baseadas em $_referenceMap.

Vou colocar logo o código completo e explicando com comentários:

/**
* Gera automaticamente um Zend_Db_Table_Select fazendo join nas tabelas
* baseado nas informações do atributo $_referenceMap
*
* @param array $columns Lista de campos a serem retornados
*  do banco de dados (usado para ganho de performance).
*  Se for  null, todos as colunos serão retornados.
* @param array $referenceTables Lista de "rules" das tabelas a serem
*  acrescentadas na query (usado para ganho de performance)
*  Se null, todas as tabelas serão acrescentadas na query.
* return Zend_Db_Table_Select
*/
public function selectWithJoins(array $columns = null,
 array $referenceTables = null)
{
 $select = $this->select();
 
 // Verifica se há restrições nos campos a serem acrescentados
 $retrieveColumns = '*';
 if($columns) {
   $tableCols = $this->info('cols');
   $retrieveColumns = array_intersect($tableCols, $columns);
 }
 $select->from($this->_name, $retrieveColumns);
 
 // Verifica se há tabelas a serem acrescentadas com join na query
 // baseado no $_referenceMap
 if (count($this->_referenceMap) > 0) {
   $select->setIntegrityCheck(false);
   if (!isset($tableCols)) {
     $tableCols = $this->info('cols');
   }
   foreach($this->_referenceMap as $rule=>$rm) {
     // Verifica se há restrições nas tabelas a serem
     // acrescentadas
     if (null === $referenceTables ||
       in_array($rule, $referenceTables))
     {
       $refTableClass = new $rm['refTableClass'];
 
       // Campos das tabelas a serem acrescentadas
       // são renomeadas
       // para "NomeDaRule_NomeDoCampo"
       $refTableColumns = array();
       foreach ($refTableClass->info('cols') as $col) {
         $colAlias = "{$rule}_{$col}";
         // Verifica se há restrições nos campos a
         // serem acrescentados
         if (!$columns || in_array($colAlias, $columns)) {
           $refTableColumns[$colAlias] = $col;
         }
       }
 
       $usedAliases = array($this->_name);
 
       // Se não há campos a serem acrescentados,
       // a tabela não será acrescentada
       if (count($refTableColumns)) {
         $refTableName = $refTableClass->info(self::NAME);
 
         // Se refTable for igual a outra tabela, muda os aliases
         $refTableAlias = $refTableName;
         $i = 2;
         while(in_array($refTableAlias, $usedAliases)) {
           $refTableAlias = $refTableName . '_' . $i++;
         }
         $usedAliases[] = $refTableAlias;
 
         if (is_string($rm['refColumns'])) {
         // Se a chave primária é única
         $joinOnString =
           "{$refTableAlias}.{$rm['refColumns']}"
           . " = {$this->_name}.{$rm['columns']}";
         } else if (is_array($rm['refColumns'])) {
           // Se a chave primária é concatenada
           $joinOnArray = array();
           foreach($rm['refColumns'] as $key=>$rc) {
             $joinOnArray[] = "{$refTableAlias}.{$rc} = "
             . "{$this->_name}.{$rm['columns'][$k]}";
           }
           $joinOnString = implode(' AND ', $joinOnArray);
         }
 
         $select->joinLeft(
           $refTableName,
           $joinOnString,
           $refTableColumns
         );
      }
    }
   }
 }
 
 return $select;
}

Pronto, feito o select, já é possível gerar a query com os joins e usar, por exemplo, em um Zend_Paginator, ou fazer um fetchAll() e usar o resultado normalmente.
Mas, pra ficar ainda mais prático, vamos acrescentar o método fetch lá na nossa classe e também um método find pra retornar um único registro, baseado na chave primária.

/**
* Faz uma busca no banco de dados com joins nas tabelas relacionadas
* baseado no atributo $_referenceMap
*
* @param string|array $where  OPTIONAL An SQL WHERE clause.
* @param string|array $order  OPTIONAL An SQL ORDER BY clause.
* @param array    $columns OPTIONAL Campos a serem retornados
* @param array    $referenceTables OPTIONAL Tabelas a serem acrescentadas
* @param mixed    $fetchMode OPTIONAL Formato do resultado da busca
* @return mixed     Depende do valor de $fetchMode.
*/
public function fetchWithJoins($where = null, $order = null,
array $columns = null, array $referenceTables = null,
$fetchMode = Zend_Db::FETCH_OBJ)
{
$select = $this->selectWithJoins($columns, $referenceTables);
 
if ($where !== null) {
  $this->_where($select, $where);
}
 
if ($order) {
  $select->order($order);
}
 
return $this->getAdapter()->fetchAll($select, null, $fetchMode);
}
 
/**
* Faz uma busca usando a chave primária no banco de dados com joins
* nas tabelas relacionadas baseado no atributo $_referenceMap
*
* @param string|array $pkey  Valor da chave primária
* @param array    $columns OPTIONAL Campos a serem retornados
* @param array    $referenceTables OPTIONAL Tabelas a serem acrescentadas
* @param mixed    $fetchMode OPTIONAL Formato do resultado da busca
* @return mixed     Depende do valor de $fetchMode.
*/
public function findWithJoins($pkey, array $columns = null,
array $referenceTables = null, $fetchMode = null)
{
// Ver posts anteriores para entender este método 
$where = $this->_generateRestrictionsFromPrimaryKeys($pkey);
 
$result = $this->fetchWithJoins($where, null, $columns,
        $referenceTables, $fetchMode);
return count($result) == 1 ? current($result) : $result;
}

Agora fica fácil fazer uma busca com joins nas tabelas. Vamos reaproveitar o exemplo de Usuarios e Cidades.
Digamos que essas sejam as respectivas classes:

class Cidades extends My_Db_Table_Abstract
{
 protected $_name    = 'cidades';
 protected $_primary = 'id_cidade';
 
 protected $_dependentTables = array('Usuarios');
}
 
class Usuarios extends My_Db_Table_Abstract
{
 protected $_name    = 'usuarios';
 protected $_primary = 'id_usuario';
 
 protected $_referenceMap = array(
   'Cidade' => array(
     'columns'       => 'id_cidade',
     'refTableClass' => 'Cidades',
     'refColumns'    => 'id_cidade'
   ),
 )
}

Perceba que os relacionamentos estão bem definidos em $_referenceMap. Agora vamos fazer a busca que no interessa.

$idUsuario = 1;
$tbUsuarios = new Usuarios();
$usuario = $tbUsuarios->findWithJoins($idUsuario);

Se considerarmos que a tabela usuarios só tem os campos id_usuario, nome e id_cidade, e a tabela cidades tem id_cidade, nome e estado, teremos um resultado como o seguinte:

$usuarios = StdClass(
  'id_usuario'       => 1,
  'nome'             => 'Fulano de Tal',
  'id_cidade'        => 40,
  'Cidade_id_cidade' => 40,
  'Cidade_nome'      => 'João Pessoa',
  'Cidade_estado'    => 'PB'
);

Veja que os campos de cidade ganharam um prefixo com o nome da rule que foi definida no atributo $_referenceMap. Isso é pra evitar conflito entre nomes de campos iguais, como ocorre neste exemplo com id_cidade e nome, por exemplo.
Para obter mais resultados, você pode usar da mesma forma o método fetchWithJoins().

No caso de uma classe que faça raferência a muitas outras tabelas, essa consulta pode ficar muito pesada, e muitas vezes nem nos interessa num certo momento todos aqueles dados, então, pode ser passado como parâmetro apenas as tabelas que temos interesse, ou mesmo os campos que nos interessa, diminuindo, dessa forma, a carga sobre o banco de dados, já que diminuirá significativamente a quantidade de dados a serem retornados na consulta.

No exemplo seguinte, a query retornará apenas os campos nome e Cidade_nome de todos os usuários do estado da Paraíba ordenados pelo nome da cidade:

$where = array('estado = ?' => 'PB');
$order = array('cidades.nome ASC');
$usuarios = $tbUsuarios->fetchWithJoins($where, $order, 
                         array('nome', 'Cidade_nome'));

Imaginando que a tabela Usuarios faça referências a outras tabelas, e para a consulta só me interessam os dados da tabela de cidades (além das de usuarios, claro), o exemplo seguinte mostra como limitar os joins apenas as tabelas de interesse, utilizando as rules definidas em $_referenceMap:

$order = array('usuarios.nome ASC');
$usuarios = $tbUsuarios->fetchWithJoins(null, $order, null, 
                         array('Cidade'));

Perceba que a classe como está aqui apresentada só faz join em um nível de relacionamento. Por exemplo, se a tabela Cidades fizesse referência a uma tabela Estados, os dados de estados não entrariam no resultado dos exemplos citados. Quem sabe numa versão futura…

Ok. Agora ficou fácil fazer joins sem precisar ficar montando os selects toda hora, nem ficar fazendo centenas de acessos desnecessários ao banco.

Ainda assim, isso deve ser usado com cautela, já que consultas com join também pesam no banco, devendo-se optar sempre que possível pela utilização de Views de banco de dados ou tabelas tamporárias. Vale também uma olhada nas dicas de performance no site do Zend Framework a respeito da utilização da Zend_Db_Table (Saber mais).

Espero que tenham gostado! ^__^

·

11 comments

  • Raphael Almeida · 4 de julho de 2011 às 10:39

    Muito boa a sua ideia. Esse problema de N+1 queries dificulta um pouco a utilização padrão do Zend_Db_Table.

    Por que você não formaliza esses códigos em um componente e coloca no github.com para ganhar mas colaboradores. Eu mesmo estou interessado em evoluir essa ideia.

    []ão

    Responder

  • Mingomax · 27 de julho de 2011 às 11:41

    Muito boa sua idéia e bem implmentado, eu tava trabalhando em algo parecido também.

    E vc já pensou em compartilhar isso com a comunidade? Para extender ainda mais?

    Abraços.

    Responder

  • Alan · 18 de fevereiro de 2012 às 21:37

    Gostei muito da ideia, mas estou com uma dúvida.
    O $_dependentTables não deveria estar na tabela onde estão as FKs e o $_referenceMap nas tabelas onde ficam as PKs?

    Responder

    • Admin comment by jaime · 18 de fevereiro de 2012 às 22:50

      Alan, acho que você se confundiu aí. $_referencaMap serve exatamente para dizer a que outras tabelas aquelas FK referenciam, e $_dependentTables pra dizer que outras tabelas fazem referência a esta tabela. Então está correto. Dê uma olhada na documentação do Zend (http://www.zendframework.com/manual/1.11/en/zend.db.table.relationships.html) que você vai ver que é assim mesmo, ok?

      Responder

  • Admin comment by jaime · 26 de fevereiro de 2012 às 0:40

    Fiz uma atualização no código agora há pouco, no método selectWithJoins().

    Acrescentei um teste para o caso de tabelas que têm duas ou mais referências para uma mesma tabela. Neste caso, é preciso renomear os aliases nos joins.

    Responder

  • Rene Bentes Pinto · 19 de junho de 2012 às 15:03

    Jaime, como vai?

    Estou iniciando no Zend e, testando o seu código obtive erros com a adequação de nomes das classes dos models. Por exemplo, no arquivo Role.php a classe está declarada como Application_Model_DbTable_Role. E quando executo selectWithJoin() não encontra a tabela no banco “Application_Model_DbTable_Role”, pois obviamente não existe. Como resolvo isso? Já procurei no Pai (Google) e não acho.

    Obrigado e desculpe a ignorância no assunto.

    Responder

    • Admin comment by jaime · 19 de junho de 2012 às 16:15

      Cara, isso aí vai depender de como está sua implementação. Primeira coisa, quando você instancia a classe num controller assim: new Application_Model_DbTable_Role(), funciona?

      Senão, você tem que adicionar no application.ini a seguinte linha: appnamespace = “Application”. Além disso, sua classe deve estar no caminho application/models/DbTable/Role.php.

      Se tudo isso estiver certo, aí tem que ver se a classe que você está chamando o selectWithJoins() está com o $referenceTables configurado corretamente…

      Ah… em breve devo estar colocando meus códigos do blog no github. Isso deverá facilitar pra quem quiser utilizá-los! ^__^

      Responder

      • Rene Bentes Pinto · 20 de junho de 2012 às 8:39

        Jaime,

        Funciona para apenas uma tabela, no caso de uma $_referenceTables a função procura pela classe com o nome completo.

        Já está no application.ini com a configuração recomendada.

        As classes estão desta forma:

        <?php

        /**
        * Classe para mapeamento da tabela Role
        * @author Rene Bentes Pinto
        * @see application/models/DbTable/Role.php
        */

        class Application_Model_DbTable_Role extends Makesoft_Model_Repository
        {

        protected $_name = 'Role';
        protected $_primary = 'id';
        protected $_dependentTables = array('User','Permission');
        }

        array(
        ‘columns’ => ‘idRole’,
        ‘refTableClass’ => ‘Role’,
        ‘refColumns’ => ‘id’
        ),
        ‘Graduation’ => array(
        ‘columns’ => ‘idGraduation’,
        ‘refTableClass’ => ‘Graduation’,
        ‘refColumns’ => ‘id’
        )
        );
        }

        Responder

        • Rene Bentes Pinto · 20 de junho de 2012 às 9:01

          Jaime,

          Consegui resolver o problema, não havia atentado para o atributo refTableClass de $_referenceTables. Ali você deve informar o nome completo da classe.

          Obrigado pela ajuda.

          Responder

  • Robson Alexandre · 29 de outubro de 2012 às 0:01

    Prezado Jaime Neto,

    segundo as boas práticas, onde deveria ser armazenado o arquivo Abstract.php de minha classe My_Db_Table_Abstract. Seria library/My/Db/Table/Abstract.php ?
    De que formas eu posso carregá-la ou auto carregar, para extender de todas minhas DbTables?

    Agradeço sua atenção e aguardamos a disponibilização dos códigos no github.

    Atenciosamente
    Robson Alexandre

    Responder

    • Admin comment by jaime · 4 de novembro de 2012 às 12:07

      Exatamente, Robson. Ponha a classe em library/My/Db/Table/Abstract.php. Se seu autoload estiver ativado ele encontra sozinho baseado no nome da classe, basta você extendêlas de My_Db_Table_Abstract.

      Responder

Leave a Reply

<<

>>

Theme Design by devolux.nh2.me