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 ?
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.
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.
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.
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 = trueoption 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.
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)?
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.
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 productThat is if you use
findandgetRelated.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)?
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.
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;
}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_partsRobotsParts::find();this would return resultset of
RobotsPartsif you want for example
Robots,RobotsPartsandPartsin a single query you have to use inner joins whether using phql or query builderthis is a static method in
RobotsPartspublic 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,RobotsPartsandParts$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 exampleRobots.name$records = RobotsParts::getAll(); foreach ($records as $key => $record) { $robotName = $record->name; $robotsParts = $record->RobotsParts; $parts = $record->Parts; }
even if you limit the results would be the same.
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
