Phalcon ORM relations query optimization

Question

Phalcon ORM relations query optimization

Wajdi Jurry Apr '19

Created Apr '19	Last Reply Oct '20	Replies 7	Views 502	Votes 0

Wajdi Jurry 7.0k

Apr '19

I am using models relations offered by Phalcon, but there is an issue with queries optimization. I see when fetching many records from reference model, It hits a separate query for each record to get data from related models, but it's bad on a big scale, from server load corner.

Is there any way to optimize this issue ? In other words, is there a way to hit one query to get related records then map them to reference model ?

Balázs Németh · Answer 1 · 2019-04-27T10:43:30-07:00

You could try eager loading, it's in the incubator currently: https://github.com/phalcon/incubator/tree/master/Library/Phalcon/Mvc/Model/EagerLoading

Also make sure to use reusable relations (as default) and metadata caching for optimal performance.

Wajdi Jurry · Answer 2 · 2019-04-27T13:41:16-07:00

Thanks for replying, but it does not answer my question. I need a way to hit one time at database to get related records, not a hit per record which EagerLoading does.

You could try eager loading, it's in the incubator currently: https://github.com/phalcon/incubator/tree/master/Library/Phalcon/Mvc/Model/EagerLoading

Also make sure to use reusable relations (as default) and metadata caching for optimal performance.

Nikolaos Dimopoulos · Answer 3 · 2019-04-27T16:45:23-07:00

You can always use the reusable = true option to your relationships. Doing so will cache the relationship for the current request.

$this->hasOne(
    'local_id,
    Model1::class,
    'remote_id',
    [
        'alias'    => 'relationshipname',
         'reusable' => true,
    ]
);

Then whenever you call

$results = $model->getRelated('relationshipname');

will bring back the cached data.

Wajdi Jurry · Answer 4 · 2019-04-28T12:33:41-07:00

Thanks for replying, but unfortunately, it is not the answer which I am looking for.

Assume you have two tables, A and B. Table A has 2 records and related to table B with one-many relationship. Table B has 6 records, and referenced to table A by foriegn key, so each record in table A has 3 records in table B.

When fetching all records from table A, Phalcon will hit a separate query to get the related records from table B for each result , which is the current behavior.

Is there any way to hit one query on table B to get all records then map them to thier related reocrds in table A ? This is my question.

Thanks in advance.

You can always use the reusable = true option to your relationships. Doing so will cache the relationship for the current request.
$this->hasOne(
   'local_id,
   Model1::class,
  'remote_id',
  [
       'alias'    => 'relationshipname',
        'reusable' => true,
   ]
);
Then whenever you call
$results = $model->getRelated('relationshipname');
will bring back the cached data.

Nikolaos Dimopoulos · Answer 5 · 2019-04-28T17:43:20-07:00

So that I understand what you are looking for correctly.

Table Products

id	name
1	Printer
2	Software

Table Types

id	product_id	type	weight
1	1	physical	1.5
2	1	physical	2.5
3	1	physical	3.5
4	2	downloadable	null
5	2	downloadable	null
6	2	downloadable	null

Models

class Products
{
    public initialize()
    {
        $this->setSource('products');

        // All products
        $this->hasMany(
            'id,
            Types::class,
            'product_id',
            [
                'alias'    => 'types',
                'reusable' => true,
            ]
        );

        // Physical
        $this->hasMany(
            'id,
            Types::class,
            'product_id',
            [
                'alias'    => 'physical',
                'reusable' => true,
                'params'   => [
                    'conditions' => 'type = :type:',
                    'bind'       => [
                        'type' => 'physical',
                    ]
                ]
            ]
        );

        // Downloadable
        $this->hasMany(
            'id,
            Types::class,
            'product_id',
            [
                'alias'    => 'downloadable',
                'reusable' => true,
                'params'   => [
                    'conditions' => 'type = :type:',
                    'bind'       => [
                        'type' => 'downloadable',
                    ]
                ]
            ]
        );
    }
}

Queries:

Products::find() // one query returns 2 records

$product->getRelated('products') // one query - returns all records related to this product

$product->getRelated('physical') // one query - returns all physical related to this product

$product->getRelated('downloadable') // one query - returns all downloadable related to this product

That is if you use find and getRelated.

If you use the builder you can get all the data in one go:


