dongyuxin5353 2015-03-12 14:36
浏览 221
已采纳

Phalcon | 一个查询中有两个用于“belongsTo”

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 :)

  • 写回答

1条回答 默认 最新

  • douyabu1528 2015-03-16 09:40
    关注
    $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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?