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

Fetch related joins in a Model

It would be nice to use PHQL to get a model and a related models pre-hydrated in the model (like in Doctrine Fetch Joins https://docs.doctrine-project.org/en/2.1/reference/dql-doctrine-query-language.html#joins)

At the moment with PHQL it is possible to do

SELECT user.*, cars.* FROM UserModel user JOIN CarModel cars ON user.id = cars.userId WHERE user.id = 1

This would return a Complex ResultSet with Rows containing UserModel and CarModel. It would be better to return only a UserModel and access the cars like user->cars



7.7k
edited Mar '14

If you have your models setup correctly (using belongsTo and hasMany) you can just do:

$user = User::findFirstById(5);
$cars = $user->getCars();


1.1k

Yes, but that is two queries. One to get the user and another one the get the cars. Sometimes you would like to get the user with the cars with one query and hydrate the cars in the user.



60

I have the same question. If jbaez have to get 10 $users, 10 additional queries will be issued to fetch each $cars result.



60

You can use the query builder to achieve this. Ensure the belongsTo and hasMany are set properly in the models.

It would look like this:

$user = new User();
$builder = $user->getModelsManager()->createBuilder()
                    ->columns('UserModel.*', 'CarModel.*')
                    ->from('UserModel')
                    ->join('CarModel', 'UserModel.id = CarModel.userId')
                    ->where('user.id = :user_id:',
                        'bind' => array('user_id' => 1)
                    );

$result = $builder->getQuery()->execute();

The $result is a ResultSet and will contain both models together, all in one query. This should work for you.