We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Is possible to join data to model by query builder?

I have query builder:

        $qb = $this->modelsManager->createBuilder();
        $qb->addFrom('App\Model\User', 'user');
        $qb->columns(['user.*']);

And it works, but I have to avoid lazy loading problem, when I have:

foreach($user->organizations as $organization){
    echo $organization->name();
}

It produces about 0,5k db queries when users list is rendering I try to use join:

        $qb = $this->modelsManager->createBuilder();
        $qb->addFrom('App\Model\User', 'user');
        $qb->columns(['user.*']);
        $qb->join('App\Model\Organization');

But query is:

FROM `user` AS `user`
INNER JOIN `organization` ON 1

And of course no effect.

Is it possible to get model without many additional queries?

If you have proper references set up, the ORM should automatically create the join condition.

But you can do it manually too:

  $qb = $this->modelsManager->createBuilder();
        $qb->addFrom('App\Model\User', 'user');
        $qb->columns(['user.*']);
        $qb->join('App\Model\Organization', 'org.id=user.organization_id', 'org');


8.3k
  1. "ORM" doesn't create a join because it's fixed on lazy loading
  2. When I add conditions manually:
        $qb->join('App\Model\UserHasOrganization', 'userHasOrganization.user_id = user.id', 'userHasOrganization');
        $qb->join('App\Model\Organization', 'organization.id = uHO.organization_id', 'organization');

    It's worse, because I have duplicated users on the list and still organization is fetched from DB each time when I call it in tpl:

    {{ organization.name }}

Make the relation re-usable:

$this->hasOne("product_id", "ProductStatus", "product_id", array('alias'=>'status', 'reusable' => true));

Also, use inner join if you dont want to have duplicate users.



8.3k
edited Feb '18
  1. Reusable doesn't work at all in my case and it works partly with many-to-one relation (doesn't create a join, but creates unique select queries - what is better but still not perfect)
  2. innerJoin/join are the same, I need many organizations connected with one user.
  1. Reusable doesn't work at all in my case and it works partly with many-to-one relation (doesn't create a join, but creates unique select queries - what is better but still not perfect)

Exactly. So it wont fetch the same organization twice in a single request, or between requests if cached properly.

  1. innerJoin/join are the same, I need many organizations connected with one user.

True, it's an old and bad habit of mine from old MSSQL days. Then dont join, use relations.

Frameworks will never cover all your use-cases and also be performant. If you really want performance, use raw queries.



8.3k

Frameworks will never cover all your use-cases and also be performant

I don't need "all" but only absolutely basics, what was achieved in other frameworks (like Hibernate or Doctrine) ages ago. In Doctrine:

        $qb = $this->createQueryBuilder('a');
        $qb->select('a', 'b', 'c', 'd', 'e');
        $qb->join('a.createdBy', 'b');
        $qb->leftJoin('a.competitionDates', 'c');
        $qb->leftJoin('c.competitionTimes', 'd');
        $qb->leftJoin('d.competitionMembers', 'e');

One custom query, few minutes, list of entities with all relations/collections. 99% percent of queries have relations, sometimes dozen of more - it means, that I have to use raw queries everywhere, what makes Phalcon ORM useless. What is a point to sacrifice time for describing all relations/tables and add performance layer when finally I use raw query? I can't see how this "ORM" would be helpful, neither creating tables, nor creating queries, nor relations.

I don't see how doctrine's performance will be different, it's not magic you know. It will still do heavy-lifting in the background, which degrades performance. Íf anything, the same setup will be faster with phalcon.



8.3k

I'm really fed up with all that "performance". So why do people use frameworks instead of pure PHP? Pure PHP is obviously much more faster! They use frameworks because they know, that doing their own implementation mean:

  • much more time is wasted
  • probably much worse effects are achieved (included performance) So what Phalcon is fast, when to achieve the same like for example in Symfony I have to add tone of code and workarounds and my final result is worse than in Symfony? That's a reason why people chose Doctrine instead obsolete active record.

BTW, I've solved my problem by creating "organizations" property with set/get and I set it manually, I have two queries but select +join is almost the same what two selects and much more better then hundreds. I'm also going to get rid off relation definition from models.

Well, you were complaining about loss of performance due to the ORM's functionality. Which, as yourself pointed out, is there to ease the development process.

Doctrine, or any other ORM has performance drawbacks compared to raw SQLs; that's the nature of any framework.

What you want can be acheived by vanila phalcon, but you didnt like it, because of the performance :P There are methods to mitigate that with phalcon (caching, reusable relations, etc), you just have to use them...

There are basic ways to write a query that will not return multiple rows, innerjoins vs left joins etc in Querybuilder.

However with Phalcon I prefer to use the PHQL to write out joins so I get a single query -- not multiple hasmany hits-- and still get access to the model definitions: The below example, calling the $row->c vs the $row->b models make me happy, using getters/setters I can control quite a bit of data properly.

<?php

$phql = "SELECT c.*, b.* FROM Cars c, Brands b WHERE b.id = c.brands_id";

$rows = $manager->executeQuery($phql);

foreach ($rows as $row) {
    echo "Car: ", $row->c->name, "\n";
    echo "Brand: ", $row->b->name, "\n";
}

https://olddocs.phalcon.io/en/3.0.0/reference/phql.html