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

Column subquery

Hi i'm tying to perform a subquery in a column, its works fine, but i cannot assign it to any model with the c. do not works, if only metas work but i cannot access from any object only direct $row->metas i would like to access like it $row->c->metas

->columns( array( 'c.','m.', 'm2.*', 'c.metas' => $this->groupConcat(array('chave' => 'valor'), 'FROM \Apps\Models\ConteudosMeta as cm where cm.id_conteudo = c.id')));

thanks

What does groupConcat makes?

edited Jul '15

Sql

$select = $this->getModelsManager()->createBuilder() ->addFrom('\Apps\Models\Conteudos', 'c') ->leftJoin('\Apps\Models\Midias', 'm.id = c.id_midias', 'm') ->leftJoin('\Apps\Models\Midias', 'm2.id = c.id_destaque', 'm2') ->columns( array( 'c.','m.', 'm2.*', 'metas' => $this->groupConcat(array('chave' => 'valor'), 'FROM \Apps\Models\ConteudosMeta as cm where cm.id_conteudo = c.id')));

Group concat make a JSON

"(SELECT CONCAT('{',GROUP_CONCAT(CONCAT('"',chave,'":"',IFNULL(valor, 0),'"')),'}' ) FROM \Apps\Models\ConteudosMeta as cm where cm.id_conteudo = c.id)"

Its return an JSON $model->metas

{"_home":"home","_router":"index:index"}>

The query is working fine, however $model->metas, i wish to access from Conteudos model, like $model->c->metas

I already tried to use c.metas => sql but does not work,

It's possible from a Model get all result from a row example $model->c->m->titulo? get title from midias



34.6k
Accepted
answer
edited Jul '15

Probably you can't use a qualified identifier as alias. What about?

->columns( array( 'c.','m.', 'm2.*', 'meta' => $this->groupConcat(array('chave' => 'valor'), 'FROM \Apps\Models\ConteudosMeta as cm where cm.id_conteudo = c.id')));

Then you can access it as:

$r->meta;