How to use Datetime comparison as condition in JOIN statement

Question

Context :

I have two entities : Advert and Rental

Advert

/**
 * @ORM\Table(name = "advert")
 * @ORM\Entity(repositoryClass = "App\Repository\AdvertRepository")
 */
class Advert
{

    /**
     * @ORM\OneToMany(targetEntity = "App\Entity\Rental", mappedBy = "advert")
     */
    private $rentals;

    private $beginDate;

    private $endDate;

    public function addRental(\App\Entity\Rental $rental)
    {
        $this->rentals[] = $rental;
        $rental->setAdvert($this);
        return ($this);
    }

    public function getRentals()
    {
        return $this->rentals
    }

Rental

/**
 * @ORM\Table(name = "rental")
 * @ORM\Entity(repositoryClass = "App\Repository\RentalRepository")
 */
class Rental
{

    /**
     * @ORM\ManyToOne(targetEntity = "App\Entity\Advert", inversedBy = "rentals")
     */
    private $advert;

    /**
     * @var \Datetime
     */
    private $beginDate;

    /**
     * @var \Datetime
     */
    private $endDate;

    public function setAdvert(\App\Entity\Advert $advert)
    {
        $this->advert = $advert;
        return ($this);
    }

    public function getAdvert()
    {
        return $this->advert;
    }

}

---

Issue :

I'm trying to wrote a repository method that fetch in the database the adverts like a provided one. Now, i want to exclude all adverts that are rented during a given period. This is what i tried :

In Advert Repository :

//This method creates the query builder and use other methods to
//improve the query
public function fetchSearchResult(Advert $advertType)
{
    $qb = $this->createQueryBuilder('a');
    // Other methods, similar to hasValidPeriods, using other attributes.
    // Some of them preform JOIN, successfully. One of them compare dates
    // (but no JOIN) also successfully.
    // [...]
    $this->hasValidRentalPeriods($qb, $advertType->getBeginDate(), $advertType->getEndDate());

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

public function hasValidRentalPeriods(QueryBuilder $qb, \Datetime $beginDate, \Datetime $endDate)
{
    $qb->leftJoin('a.rentals', 'rental', 'WITH NOT',
    '(
        (rental.beginDate < :beginDate AND :beginDate < rental.endDate)
        OR (rental.beginDate < :endDate AND :endDate < rental.endDate)
        OR (:beginDate < rental.beginDate AND rental.endDate <:endDate)
    )')
        ->setParameter('beginDate', $beginDate)
        ->setParameter('endDate', $endDate)
    ;
}

With this left join i expected to get :

• All advert with no rentals
• All advert with no collision between the actual rentals and the dates provided in the advert

But it doesn't return the expected result. I get all adverts with no rentals as expected, but also advert with collision. In addition, removing the NOT statement doesn't change the result. That's why i realised something is wrong here.

After that i tried to use a more simple join :

$qb->leftJoin('a.rentals', 'rental', 'WITH NOT',
    '(rental.beginDate < :beginDate AND :beginDate < rental.endDate)'
    )

This time i expected to get all adverts except the ones having a rental colliding the beginDate. But i still got all adverts.

I also tried to convert the date to a string with Datetime::format('Y-m-d H:i:s'), as suggested in this post, but i still have the same result.

I'm pretty sure i misuse the comparison between Datetime objects. On the other hand, i already succeeded in perfoming such comparison. Or perhaps, my JOIN is wrong ?

Note : The rentals are setted correctly for the advert.

Note 2 : If needed, you can see the full code and tests on my github

Answer 1

try this:

public function hasValidRentalPeriods(QueryBuilder $qb, \DateTime $beginDate, \DateTime $endDate)
{
    if (!$beginDate OR !$endDate) {
        throw new PreconditionRequiredHttpException('You must provide begin and end dates');
    }

    if ($endDate <= $beginDate) {
        throw new \Exception('wrong dates');
    }

    $qb
        ->leftJoin('a.rentals', 'rental')
        ->where('rental.beginDate > :endDate')
        ->orWhere('rental.endDate < :beginDate')
        ->orWhere('rental.beginDate IS NULL')
        ->orWhere('rental.endDate IS NULL')
        ->setParameter('beginDate', $beginDate->format('Y-m-d H:i:s'))
        ->setParameter('endDate', $endDate->format('Y-m-d H:i:s'));
}

this will return all Adverts, that has no rentals in beginDate-endDate period