CODEWITHSUNDEEP
datesearchdoctrine-ormsymfony-2.3
Felix Aballi
Felix Aballi

Reputation: 949

Symfony 2.4.1 and Doctrine 2 dates interval query

I have two tables: Empleado and Fichaje in a (1..*) relationship.
I created a query builder for getting fichajes corresponding to IdEmpleado (key) property in Fichaje.
I attempt to filter those Fichajes, but it never works.
So I've searched for any clear example of dates in Doctrine for this basic case in vane.
The query result is empty always. No error is thrown.
If I check for IdEmpleado parameter only it gives me all the available records. The dates interval is the problematic one.

Note: I checked this similar post

Here is the table data, I'm quite convinced of the dates availability.

query and table results

This is my function:

public function empleadoAction(Request $request){

     ...
        $repository = $em->getRepository('ZkTimeBundle:Fichaje');
        $fichajes = $repository->FindByEmpleadoAndDateInterval(
            array(
                'IdEmpleado' => $workerId,
                'FechaInicial' => (new \DateTime('2014-01-10'))->format('Y-m-d'),
                'FechaFinal' => (new \DateTime('today'))->format('Y-m-d')
            ));
     ...

This is my repository function:

public function FindByEmpleadoAndDateInterval($parameters = array(), $limit = null){

...
    $qb = $this->createQueryBuilder('q');
    $qb
        ->where('q.IdEmpleado = :IdEmpleado')
        ->andWhere('q.Fecha > :FechaInicial')
        ->andWhere('q.Fecha < :FechaFinal')
        ->setParameter('IdEmpleado', $parameters['IdEmpleado'])
        ->setParameter('FechaInicial', $parameters['FechaInicial'])
        ->setParameter('FechaFinal', $parameters['FechaFinal'])
    ;

    return $qb->getQuery()->execute();
}

Upvotes: 2

Views: 1365

Answers (1)

Felix Aballi
Felix Aballi

Reputation: 949

Folks, careful with this, have a nice look to the format of dates when you're working with Doctrine 2. The problem was this:

-I've set dates format as: 'Y-M-d', but: 'Ymd' was the correct one (in my particular case). So, have faith in Doctrine 2 and try every known format (Y-m-d), (Y/m/d), etc. So, you could use dates intervals in these simple ways:

 public function findByEmpleadoAndDateInterval($parameters = array(), $limit = null)
    {
        $qb = $this->createQueryBuilder('q');
        $qb->where('q.IdEmpleado = :IdEmpleado')
            ->andWhere('q.Fecha between :FechaInicial and :FechaFinal')
            ->setParameters($parameters);

        return $qb->getQuery()->execute();
    }

OR

public function findByEmpleadoAndDateInterval($parameters = array(), $limit = null)
    {
        $qb = $this->createQueryBuilder('q');
        $qb->where('q.IdEmpleado = :IdEmpleado')
            ->andWhere('q.Fecha >= :FechaInicial and q.Fecha <= :FechaFinal')
            ->setParameters($parameters);

        return $qb->getQuery()->execute();
    }

Maybe, out there, there are more elaborated examples, but this case took me a while to figure it out. Specially because isn't directly accesible online.

Upvotes: 1

Related Questions