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

Several joins for the same model

Hi there!

I had similar problem in the past https://forum.phalcon.io/discussion/600/phql-self-join-using-query-builder but didn't finally solved my problem.

Now it seems like the problem shows up again.

Well, here is the task: for instance model App\Models\System\Organization has more then 1 relationships with another model App\Models\System\Address by ID

If i fetch rows by means of PHQL\QueryBuilder this way:

$companies = $manager->createBuilder()
            ->columns('Organization.*, RegisteredAddress.*')
            ->from(array('Organization' => 'App\Models\System\Organization'))
            ->leftJoin('App\Models\System\Address', 'RegisteredAddress.id = Organization.registeredAddress', 'RegisteredAddress')
            ->where('Organization.id = :id:', array ('id' => $this->orgId))
            ->getQuery()
            ->execute();

everything is fine and i can access values by $companies->getFirst()->RegisteredAddress

But everything has changed when i added one more join for the same model by another column:

  $companies = $manager->createBuilder()
            ->columns('Organization.*, RegisteredAddress.*, PhysicalAddress.*')
            ->from(array('Organization' => 'App\Models\System\Organization'))
            ->leftJoin('App\Models\System\Address', 'RegisteredAddress.id = Organization.registeredAddress', 'RegisteredAddress')
             ->leftJoin('App\Models\System\Address', 'PhysicalAddress.id = Organization.physicalAddress', 'PhysicalAddress')
            ->where('Organization.id = :id:', array ('id' => $this->orgId))
            ->getQuery()
            ->execute();

Now if i try to look at $companies->getFirst()->RegisteredAddress i get the error

Notice: Undefined property: Phalcon\Mvc\Model\Row::$RegisteredAddress

but now it's possible to access to $companies->getFirst()->PhysicalAddress

I also tried to join the same model for one of the columns one more time (so i get 3-times joined model Address) and only last joined model (RegisteredAddress1) can be accessed.

        $companies = $manager->createBuilder()
            ->columns('Organization.*, RegisteredAddress.*, PhysicalAddress.*, RegisteredAddress1.*')
            ->from(array('Organization' => 'App\Models\System\Organization'))
            ->leftJoin('App\Models\System\Address', 'RegisteredAddress.id = Organization.registeredAddress', 'RegisteredAddress')
            ->leftJoin('App\Models\System\Address', 'PhysicalAddress.id = Organization.physicalAddress', 'PhysicalAddress')
            ->leftJoin('App\Models\System\Address', 'RegisteredAddress1.id = Organization.registeredAddress', 'RegisteredAddress1')
            ->where('Organization.id = :id:', array ('id' => $this->orgId))
            ->getQuery()
            ->execute();

Is it a bug or am i doing something wrong? Thank you.



98.9k

Try: $companies->getFirst()->registeredAddress instead of $companies->getFirst()->RegisteredAddress



2.3k

Nope, it doesn't work. I've monitored raw-SQL, it produces query only for the last joined model.