Practical PHP Patterns: Query Object

An ORM provides an abstraction of storage as an in-memory object graph, but it is difficult to navigate that graph via object pointers without loading a large part of it. Typical problems of this approach are the performance issues related to loading of the various objects, and the transfer of business logic execution from the database side to the client code side, with the resulting duplication.

Anyway, when we start navigating an object graph we have to obtain a reference to an entity somehow (an Aggregate Root), from which we can navigate to the other ones. ORMs and, in general, Data Mappers provide different ways to select a subset of objects (or a single one) and reconstitute only that subset from the data storage.

• Custom mapper classes with domain-specific methods are the the simplest solution, which is often recommended when not using a generic Data Mapper.
• Custom mapper classes with finder methods are an half-baked solution, which mixes up domain-specific mappers with general purpose methods, sometimes needed to allow flexibility on the user side.
• Generic mapper classes with finder methods can be provided as a way to parametrize fields, resulting in methods like findBy($entityName, $field, $value).
• Generic mapper classes with query objects are employed when there is the necessity of composing queries and pass them around for further elaboration or refining. Promoting the query as an object helps this use case.

Note that once a mapper implements query objects, they can be effectively used in finder methods, which are a subset of the functionality provided by query objects.

In fact, query objects are the most versatile way to ask for the objects that satisfy certain conditions, and they are an Interpreter implementation over a query language adapt for an object model.

All of us already know a query language: SQL. But SQL is pertinent to relational databases, while an ORM strives for keeping the illusion of an object-only model into existence. As a result, it must adopt a different language which describes object features, like HQL (Hibernate) or DQL (the Doctrine equivalent).

Object query languages

There are several differences between an object query language and SQL in the entities you can refer to within queries:

• SQL refers to tables; object query languages refer to classes and some tables like the association tables for many-to-many relationships simply vanish.
• SQL refers to rows; object query languages to objects.
• SQL refers to other tables for making JOINs; object query languages to object collaborators.
• SQL refers to columns, which also include foreign keys; object query languages only to fields of the objects.

When a full-featured language is involved, there must be a component of the ORM that parses the strings containing language statements into a Query Object. Another way to define such an object (Interpreter) is constructing it by hand, by calling a series of setter methods or by implementing a Builder pattern.

Advantages

• A Query Object hides the relational model (the schema) from the user, as it can be inferred by the union of the queries and the Metadata Mapping anyway. The information contained in the metadata, like foreign keys and additional tables, do not have to be repeated in the various components of client code.
• It hides also the peculiarities of the particular database vendor, since the generation of SQL can be addressed by a driver.
• It promotes queries as first-class citizens, making them objects that can be passed around, cloned or modified.

The database abstraction layers like PDO make of statement objects (PDOStatement) one of their first modelling points.

Disadvantages

The implementation of the parser for a query language is a task of great complexity, which makes this pattern only feasible in generic Data Mappers. Even when using only Query Objects made by hand, it is advisable to employ an external Data Mapper to take advantage of the translation of object-based queries to SQL.

Examples

Doctrine 2 contains a parser for its Doctrine Query Language, which lets you define queries like you would do with PDO, but still referring to an object model. The documentation of the query language itself is pretty complete, so I won't go into details but I'll give you a feel of how using DQL is like. The language itself is compatible with the Doctrine 1 version, if you happen to have used it.

<?php
$query = $em->createQuery('SELECT u FROM MyProject\Model\User u WHERE u.age > 20');
$users = $query->getResult();

$query = $em->createQuery("SELECT u, a FROM User u JOIN u.address a WHERE a.city = 'Berlin'");
$users = $query->getResult();

uery = $em->createQuery('SELECT u, p FROM CmsUser u JOIN u.phonenumbers p');
$users = $query->getResult(); // array of CmsUser objects with the phonenumbers association loaded
$phonenumbers = $users[0]->getPhonenumbers();

$query = $em->createQuery('SELECT u, a, p, c FROM CmsUser u JOIN u.articles a JOIN u.phonenumbers p JOIN a.comments c');
$users = $query->getResult();

Sometimes there are no fixed queries, but a dynamic query has to be constructed from its various parts, as a union of conditions, joins and sorting parameters; not all the parameters may be available at a certain time and concatenating strings to compose a DQL statement is prone to error. Doctrine 2 includes a Query Builder which has methods you can call orthogonally, in any order and combination.

<?php

// $qb instanceof QueryBuilder

// example6: how to define: "SELECT u FROM User u WHERE u.id = ? ORDER BY u.name ASC" using QueryBuilder string support
$qb->add('select', 'u')
   ->add('from', 'User u')
   ->add('where', 'u.id = :identifier')
   ->add('orderBy', 'u.name ASC');
   ->setParameter('identifier', 100); // Sets :identifier to 100, and thus we will fetch a user with u.id = 100