Tag Select "Using" Concat of columns from related models

With relationships (you need a model for each table you use): https://docs.phalcon.io/pt/latest/reference/models.html#relationships-between-models

After that, do something like:

$naturalProject = Projects::find(do the ID search);
$modifiedProject = array(
    'id' => $naturalProject->id,
    'concat' => ($naturalProject->id,
        ' - ',
        $naturalProject->sites->client->name,
        ', ',
        $naturalProject->sites->adress->city,
        );
    );
$this->view->setVar('projects', $modifiedProject);

(Not sure if with that type of tables will work).

@RompePC: I've set up the models correclty and can access the relate model fields from the view.

looking at your solution, is the a way i can offload it to the model? there are a number of locations i may use this particular dropdown and i dont want to have to copy it ever time i need it.

Declare a function in your Projects model that does that and return $modifiedProject. Nothing important.

A model is just a class representation of a table: you can do what do you want with it. Rember to declare static the function, so you access it with Project::modifiedProject.

Thanks RomePC. I tried your suggestion first, but it nearly killed my firefox as it was running a 3 queries for every project id as I was looping through all the projects under the find(). As such I tried a slightly different approach by using the query builder to return the full resultset as I needed it, rather than building the array up manually.

This is what I ended up with:

    static public function dropdown()
    {

        $projects = new \Phalcon\Mvc\Model\Query\Builder();

        $projects = $projects->columns(array(
                "Projects.id",
                "CONCAT(Projects.id, ' - ', Client.name, ', ', Address.city) as dropdown"
                )
            )
            ->from(array("Projects" => "\\my-app\\Models\\PmsProjects"))
            ->join("\\my-app\\Models\\CrmSites", "Site.id = Projects.site_id", "Site")
            ->join("\\my-app\\Models\\CrmClients", "Client.id = Site.client_id", "Client")
            ->join("\\my-app\\Models\\CoreAddresses", "Address.id = Site.address_id", "Address")
            ->where('Projects.phase_id < 8')
            ->orderBy("Projects.id DESC")
            ->getQuery()
            ->execute();

        return $projects;
    }

Then i just call it using $this->view->setVar("projects", Projects::dropdown()); and it works perfectly with the volt template.

Thanks for your help.

I don't know how can it be killing your Firefox: I'm using it too, and I don't have problems (and I have more much functions like I coded before). However, you don't need to create a Builder for that: just do Projects::query()->columns(...)->... and skip the ->getQuery() method (as far as I've read here https://docs.phalcon.io/es/latest/api/Phalcon_Mvc_Model_Criteria.html )

The reason it was killing it was that I have profiling turned on and I'm printing the queries at the bottom of my content (for testing/debugging). I'm then running a JS syntax highlighter on the SQL queries, to make them easier to read. When you have 3000+ queries that JS has to process things tend to slow down a little lol.

As a slight aside, the query profiler was saying that the loop of SQL queries were taking up nearly 0.5secs to complete whereas the single query is only 0.00415s i.e. about 100x faster. I think it's also better that the DB does the processing and not php.

I've tried your suggestion, but I'm struggling with the ->from() part as it's not a function of the Criteria. Is there a way to get around it?

Well, didn't knew that about the killing reason.

Yes, doing all in the DB directly saves time 'cause of no need to translate PHP SQL to the DB. But with complicated queries it gets easier with PHP.

When you do something like Projects::find(), what value do you think from() takes? (if you still have problems, try changing the source of the model with public function getSource() { return "string"; }).