CODEWITHSUNDEEP
mysqlsymfonyormdoctrine-orm
Lukas Klizas
Lukas Klizas

Reputation: 175

Doctrine2 One2Many related entity filter results

Basically, I have two entities, Place and Event:

/**
 * Place
 *
 * @ORM\Table(name="place")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\PlaceRepository")
 */

class Place
{
   /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;



   /**
     * @ORM\OneToMany(targetEntity="AppBundle\Entity\Event", mappedBy="place")
     */
    private $events;
}

and

class Event
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var \DateTime
     * @JMS\Type("DateTime<'H:i'>")
     * @ORM\Column(name="event_starts", type="datetime")
     */
    private $eventStarts;

    /**
     * @var \DateTime
     * @JMS\Type("DateTime<'Y/m/d H:i'>")
     * @ORM\Column(name="event_ends", type="datetime", nullable=true)
     */
    private $eventEnds;

    /**
     * @var string
     *
     * @ORM\Column(name="title", type="string", length=255)
     */
    private $title;

    /**
     * @var string
     *
     * @ORM\Column(name="event_id", type="string", length=70)
     */
    private $event_id;

    /**
     * @JMS\Exclude()
     * @ORM\ManyToOne(targetEntity="Place")
     * @ORM\JoinColumn(name="place_id", referencedColumnName="id")
     */
    private $place;

When I'm querying Places, I would like that not all Events would be joined, but only those which are taking place today (eventStarts = today). Is there a way to do it only with Doctrine or MySQL?

Upvotes: 0

Views: 71

Answers (1)

Richard
Richard

Reputation: 4119

Pretty straightforward.

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

If you want to only get places which have an event in a specific date range:

    $data = $qb->select('p, e')
        ->from('YourBundle:Place', 'p')
        ->join('p.events', 'e')
        ->where($qb->expr()->between('e.eventStarts', ':start', ':end'))
        ->setParameter('start', $someStartDateTimeObject)
        ->setParameter('end', $someEndDateTimeObject)
        ->getQuery()
        ->getResult();

If you would like to get all places, even if they don't have an event, but join an event if there is one in the specified range:

    $data = $qb->select('p, e')
        ->from('YourBundle:Place', 'p')
        ->leftJoin('p.events', 'e', 'WITH', $qb->expr()->between('e.eventStarts', ':start', ':end'))
        ->setParameter('start', $someStartDateTimeObject)
        ->setParameter('end', $someEndDateTimeObject)
        ->getQuery()
        ->getResult();

Upvotes: 1

Related Questions