Header

The other day I ran into an interesting case trying to get data out of a Symfony project where the entities I was interested in were dependent on a second entity but I couldn’t find an easy way to find them without multiple queries. It took me a lot of reading through Stack Overflow questions to find the results but I wanted to document what I’ve found.

Please note I wrote this using Symfony 3.2.3. Last time I posted something about Symfony someone asked me to mention this.

Background

In this example, I have a super basic ticketing system. There’s a Ticket entity and a Ticket Entry entity that keeps track of when people update the tickets.

Ticket Entity

// ./src/AppBundle/Entity/Ticket.php
namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * @ORM\Entity
 * @ORM\Table(name="Ticket")
 */
class Ticket
{
    /**
     * @ORM\Id
     * @ORM\Column(name="id", type="integer");
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @ORM\Column(name="name", type="string");
     */
    protected $name;

    /**
     * @ORM\OneToMany(targetEntity="\AppBundle\Entity\Ticket\Entry", mappedBy="ticket", cascade={"persist"})
     */
    protected $entries;

    // Getters and setters excluded for space
}

Ticket Entry Entity

The Ticket Entry class is listed below. The two piece of information I would like to highlight are the user attribute which is a text representation of the user who made the change (text because it keeps the examples easier to understand) and the created attribute which keeps track of when the entry was added.

// ./src/AppBundle/Entity/Ticket/Entry.php
namespace AppBundle\Entity\Ticket;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * @ORM\Entity
 * @ORM\Table(name="Ticket_Entry")
 */
class Entry
{
    /**
     * @ORM\Id
     * @ORM\Column(name="id", type="integer");
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @ORM\Column(name="user", type="string");
     */
    protected $user;

    /**
     * @ORM\Column(name="created", type="datetime");
     */
    protected $created;

    /**
     * @ORM\ManyToOne(targetEntity="\AppBundle\Entity\Ticket", inversedBy="entries", cascade={"persist"})
     * @ORM\JoinColumn(name="TicketId", referencedColumnName="id")
     */
    protected $ticket;

    // Getters and setters excluded for space
}

Test Data

Now we need to create some example data. We’re going to create three tickets each with an entry.

// inside a controller
$em = $this->getDoctrine()->getManager();
$ticket1 = new \AppBundle\Entity\Ticket();
$ticket1->setName('ticket1');
$entry = new \AppBundle\Entity\Ticket\Entry();
$entry
    ->setUser('scott')
    ->setTicket($ticket1)
    ->setCreated('2017-01-01 08:00:00');
$em->persist($entry);
$em->persist($ticket1);


$ticket2 = new \AppBundle\Entity\Ticket();
$ticket2->setName('ticket2');
$entry = new \AppBundle\Entity\Ticket\Entry();
$entry
    ->setUser('scott')
    ->setTicket($ticket2)
    ->setCreated('2017-02-01 08:00:00');
$em->persist($entry);
$em->persist($ticket2);

$ticket3 = new \AppBundle\Entity\Ticket();
$ticket3->setName('ticket3');
$entry = new \AppBundle\Entity\Ticket\Entry();
$entry
    ->setUser('john')
    ->setTicket($ticket3)
    ->setCreated('2017-01-01 08:00:00');
$em->persist($entry);
$em->persist($ticket3);

$em->flush();

Find By ID

Now we can talk about how to search. The most basic example is to search based on the entity’s ID column:

$repo = $this->getDoctrine()->getRepository('AppBundle:Ticket');

// this returns a single item
$found = $repo->find(1);

// output: 1
echo $found->getId();

Find By Another Field

An amazing feature of Doctrine is it’s ability to allow you to search based on another field inside the entity. For example, we can find the ticket whose name is ‘ticket2’:

$repo = $this->getDoctrine()->getRepository('AppBundle:Ticket');

// this returns an array
$found = $repo->findByName('ticket2');

// output: 1
var_dump(count($found));

// output: 2
var_dump($found[0]->getId());

Find With a Join

This is the part I spend ran into some trouble, what if we’re curious about all the tickets that were entered by ‘scott’ (so you don’t have to scroll back up it’s ticket1 and ticket2).

The solution to this is to do a join and then you’ll get the correct results:

$qb = $em->createQueryBuilder();

// this returns an array 
$tickets = $qb->select(array('t'))
    ->from('AppBundle:Ticket', 't')
    ->join('AppBundle:Ticket\\Entry', 'e')
    ->where('t.id = e.ticket')
    ->andWhere('e.user = :userName')
    ->setParameter('userName', 'scott')
    ->orderBy('e.created', 'ASC')
    ->getQuery()
    ->getResult();

// output: 2
var_dump(count($tickets));

// output: 1
var_dump($tickets[0]->getId());

I learned the hard way that you must define the parameters and then set them.

You can also search by date:

$qb = $em->createQueryBuilder();
$tickets = $qb->select(array('t'))
    ->from('AppBundle:Ticket', 't')
    ->join('AppBundle:Ticket\\Entry', 'e')
    ->where('t.id = e.ticket')
    ->andWhere('e.created >= :startDate')
    ->andWhere('e.created <= :endDate')
    ->setParameter('startDate', '2017-01-01')
    ->setParameter('endDate', '2017-01-31')
    ->orderBy('e.created', 'ASC')
    ->getQuery()
    ->getResult();