Geek Answers Handbook

Phalcon Performance Issues

I am creating a REST API for outputting endpoints / projects. I created two models:

Projects

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

The participants:

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

Let's say I have 10 projects: (1 request)

$results = Projects::find();

I go through all 10 of them, but I also want all the participants:

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

So, at the end of the cycle, Phalcon made an additional request for each project.

These queries were made by accessing the participants of $ result->, iterating over 10 projects:

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

Question

Is there a way to request a relationship before hand, so this will be a single request. When I use the query builder provided by Phalcon, I cannot access the participants -> in the same way.

Edit

I ended up using Query Builder, namespacing all columns

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

Columns:

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

Since the access β†’ of the participants to the result created by Query Builder also performed additional queries.

+4
2

β†’ , 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() , , , .

( PgSQL) Phalcon ResultSet pi p.

foreach(), , , .

Fireing $result = $resultSet->toArray() made $result['pi'] , . , columns(). - groupBy(), , Phalcon 1.3.2 PHP 5.5.3 im .

+5

phalcon.

stibiumz phalcon eager loading

N + 1 . . .

, IN .

a :

SELECT * FROM main
SELECT * FROM related WHERE x.id IN (results from the previous resultset)
0

More articles: