We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

How to access columns when using leftJoin

Hi! I have these two tables:

CREATE TABLE `administradores` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `nome` varchar(50) NOT NULL DEFAULT '',
  `email` varchar(100) NOT NULL DEFAULT '',
  `senha` char(64) NOT NULL DEFAULT '',
  `criado_em` datetime DEFAULT NULL,
  `modificado_em` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
---------------------------------------------------------------------
CREATE TABLE `lista_negra` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ip` varchar(15) NOT NULL DEFAULT '',
  `agente` varchar(255) DEFAULT NULL,
  `data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `motivo` varchar(100) DEFAULT NULL,
  `usuario` int(11) unsigned NOT NULL DEFAULT '0',
  `criado_em` datetime DEFAULT NULL,
  `modificado_em` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `usuario` (`usuario`),
  CONSTRAINT `lista_negra_ibfk_1` FOREIGN KEY (`usuario`) REFERENCES `administradores` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
---------------------------------------------------------------------

I'm trying to list all rows on lista_negra with some data of administradores. First I just used:

$builder = $this->getQueryBuilder()
            ->leftJoin( 'Reserver\\Models\\Administradores' );

$this->view->setVar( 'paginator', $Crud->getListagem( $builder ) );

public function getListagem( \Phalcon\Mvc\Model\Query\Builder $builder ) {
        $paginator = (
        new \Phalcon\Paginator\Adapter\QueryBuilder( [
            'builder'   => $builder,
            'limit'     => 12,
            'page'      => 1
        ] )
        );
        return $paginator->getPaginate();
    }

Then I was listing like this:

foreach( $paginator->items as $row ):
    printf(
        '....',
        $row->getId(),
        $row->getIp(),
        $row->getMotivo(),
        $row->getAdministradores()->getNome(),
    )

But when I did this, for each getAdministradores() I was calling a different SELECT:

    77 Query    SELECT administradores.id, administradores.nome, administradores.email, administradores.senha, administradores.criado_em, administradores.modificado_em FROM administradores WHERE administradores.id = '2' LIMIT 1
    77 Query    SELECT administradores.id, administradores.nome, administradores.email, administradores.senha, administradores.criado_em, administradores.modificado_em FROM administradores WHERE administradores.id = '3' LIMIT 1
    77 Query    SELECT administradores.id, administradores.nome, administradores.email, administradores.senha, administradores.criado_em, administradores.modificado_em FROM administradores WHERE administradores.id = '2' LIMIT 1
    77 Query    SELECT administradores.id, administradores.nome, administradores.email, administradores.senha, administradores.criado_em, administradores.modificado_em FROM administradores WHERE administradores.id = '2' LIMIT 1

I would like to know how to access properly the joined columns from the Query Builder.

I already tried some variations, like using the columns(), addFrom() or join(), but I really I'm confused about how to get this working.

Thanks!



7.1k
Accepted
answer
edited Mar '15

Finally I figured out how to get this working. I hope it helps someone:

$builder = $this->modelsManager->createBuilder();
$join1 = 'Administradores';
$builder
    ->addFrom( 'Reserver\\Models\\ListaNegra', 'ListaNegra' );
    ->columns( [ 'ListaNegra.*', "{$join1}.*" ] )
    ->leftJoin( "Reserver\\Models\\{$join1}", null, $join1 );

And to read it's simple:

foreach( $paginator->items as $row ):
    echo $row->ListaNegra->getId();
    echo $row->ListaNegra->getIp();
    echo $row->ListaNegra->getMotivo();
    echo $row->Administradores->getNome();
endforeach;

Thanks!

Cool solution. Thanks