$builder = new Builder();

$data = $builder
    ->columns('p.id, t.type, p.name, t.weight')
    ->addFrom(Products::class, 'p')
    ->innerJoin(Types::class, 'id = product_id', 't')
    ->orderBy('t.type, p.name')
    ->executeQuery()
    ....

That will be one query for both tables and all the data.

Can you explain a bit on what you really wanted to do? According to the issue you opened here the resultset you want back is a multi dimentional array/object that groups things according to what you want (in this case the product type)?

Dylan Anderson · Answer 6 · 2019-05-01T13:02:50-07:00

I don't believe there's any way to do this. Using the QueryBuilder can get you all the data you want. However, since you're asking for data from multiple tables, it will only return objects or arrays, it won't return Models. If you just want the data and not the functionality - for example if you're outputing a table of data - then QueryBuilder will work.

Wajdi Jurry · Answer 7 · 2019-05-01T14:06:12-07:00

Thanks for clarification and writing an example. Yes, your example is close about my needs.

The issue opened is not related to this discussion at all. What I was looking for is to hit less queries on database as much as I can, but the NFR is to do inheritcance mapping on records instead of doing them manually.

So that I understand what you are looking for correctly.

Table Products

id name

1 Printer

2 Software

Table Types

id product_id type weight

1 1 physical 1.5

2 1 physical 2.5

3 1 physical 3.5

4 2 downloadable null

5 2 downloadable null

6 2 downloadable null

Models
class Products
{
   public initialize()
   {
       $this->setSource('products');

       // All products
       $this->hasMany(
           'id,
           Types::class,
           'product_id',
           [
               'alias'    => 'types',
               'reusable' => true,
           ]
       );

       // Physical
       $this->hasMany(
           'id,
           Types::class,
           'product_id',
           [
               'alias'    => 'physical',
               'reusable' => true,
               'params'   => [
                   'conditions' => 'type = :type:',
                   'bind'       => [
                       'type' => 'physical',
                   ]
               ]
           ]
       );

       // Downloadable
       $this->hasMany(
           'id,
           Types::class,
           'product_id',
           [
               'alias'    => 'downloadable',
               'reusable' => true,
               'params'   => [
                   'conditions' => 'type = :type:',
                   'bind'       => [
                       'type' => 'downloadable',
                   ]
               ]
           ]
       );
   }
}
Queries:

Products::find() // one query returns 2 records

$product->getRelated('products') // one query - returns all records related to this product

$product->getRelated('physical') // one query - returns all physical related to this product

$product->getRelated('downloadable') // one query - returns all downloadable related to this product

That is if you use find and getRelated.

If you use the builder you can get all the data in one go:
$builder = new Builder();

$data = $builder
   ->columns('p.id, t.type, p.name, t.weight')
   ->addFrom(Products::class, 'p')
   ->innerJoin(Types::class, 'id = product_id', 't')
   ->orderBy('t.type, p.name')
   ->executeQuery()
   ....
That will be one query for both tables and all the data.

Can you explain a bit on what you really wanted to do? According to the issue you opened here the resultset you want back is a multi dimentional array/object that groups things according to what you want (in this case the product type)?

Wajdi Jurry · Answer 8 · 2019-05-01T14:08:47-07:00

Yes, I agree, the query builder will do the job, but I need more performed way to do that instead of writing queries depending on ORM.

I don't believe there's any way to do this. Using the QueryBuilder can get you all the data you want. However, since you're asking for data from multiple tables, it will only return objects or arrays, it won't return Models. If you just want the data and not the functionality - for example if you're outputing a table of data - then QueryBuilder will work.

talal424 · Answer 9 · 2019-05-06T17:31:27-07:00

for me i do both cache and inner joins

for example:

let say this is our tables:

