Advantages of ORM

PHP
10

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.