Using custom functions in Symfony & Doctrine
This is a follow-up article for “Using custom types in Symfony & Doctrine” published in 4xxi blog.
There we created a custom DateRange type for Doctrine, registered it in Symfony and wrote some sample code.
However, that is not the end of the story. The true power of PostgreSQL’s range types lies in range operators. These operators enable us to shift a lot of work to PostgreSQL: overlaps, unions, differences etc.
Unfortunately, we can’t use all this functionality in Doctrine… or can we?
Prerequisites
Some Symfony/Doctrine/PostgreSQL knowlegde.
Check out previous article on daterange type and description of range operators.
Understanding of what Abstract Syntax Tree is would be helpful, but it is not required.
Implementing node class
As an example we are going to implement “overlaps” operator for ranges.
It works like this:
Now, we are going to represent it in DQL using a user-defined function.
The class extends the Doctrine’s FunctionNode class and represents a node from the AST, i. e. it is an abstract model of our function in DQL.
The first method, parse, describes how to parse an expression. For this function we use match and StringPrimary calls, but there are other options.
match is basically a step for the Doctrine parser. First, we match identifier, which is overlaps in our case and is equal to a name of a function. Then, step by step we match other syntax elements — parenthesis, comma, parenthesis.
Other call we use is StringPrimary that matches a string-like expression and returns the result of parsing (another AST node). We use it to parse arguments of a call, two dateranges.
The second method, getSQL, translates the model of the function call (AST node) into its SQL form. There is no need to manually convert the arguments, Doctrine will take care of it.
Symfony Integration
Again, just add your function to the configuration.
How to use
Since we have already created entity that uses DateRange type, I am going to use it as an example.
Results:
All the code can be found here.
So, apart from special data types, many vendors provide useful sets of functions and operators. In some cases, you may be tempted to create your own — though this is not what I would usually recommend.
To take advantage of built-in functions of your RDMBS that are not covered by the Doctrine you can define your own functions and even share it.
Please note that some of them are already available on Github:
