Imagine you have a game entity with plenty of many-to-one relations as well as many-to-many relations with teams and officials. You want to hydrate a game object graph with everything nicely linked.
Here is an example of building such a query using Doctrine 2ās ORM.
Not saying itās a good example but it is real and it works.
public function createQueryBuilderForGames()
{
$qb = $this->createQueryBuilder('game');
$qb->addSelect('game, game_location, game_age_group, game_region');
$qb->addSelect('project, project_official_positions');
$qb->addSelect('project_game_teams,project_game_team, project_game_team_region');
$qb->addSelect('project_game_officials,project_game_official,project_game_official_region');
$qb->leftJoin('game.project', 'project');
$qb->leftJoin('project.offpositions','project_official_positions');
$qb->leftJoin('game.region', 'game_region');
$qb->leftJoin('game.location','game_location');
$qb->leftJoin('game.agegroup','game_age_group');
$qb->leftJoin('game.projectGameTeams', 'project_game_teams');
$qb->leftJoin('project_game_teams.projectTeam','project_game_team');
$qb->leftJoin('project_game_team.region', 'project_game_team_region');
$qb->leftJoin('game.projectGameOfficials', 'project_game_officials');
$qb->leftJoin('project_game_officials.projectOfficial','project_game_official');
$qb->leftJoin('project_game_official.region', 'project_game_official_region');
return $qb;
}
You can use the above query to load a single game:
public function find($id)
{
if (!$id) return null;
$qb = $this->createQueryBuilderForGames();
$qb->where('game.id = :id');
$qb->setParameter('id',$id);
return $qb->getQuery()->getSingleResult();
}
Or to load a bunch of games using a criteria object:
public function findGamesByCriteria(array $criteria)
{
$gameIds = $this->findGameIdsByCriteria($criteria);
if (count($gameIds) < 1) return [];
$qb = $this->createQueryBuilderForGames();
$qb->where('game.id IN(:game_ids)');
$qb->setParameter('game_ids',$gameIds);
$qb->addOrderBy('game.date','ASC');
$qb->addOrderBy('game.time','ASC');
$qb->addOrderBy('game.id', 'ASC');
return $qb->getQuery()->getResult();
}
Yes you do have to map your objects to database tables.
And sometimes the ORM loads more than you really need.
But using one can save quite a bit of development time.