我正在为创建的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
因为访问->参与者对查询生成器创建的结果,所以也做了额外的查询。
要使用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下进行了测试)使Phalconpi
在项目的Resultsset中创建了参与者的一些后续ResultSet对象p
。
您仍然可以使用进行遍历,foreach()
但毕竟我不确定它是否减少了最终查询数。
进行的射击仍保留为Resultset,因此您应$result = $resultSet->toArray()
对此$result['pi']
保持谨慎。您可以通过在columns()
参数中定义确切的列来强制将其转储为数组。它有其缺点-groupBy()
至少在这里运行的Phalcon 1.3.2和PHP 5.5.3 im上,您将不再从中受益。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句