Phalcon | One query insted of two for "belongsTo"

Question

Is it possible to make one JOIN-query instead of two SELECT-queries if a model has relation belongsTo?

I mean that if there's two models:

<?php

class Robots extends \Phalcon\Mvc\Model
{
    public $id;

    public $name;

    public $type_id;

    public function initialize()
    {
        $this->belongsTo("type_id", "RobotsTypes", "id");
    }

}

and

<?php

class RobotsTypes extends \Phalcon\Mvc\Model
{

    public $id;

    public $type;

}

And I'm trying to get robot type:

$robot = Robots::findFirst(2);
echo $robot->RobotsTypes->type;

Then Phalcone makes two SELECT-queries:

150312 14:41:02 49 Connect robots@localhost on robots
49 Query SELECT IF(COUNT(*)>0, 1 , 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME`='robots'
49 Query DESCRIBE `robots`
49 Query SELECT `robots`.`id`, `robots`.`name`, `robots`.`type_id` FROM `robots` WHERE `robots`.`id` = '2' LIMIT 1
49 Query SELECT IF(COUNT(*)>0, 1 , 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME`='robot_types'
49 Query DESCRIBE `robot_types`
49 Query SELECT `robot_types`.`id`, `robot_types`.`type` FROM `robot_types` WHERE `robot_types`.`id` = '2' LIMIT 1
49 Quit

Is it possible to make Phalcon execute just 1 query with JOIN?

SELECT `robots`.`id`, `robots`.`name`, `robots`.`type_id`, `robot_types`.`id`, `robot_types`.`type` FROM `robots` JOIN `robot_types` ON `robots`.`type_id` = `robot_types`.`id` WHERE `robots`.`id` = '2' LIMIT 1

I know that using views can solve the problem. But it needs to create a view and one more model.

Is it possible to do this only in Phalcone without using PHQL? For exmaple, by specifying addition argument in belongsTo method?

It's similar with Phalcon performance related queries

Thanks :)

Answer 1

$queryBuilder = $this->getDI()->getModelsManager()
    ->createBuilder()
    ->columns(['r.id','r.name', 'r.type_id', 'rt.type'])
    ->addFrom('Robot', 'r')
    ->leftJoin('RobotTypes', 'rt.id = r.type_id', 'rt');

$resultSet = $queryBuilder->getQuery()->execute();//->toArray(); //optional

If you call by name all columns you need, you should be able to retrieve full result w/o multiquerying DB for types separately. It's still not a PHQL, it also does not require declarations of belongsTo() to work properly.

As far as i know, you're not able to fetch joinable things only using models - It's because implementation would be too complex in case of multirelational tables, and thats for what queryBuilder is designed.