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

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.

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

报告相同问题?

悬赏问题

  • ¥60 pb数据库修改或者求完整pb库存系统,需为pb自带数据库
  • ¥15 spss统计中二分类变量和有序变量的相关性分析可以用kendall相关分析吗?
  • ¥15 拟通过pc下指令到安卓系统,如果追求响应速度,尽可能无延迟,是不是用安卓模拟器会优于实体的安卓手机?如果是,可以快多少毫秒?
  • ¥20 神经网络Sequential name=sequential, built=False
  • ¥16 Qphython 用xlrd读取excel报错
  • ¥15 单片机学习顺序问题!!
  • ¥15 ikuai客户端多拨vpn,重启总是有个别重拨不上
  • ¥20 关于#anlogic#sdram#的问题,如何解决?(关键词-performance)
  • ¥15 相敏解调 matlab
  • ¥15 求lingo代码和思路