CREATE TABLE robots (
    id int(10) unsigned NOT NULL AUTO_INCREMENT,
    name varchar(70) NOT NULL,
    type varchar(32) NOT NULL,
    year int(11) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE robots_parts (
    id int(10) unsigned NOT NULL AUTO_INCREMENT,
    robots_id int(10) NOT NULL,
    parts_id int(10) NOT NULL,
    created_at DATE NOT NULL,
    PRIMARY KEY (id),
    KEY robots_id (robots_id),
    KEY parts_id (parts_id)
);

CREATE TABLE parts (
    id int(10) unsigned NOT NULL AUTO_INCREMENT,
    name varchar(70) NOT NULL,
    PRIMARY KEY (id)
);

now if i want to query robots_parts

RobotsParts::find();

this would return resultset of RobotsParts

if you want for example Robots, RobotsPartsand Parts in a single query you have to use inner joins whether using phql or query builder

this is a static method in RobotsParts

 public static function getAll()
{
    $di = Phalcon\Di::getDefault();

    $modelsManager = $di->getModelsManager();

    // assuming you made all the relationships in all models involved

    // otherwise you have to specify primary keys in inner joins (  INNER JOIN Robots on RobotsParts.robots_id = Robots.id )

    $phql = 'SELECT RobotsParts.*, Robots.*, Parts.* FROM RobotsParts INNER JOIN Robots INNER JOIN Parts';

    return $modelsManager->executeQuery($phql);
}

this would return resultset of Robots, RobotsPartsand Parts


$records = RobotsParts::getAll();

foreach ($records as $key => $record) {
    $robot = $record->Robots;
    $robots_parts = $record->RobotsParts;
    $parts = $record->Parts;
}

if you select some columns instead of Robots.* for example Robots.name


$records = RobotsParts::getAll();

foreach ($records as $key => $record) {
    $robotName = $record->name;
    $robotsParts = $record->RobotsParts;
    $parts = $record->Parts;
}

Wajdi Jurry · Answer 10 · 2019-05-09T11:24:04-07:00

I agree to use this method in querying data, but how to handle pagination in this case ? If you limit the query by 3, it would get the first three results only and it's wrong, since the first record in the reference table may have 5 records in the related table. Did you get my concern ?

for me i do both cache and inner joins

for example:

let say this is our tables:

CREATE TABLE robots (
   id int(10) unsigned NOT NULL AUTO_INCREMENT,
   name varchar(70) NOT NULL,
   type varchar(32) NOT NULL,
   year int(11) NOT NULL,
   PRIMARY KEY (id)
);

CREATE TABLE robots_parts (
   id int(10) unsigned NOT NULL AUTO_INCREMENT,
   robots_id int(10) NOT NULL,
   parts_id int(10) NOT NULL,
   created_at DATE NOT NULL,
   PRIMARY KEY (id),
   KEY robots_id (robots_id),
   KEY parts_id (parts_id)
);

CREATE TABLE parts (
   id int(10) unsigned NOT NULL AUTO_INCREMENT,
   name varchar(70) NOT NULL,
   PRIMARY KEY (id)
);

now if i want to query robots_parts

RobotsParts::find();

this would return resultset of RobotsParts

if you want for example Robots, RobotsPartsand Parts in a single query you have to use inner joins whether using phql or query builder

this is a static method in RobotsParts

public static function getAll()
{
   $di = Phalcon\Di::getDefault();

   $modelsManager = $di->getModelsManager();

   // assuming you made all the relationships in all models involved

  // otherwise you have to specify primary keys in inner joins (  INNER JOIN Robots on RobotsParts.robots_id = Robots.id )

   $phql = 'SELECT RobotsParts.*, Robots.*, Parts.* FROM RobotsParts INNER JOIN Robots INNER JOIN Parts';

   return $modelsManager->executeQuery($phql);
}

this would return resultset of Robots, RobotsPartsand Parts


$records = RobotsParts::getAll();

foreach ($records as $key => $record) {
   $robot = $record->Robots;
   $robots_parts = $record->RobotsParts;
   $parts = $record->Parts;
}

if you select some columns instead of Robots.* for example Robots.name


$records = RobotsParts::getAll();

foreach ($records as $key => $record) {
   $robotName = $record->name;
   $robotsParts = $record->RobotsParts;
   $parts = $record->Parts;
}

talal424 · Answer 11 · 2019-05-09T15:13:29-07:00

talal424
8.4k

May '19

even if you limit the results would be the same.

afsdefdse · Answer 12 · 2020-10-09T03:55:47-07:00

If you've got don't have an index on call, depening for your database, it could be a piece bit slower. But please start with some measurements. For instance the usage of microtime get here guide https://forum.phalcon.io/discussion/19917/phalcon-task-is-very-slow-for-migrating-old-tables-to-new-tables