One query insted of two for “belongsTo”

Question

One query insted of two for “belongsTo”

evyasonov Mar '15

Created Mar '15	Last Reply Mar '15	Replies 1	Views 813	Votes 1

evyasonov 980

Mar '15

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 [email protected] 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?

Thanks :)

1

Shad Mickelberry · Answer 1 · 2015-03-13T09:10:15-07:00

Yes the modelsManager has this functionality to build query. Set up in DI

$di = new Phalcon\DI();

$di->set('modelsManager', function() { return new Phalcon\Mvc\Model\Manager(); });

This is an example in the documentation

$resultset = $this->modelsManager->createBuilder() ->from('Robots') ->join('RobotsParts') ->limit(20) ->orderBy('Robots.name') ->getQuery() ->execute();

evyasonov · Answer 2 · 2015-03-13T09:19:00-07:00

Thank you

But it's too heavy for writing if there's a model with 20 belongTo relations. Is it possible to get all data from related tables in one query without modelsManager and Builder?

Is it possible to do that automaticly for every query to the model by passing some "automatic JOIN for every belongsTo" parameter?

I want somethink like this:

$robot = Robots::findFirst(2, array( "automaticJoinForBelongsTo" => true  ) );
echo $robot->RobotsTypes->type;

or this

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

I think that it's natural, it's logical, it's intuitive.

Yes the modelsManager has this functionality to build query. Set up in DI

$di = new Phalcon\DI();

$di->set('modelsManager', function() { return new Phalcon\Mvc\Model\Manager(); });

This is an example in the documentation

$resultset = $this->modelsManager->createBuilder() ->from('Robots') ->join('RobotsParts') ->limit(20) ->orderBy('Robots.name') ->getQuery() ->execute();