Phalcon Performance Related Queries

Phalcon performance related queries

To access ->participants the same way using QueryBuilder, you will have to build join into Query.

Code example could be something like:

$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 is used here for making result possibly multi-dimensional.

That kind of query (tested under PgSQL) made Phalcon create some subsequent ResultSet objects of participants pi inside Resultsets for projects p.

You still can iterate through it by using foreach() but after all, I'am not sure it did reduce final query count.

Fireing $result = $resultSet->toArray() made $result['pi'] remain as Resultset, so u should stay cautious on that. You may force it to dump as arrays by defining exact columns in columns() parameters. It has its downside - you will no longer profit from groupBy(), at least on Phalcon 1.3.2 and PHP 5.5.3 im running here.

Phalcon | One query insted of two for belongsTo

$queryBuilder = $this->getDI()->getModelsManager()
    ->createBuilder()
    ->columns(['r.id','r.name', 'r.type_id', 'rt.type'])
    ->addFrom('Robot', 'r')
    ->leftJoin('RobotTypes', 'rt.id = r.type_id', 'rt');

$resultSet = $queryBuilder->getQuery()->execute();//->toArray(); //optional

If you call by name all columns you need, you should be able to retrieve full result w/o multiquerying DB for types separately. It's still not a PHQL, it also does not require declarations of belongsTo() to work properly.

As far as i know, you're not able to fetch joinable things only using models - It's because implementation would be too complex in case of multirelational tables, and thats for what queryBuilder is designed.

PhalconPHP: eager load related records?

Yes and No. Yes: you can use a subquery to select all company title ids into a separate field and manually digest it into array. Actually no… according to this.

No: you can't do that if you want to keep your one to many relations and be able to do $companies[0]->companyTitles without loading all of that data. That would be barely possible and very hacky in plain sql.

Any ORM is an attempt to abstract away from dealing directly with sql, which will always cost you functionality (can't account for everything without reinventing the wheel) and performance (query compilation, non-optimal queries, multiple queries, etc) vs using plain sql. Phalcon is an amazing project, but like other frameworks with ORM support or ORM frameworks, it's not going to outsmart you in situations like this. I've stopped using the native Phalcon models after hitting too many dead ends, they are awesome for simple tasks, but for something more complex they run into problems.

If your priority is the time, then stick with what you have. Extra 10-50 ms for an extra query won't change much. If you need a maintainable codebase, this is a big and long running project, then this is a risky way forward. When things get more complicated this gets truly unreliable. Use an more mature ORM, I'm personally having a great success with illuminate/database for building queries and manually mapping data to models – I have to say though this was pretty time consuming.

Phalcon bulk insert performance. Models or Raw SQL

Phalcon\Mvc\Model is slow because it creates a PHQL statement for every INSERT, then parses it and translates into the SQL dialect supported by the current DBMS. PHQL parsing is what makes this slow.

If you need performance, you will have to generate the INSERT statement yourself.

Phalcon - search with joins

You should use queryBuilder for best performance. Anyway if you alias your relation and stick to objects rather than arrays youre able to make some tricks like:

// in users model
$this->belongsTo(
    'id',   // local id
    'Posts', // model relation
    'user_id', [ // related id
        'alias' => 'posts' // alias for related records
    ]
);

$user = Users::findFirst(); // first user from table, just for example

// foreach($user->posts as $post) // using exact alias name
foreach($user->getPosts() as $post) { // using getter for alias
    var_dump($post);
}

Thing is, for more users in such case, like when foreaching over users and than foreaching over posts, engine is sending separate query what may stress server quite alot, depending on situation. Here you have a bit more in topic.

Phalcon Model find() with condition in another table

You have possible two approaches:

1. Via queryBuilder

Joins directly from models are hard to achieve. It's why queryBuilder was designed - models are querying for data only if you are willing to get them, eg. by accessing supposedly-joined $books->getStatus(). For more information search documentations: hudge example. There was also an useful topic about optimisations here on SO, so you will know why not always direct usage of models is good idea.

2. Via creating a separate model

To make it easy, clear but maybe not most performance-wise (depends on usage), since not long ago (Phalcon 1.3.2?) you can create a separate model with additional conditions in it (not tested example):

class LostBooks extends Books {

    public function initialize() {
       $this -> belongsTo("status_id", "Status", "id",
       [
            'alias' => 'status',
            'params' => [
                'description' => 'lost'
            ]
       ]);
    }
}

With that properly declarated you can get those simple by

$lostBooks = LostBooks::find();

PS: use array() instead [] of, if you are previous to PHP 5.4, got some habits.

---

Related Topics