Doctrine querybuilder：参数BETWEEN prop1和prop2

Question

Hey i have a question about the querybuilder.

I´m inside an EntityRepository

this code finds all between from and to

public function getBySpan($from,$to)
{

    $from = new \DateTime($from->format("Y-m-d")." 00:00:00");
    $to   = new \DateTime($to->format("Y-m-d")." 23:30:00");
    $qb = $this->createQueryBuilder("e");
    $qb
        ->andWhere('e.date BETWEEN :from AND :to')
        ->setParameter('from', $from )
        ->setParameter('to', $to)
    ;
    $result = $qb->getQuery()->getResult();

    return $result;
}

what i try to archieve is find all where $date is between e.from AND e.to

    public function getByDate($date)
{

    $date = new \DateTime($date->format("Y-m-d")." 00:05:50");
    $qb = $this->createQueryBuilder("e");
    $qb
        /* HOW TO PUT THIS LINE TO WORK ? */
        ->andWhere(':date BETWEEN e.from AND e.to')
        ->setParameter('date', $date)
    ;
    $result = $qb->getQuery()->getResult();

    return $result;
}

for any help thanks in advance

Answer 1

I am not so familiar with this query builder but you can try something like this

->andWhere('e.from <= :date')
->andWhere('e.to >= :date')
->setParameter('date', $date);

This is something similar to what you are trying to achieve.