Home:ALL Converter>Use a where condition on a datetime field of an associated entity with Doctrine2?

Use a where condition on a datetime field of an associated entity with Doctrine2?

Ask Time:2014-09-06T03:43:53         Author:Miles M.

Json Formatter

I basically have a Chat entity which is linked by a ManyToOne associatino to an event. So One chat can only refer to one event but one event can have multiple chats. Each event have a dateEnd which specify at which date does the event ends.

Basically:

class Chat
{
    /**
     * Bidirectional - Many Requests are associated to one Event (OWNING SIDE)
     *
     * @ORM\ManyToOne(targetEntity="Entity\Event", inversedBy="invitations")
     */
    private $event;
}

and in my event:

class Event
{
    /**
     * @var \DateTime
     *
     * @ORM\Column(name="date_end", type="datetime", nullable=true, unique=false)
     */
     private $dateEnd;


     /**
     * Bidirectional - One-To-Many (INVERSE SIDE)
     *
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\OneToMany(targetEntity="Entity\Request", mappedBy="event", cascade={"remove"})
     */
     private $invitations;
}

I am trying to grab the events that have not ended yet. But I can't have it working:

This works :

    $qb = $this->getEntityManager()->createQueryBuilder();

    $qb->select( 'CHATS', 'messages' )
        ->from( 'Entity\Chat',  'CHATS' )
        ->where( 'CHATS.user = :user' )
        ->leftJoin( 'CHATS.messages', 'messages' )
        ->orderBy( 'CHATS.lastActive', 'DESC' );

    $array = array(
         'user' => $user
    );

    $qb->setParameters( $array );

    $newMessages = $qb->getQuery()->getResult();

This doesn't work and I don't know why:

    $qb = $this->getEntityManager()->createQueryBuilder();

    $qb->select( 'CHATS', 'messages' )
        ->from( 'Entity\Chat',  'CHATS' )
        ->where( 'CHATS.user = :user' )
        ->andWhere(
            $qb->expr()->gt( 'CHATS.event.dateEnd', ':yesterday' )
        )
        ->leftJoin( 'CHATS.messages', 'messages' )
        ->orderBy( 'CHATS.lastActive', 'DESC' );


    //Only take notifs that end after the beggining of the day ..
    $yesterday = new \DateTime( 'NOW', new \DateTimeZone('America/Los_Angeles') );

    $array = array(
         'yesterday' => $yesterday,
         'user' => $user
    );

    $qb->setParameters( $array );

    $newMessages = $qb->getQuery()->getResult();

So Doctrine doesn't like the

->andWhere( $qb->expr()->gt( 'CHATS.event.dateEnd', ':yesterday' ) )

Why ?

Many Thanks for your insights.

Author:Miles M.,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/25692938/use-a-where-condition-on-a-datetime-field-of-an-associated-entity-with-doctrine2
yy