Doctrine, Complex SQL Queries, and Paginators

In my efforts to build web applications using domain model programming I’ve encountered some interesting technical hurdles for keeping my implementation details away from my models. The most problematic issue that has come up time and time again has been that of using paginators. It’s very difficult to keep your paginator class in the dark about database implementation details and have it be able to handle complex use cases. Over the last few months I have slowly evolved a sophisticated way to describe a complex sql query using nothing more than an associative array which can be passed to a clueless paginator which talks to the database through a predefined interface to a model gateway. Yeah the mad scientist is me because that’s how I felt when I looked back on the pure genius of this method.

Some time ago I wrote an article on how to implement domain model programming with the Zend Framework while using Doctrine for your Object Relational Mapper (ORM),
you read it here:

I’ve refined my methods quite a bit since then, most notably I encapsulate most of the raw ORM-specific code in an application specific gateway class which all other model gateways extend. Most CRUD operations work pretty much ad-naseum with only the model classes changing between functions so it seemed natural in the interest of the DRY methodology to merge these into one class and use placeholders in place of the database model names.

The base class defines 3 member variables. One for the Doctrine model class name, one for the data model class name and one for the data model collection class (Definitely read my domain model programming article if you don’t know what these are). The base class defines these as null because it is up to the extending class to define them. The base class would provide all basic CRUD operations, allowing a generic ‘criteria’ variable to be passed to the different methods which I will explain soon. Here are some example methods this class would provide:

This gives you an idea of CRUD operations can be conducted without knowing the models involved ahead of time. For most use cases, these methods are adequate for gateways that extends this class and for those that have special needs, they can simply include a wrapper method for modifying the criteria that gets fed to the base class. I have snipped count() and fetch() because they are the thesis for my article so bear with me just a little longer.

The utility of all this becomes evident when defining many different model gateway classes. I write one gateway for each data model, each tasked with knowing all of the intimate details about the model it is responsible for. Here is an example:

That’s it. The base class takes care of all the heavy lifting and we are free to use this class for particular use cases and implementation specifics.

So moving on, more often than not, we will want to get a paginator out of the model gateway when outputting to a browser page. Since we have a method called fetch() which is consistent across all model gateways we can use an interface and build a custom Zend_Paginator_Adapter to construct a paginator object that can talk to these gateways. This is what our paginator adapter would look like:

When constructing one of these adapters, we need to store the criteria, the model gateway object and the order the results need to be fetched in. The base paginator class will handle limit and offset automatically so we just need to tell it how to fetch the data. Here is where our magic ‘criteria’ array comes into focus. Remember, that our paginator has no concept of an ORM nor should it have a care in the world about our particular database server implementation. This makes it tricky to handle query criteria as eventually it has to be converted into a format that the ORM implementation will understand. My idea was to use a format for criteria that had no external dependencies and was general enough to be passed through multiple layers of implementation down to the ORM. I opted for a plain old associative array where the keys are the fields I am interested in and the values are what I am comparing against. Obviously I need to some additionaly syntactical sugar in order to describe different scenarios where I might want to use LIKE instead of =, or where I might want to do a WHERE x IN (a, b, c, … n). Needless to say, there were plenty of scenarios I did not take into account when designing this algorithm initially so it was a painful evolution.

I ended up with a syntax for defining types of comparisons and for defining whether to negate a comparison or trigger certain database operators without using any database specific verbage at all. Let me now tease you with the contents of fetch() and count(), the two gateway methods our paginator adapter relies on:

This is full of all sorts of juicy Doctrine specific objects and method calls which construct a Doctrine query based on the parameters that get passed in. The final piece of this is the buildQuery() method which is where the magic happens.

This method is used by both count() and fetch() and accepts our fully loaded ‘criteria’ array and the pre-constructed Doctrine query. It’s pretty intense so I will break it up into chunks and explain.

We need to loop through all of the information in $pCriteria so we first set up an array for remembering which external models have already been joined and also we need to know when we are on the first run through the loop in order to properly set up the boolean conditions. For each criteria we run the array key through a number of regular expressions in order to detect our syntactic triggers.

The first thing we check for is a preceeding ‘!’ which tells us that the comparison will be negated. This means instead of comparing with ‘=’ we compare with ‘!=’. Next we need to know if this is the first item in the list, and if it is then we are going to use a plain old ‘where’ clause, otherwise we will need to do ‘andWhere’ or ‘orWhere’. We check to see if the key starts with ‘||’ which denotes ‘orWhere’ instead of ‘andWhere’ which we would assume by default.

All the conditional blocks that follow are for determining the operator we will use and appending the whole condition to the query.

RAW SQL

First we need to account for when we want to use a raw SQL where string. Frankly, this is the only way to group conditions together with parentheses so we can define some kind of order of operations. For example if we want to define (col1 = X and col2 = Y) OR (col3 = Z) we would need to use this syntax for defining the first set of conditions. As long as the key begins with ’sql:’ then this operation will be triggered. If you need more than one, you can suffix them with numbers to keep the associative array keys unique (ie, sql:1, sql:2, etc).

would translate to:

LIKE/NOT LIKE

Here we assume that any key wrapped with percent ‘%’ will trigger a LIKE or NOT LIKE operation.

would translate to

Also here is the first time we check the key for a ‘.’ which indicates we want to join with another table/model. Obviously the Doctrine model must have a relationship with another table defined in order for this to work:

With this use case we can do something like this:

would translate to

IN

Here we check to see if the key is wrapped in parentheses ‘()’. This indicates that we will be checking if that column is in a list of values. The value for the key should always be an array. We take into account join requests here as well.

will translate to

COMPARISON/IS NULL/IS NOT NULL

The remaining cases handle standard comparisons (=/!=) or IS NULL/IS NOT NULL. If the value for the key is literally NULL, than we make sure to trigger IS NULL or IS NOT NULL. Again, we accommodate requests for joins.

So now that we have this bizarre set of incantations to translate our desires into Doctrine queries we can make some pretty interesting combinations of criteria in order to build complex queries which can be easily paginated.

magically becomes

Neat huh? Now we apply all this using our nifty paginator adapter.

Now we have a perfectly usable paginator that returns exactly what we want in the order we want, all without knowing any of the implementation details of the database. What we have here is a nice tidy separation of data and logic. Enjoy.