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

Have you checked the actual sql derived from your dql by means of log or Symfony's profile bar? Sometimes dql is interpreted not in the way you meant(even if your dql's condition is right), so maybe you could see them first. — Motoroller, Jan 10 '18 at 04:15
You're using a left join, which just means it replaces values not found with `NULL` entries. Your conditions should be in the `WHERE` clause. — ccKep, Jan 10 '18 at 11:20

score 3 · Accepted Answer · answered Jan 10 '18 at 12:48

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

How to use Datetime comparison as condition in JOIN statement

Context :

Issue :

1 Answers1