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!