Phalcon性能相关查询

一尘不染

Phalcon性能相关查询

mysql

我正在为创建的2个模型的终结点/项目的输出构建REST API：

项目：

class Projects extends BaseModel
{
    public function initialize()
    {
        $this->hasMany('id', 'Participants', 'projectId');
    }
}

参加者：

class Participants extends BaseModel
{
    public function initialize()
    {
        $this->belongsTo('projectId', 'Projects', 'id');
    }
}

可以说，我有10个项目：（1个查询）

$results = Projects::find();

我遍历了所有10个人，但我也希望所有参与者：

foreach($results as $result) {
    echo $result->participants; // 1 query
}

因此，在循环结束时，Phalcon对每个项目进行了额外的查询。

这些查询是通过访问$ result->参与者进行的，同时迭代了10个项目：

SELECT IF(COUNT(*)>0, 1 , 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME`='projects'
DESCRIBE `projects`
SELECT `projects`.`id`, `projects`.`title`, `projects`.`client`, `projects`.`color`, `projects`.`start_date`, `projects`.`end_date`, `projects`.`notes`, `projects`.`stateId`, `projects`.`created_at`, `projects`.`updated_at` FROM `projects`
SELECT IF(COUNT(*)>0, 1 , 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME`='project_participants'
DESCRIBE `project_participants`
SELECT `project_participants`.`id`, `project_participants`.`project_id`, `project_participants`.`user_id`, `project_participants`.`user_role_id`, `project_participants`.`user_state_id`, `project_participants`.`updated_at`, `project_participants`.`created_at` FROM `project_participants` WHERE `project_participants`.`project_id` = :0
SELECT `project_participants`.`id`, `project_participants`.`project_id`, `project_participants`.`user_id`, `project_participants`.`user_role_id`, `project_participants`.`user_state_id`, `project_participants`.`updated_at`, `project_participants`.`created_at` FROM `project_participants` WHERE `project_participants`.`project_id` = :0
SELECT `project_participants`.`id`, `project_participants`.`project_id`, `project_participants`.`user_id`, `project_participants`.`user_role_id`, `project_participants`.`user_state_id`, `project_participants`.`updated_at`, `project_participants`.`created_at` FROM `project_participants` WHERE `project_participants`.`project_id` = :0
SELECT `project_participants`.`id`, `project_participants`.`project_id`, `project_participants`.`user_id`, `project_participants`.`user_role_id`, `project_participants`.`user_state_id`, `project_participants`.`updated_at`, `project_participants`.`created_at` FROM `project_participants` WHERE `project_participants`.`project_id` = :0
SELECT `project_participants`.`id`, `project_participants`.`project_id`, `project_participants`.`user_id`, `project_participants`.`user_role_id`, `project_participants`.`user_state_id`, `project_participants`.`updated_at`, `project_participants`.`created_at` FROM `project_participants` WHERE `project_participants`.`project_id` = :0
SELECT `project_participants`.`id`, `project_participants`.`project_id`, `project_participants`.`user_id`, `project_participants`.`user_role_id`, `project_participants`.`user_state_id`, `project_participants`.`updated_at`, `project_participants`.`created_at` FROM `project_participants` WHERE `project_participants`.`project_id` = :0
SELECT `project_participants`.`id`, `project_participants`.`project_id`, `project_participants`.`user_id`, `project_participants`.`user_role_id`, `project_participants`.`user_state_id`, `project_participants`.`updated_at`, `project_participants`.`created_at` FROM `project_participants` WHERE `project_participants`.`project_id` = :0
SELECT `project_participants`.`id`, `project_participants`.`project_id`, `project_participants`.`user_id`, `project_participants`.`user_role_id`, `project_participants`.`user_state_id`, `project_participants`.`updated_at`, `project_participants`.`created_at` FROM `project_participants` WHERE `project_participants`.`project_id` = :0
SELECT `project_participants`.`id`, `project_participants`.`project_id`, `project_participants`.`user_id`, `project_participants`.`user_role_id`, `project_participants`.`user_state_id`, `project_participants`.`updated_at`, `project_participants`.`created_at` FROM `project_participants` WHERE `project_participants`.`project_id` = :0

题

有没有一种方法可以事先查询关系，因此将是一个查询。当我使用Phalcon提供的查询生成器时，我无法以相同的方式访问->参与者。

编辑

我最终使用查询生成器，对所有列进行命名

$builder = $modelsManager->createBuilder();
$builder->columns($columns)
        ->from('Projects')
        ->leftJoin('Participants')
        ->getQuery()
        ->execute();

像这样的列：

Projects.id as projects_id
...
Participants.id as participants_id
Participants.projectId as participants_projectId

因为访问->参与者对查询生成器创建的结果，所以也做了额外的查询。

阅读 234

2020-05-17

共1个答案

一尘不染

要使用QueryBuilder以相同的方式访问->参与者，您将必须在Query中建立联接。

代码示例可能类似于：

$queryBuilder = $this->getDI()->getModelsManager()
    ->createBuilder()
    ->columns(['p.id','participants.*'])
    ->addFrom('Entity\Projects', 'p')
    ->leftJoin('Entity\Participants', 'participants.projectId = p.id', 'participants')
    ->groupBy('p.id, participants.id')
    ->orderBy('p.id ASC');

$resultSet = $queryBuilder->getQuery()->execute();

groupBy() 这里使用by来使结果可能是多维的。

这种查询（在PgSQL下进行了测试）使Phalcon pi在项目的Resultsset中创建了参与者的一些后续ResultSet对象p。

您仍然可以使用进行遍历，foreach()但毕竟 我不确定它是否减少了最终查询数 。

进行的射击仍保留为Resultset，因此您应$result = $resultSet->toArray()对此$result['pi']保持谨慎。
您可以通过在columns()参数中定义确切的列来强制将其转储为数组。它有其缺点- groupBy()至少在这里运行的Phalcon
1.3.2和PHP 5.5.3 im上，您将不再从中受益。

2020-05-17