How to make a query using Between with Doctrine and ORM

Question

Work with Docrtine 2 and Zend Framework 2 I have a service that work like this:

class TransactionService 
{
    public function __construct(TransactionMapperInterface $transactionMapper)
    {
        $this->transactionMapper = $transactionMapper;
    }

    public function findBy($conditions = array(), $order = array(), $limit = null, $offset = null)
    {
        return $this->transactionMapper->findBy($conditions, $order, $limit, $offset);
    }
}

But I need to query with BETWEEN condition like this result:

select * from transactions where created between '2014-02-01' and '2014-03-01';

This is possible with findBy method?

Answer 1

I found another simpler way to do this. Ok, with findBy this is not possible, but we can use createQueryBuilder, see a litte example:

$qb = $this->transactionMapper->createQueryBuilder('transaction');

$query = $qb->select('t')
            ->from('Transaction\Entity\Transaction', 't')
            ->where($qb->expr()->between('t.created', "'2014-02-01'", "'2014-03-01'"));

return $query->getQuery()->getResult();

See documentation The QueryBuilder

Answer 2

In short, no. findBy is only used for simple querying, and your BETWEEN is not part of that.

However it can be done relatively simple: I would use a Doctrine\ORM\Query\Filter\SQLFilter to do this kind of query, which is a new feature in since Doctrine 2.2.

The way this works is that create a class that extends from the base SQLFilter class, and add your own filter constraints to them. When the filter is enabled, any query after that that is based on that specific entity will get this filter applied.

For your specific problem, you could do something like the following (assuming that the transactions table is actually an entity called ORM\Entity\Transactions.

<?php

namespace ORM\Filter;

use Doctrine\ORM\Mapping\ClassMetaData;
use Doctrine\ORM\Query\Filter\SQLFilter;

/**
 * This filter filters on records between two given dates.
 */
class BetweenFilter extends SQLFilter
{
    public function addFilterConstraint(ClassMetaData $targetEntity, $targetTableAlias)
    {
        // First check if the entity implements the BeteenAware interface
        if (!$targetEntity->reflClass->implementsInterface('ORM\Filter\BetweenAware')) {
            return "";
        }

        // We know now that our table supports the between entity, please note
        // that getParameter automatically quotes variables
        return sprintf(
            '(created BETWEEN %s AND %s)',
            $this->getParameter('betweenFrom'),
            $this->getParameter('betweenTo')
        );
    }
}

Now what we want to do is create a ORM\Filter\BetweenAware interface, that our ORM\Entity\Transactions needs to implement so that Doctrine knows that is able to filter on these fields. The interface is only for identification purposes only, so it is very short.

<?php

namespace ORM\Filter;

/**
 * BetweenAware defines that an Entity is able to filter on the
 * 'created' field.
 */
interface BetweenAware {}

All that is left at this point, is add the filter, set the parameters and enable the filtering.

// We'll ASSUME that the $config variable is a valid Doctrine configuration object retrieved 
// by instances such as `Setup::createAnnotationMetadataConfiguration`. On the other hand
// the variable $connection is assumed to contain valid connection details.

// First add the filter to the configuration
$config->addFilter('between', 'ORM\Filter\BetweenFilter');

// Now create the EntityManager
$entityManager = EntityManager::create($connection, $config);

// We will now enable our filter and set the parameters
$entityManager
->getFilters()
->enable('between')
->setParameter('betweenFrom', '2014-02-01')
->setParameter('betweenTo', '2014-03-01')
;

// Now for the moment supreme, lets get all the records back
$records = $entityManager
->getRepository('ORM\Entity\Transactions')
->findAll()
;

I have not tested the code, so I am not sure if it is without faults, but this is definitely a good way of filtering on records without all the mess.

Hopefully this will answer your question. I don't mind down voting, but please let me know why so we can all learn.

DO NOT FORGET to add the ORM\Filter\BetweenAware interface to the Entity class, otherwise the filter will be ignored.