Doctrine Inet_Aton

Imagine we have a table in our database (and a Doctrine model), like the below, that holds a list of IP address ranges and their associated countries. We want to find the country that corresponds to the IP address 1.0.0.12

In order to do this we want to effectively search for a row where our chosen IP is between the IP Start and IP End figures. Because these values are not numeric it's not as straight forward as simply performing a greater than or less than query. We can however use the INET_ATON functionality built into MySQL to return integers that represents our IP addresses. Our example IP of 1.0.0.12 would be represented as 16777228 (1×256³ + 0×256² + 0×256 + 12)

Doctrine doesn't support INET_ATON out of the box due to only supporting SQL functions that are common across all vendors. The Inet Aton function is specific to MySQL so we have to register a custom DQL extension to allow this functionality. First we create our class which tells Doctrine how we're going to use this function.

# src\AppBundle\Extension\Doctrine\InetAton.php

namespace AppBundle\Extension\Doctrine;

use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;

class InetAton extends FunctionNode
{

    public $valueExpression = null;

    /**
     * parse
     *
     * @param \Doctrine\ORM\Query\Parser $parser
     * @access public
     * @return void
     */
    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->valueExpression = $parser->StringPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    /**
     * getSql
     *
     * @param \Doctrine\ORM\Query\SqlWalker $sqlWalker
     * @access public
     * @return string
     */
    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'INET_ATON('. $this->valueExpression->dispatch($sqlWalker) . ')';
    }
} 

Next we need to instruct Symfony and Doctrine to load our custom functionality by adding the following to our config.yml file

# app/config/config.yml
doctrine:
    orm:
        dql:
            string_functions:
                INET_ATON: AppBundle\Extension\Doctrine\InetAton

We can now use our INET_ATON function directly in our Doctrine queries like:

$qb = $this->getEntityManager()
    ->createQuery("SELECT i.country FROM AppBundle:IpCountries i WHERE INET_ATON(:ip) >= INET_ATON(i.ipStart) AND INET_ATON(:ip) <= INET_ATON(i.ipEnd)")
    ->setParameter('ip', '10.0.0.12');