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 http://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



5.1k
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();


755

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.

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.