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

Include 3 columns from 2 models in a relationship.

Hello everybody, I am trying to create a group of parameters to complete a rows selection based on two models with a relationship. I need to concatenate the 'NombreRuta' and 'FechaRuta' columns from the Georutaline model and the 'SalesRepEntityRef_FullName' column from the Salesrep model.

But I am getting the following message: "Unknown model or alias 'Salesrep'"

Thank you in advance. Happy quarentine.

        $parametros = array(
            'conditions' => 'Featured = 1', 
            'columns' => 'RutaID, CONCAT(NombreRuta,  ", ", FechaRuta, ", ", Salesrep.SalesRepEntityRef_FullName) AS RutaNombre'
        );

        $rutas = Georutaline::find($parametros);


125.7k

When you call find() on a model, you're just querying a single table. As far as I know, you can't do a combined query like this with find() or even getRelated().

This looks like a problem that would best be solved by writing a PHQL query and using modelsManager or queryBuilder.



12.8k
edited Apr '20

You can use subquery like this, its easier to use columns map there and replace where clause corresponding to your relation 

$parametros = array(
            'conditions' => 'Featured = 1', 
            'columns' => 'RutaID, (SELECT CONCAT(NombreRuta,  ", ", FechaRuta, ", ", Salesrep.SalesRepEntityRef_FullName) FROM Salesrep WHERE Salesrep.id = Georutaline.id) AS RutaNombre'
);

$rutas = Georutaline::find($parametros);

Anyway with the model alias you can access to the relation with the result :

$parametros = array(
            'conditions' => 'Featured = 1', 
            'columns' => 'RutaID, NombreRuta, FechaRuta'
);

$rutas = Georutaline::find($parametros);    
foreach($rutas as $ruta){
            $rutaNombre = $ruta->NombreRuta.','.$ruta->FechaRuta.','.$ruta->alias_relation_salesrep->SalesRepEntityRef_FullName;
}