Linked Questions

Popular Questions

There are entities: Event, EventIncident and Country.
A country can contain multiple events. An event can contain multiple incidents.

/**
 * @ORM\Entity()
 */
class Country {
    /**
     * @var ArrayCollection
     * @ORM\OneToMany(targetEntity="App\Entity\Event", mappedBy="country")
     */
    private $events;
}

/**
 * @ORM\Entity()
 */
class Event {
    /**
     * @var Country
     * @ORM\ManyToOne(targetEntity="App\Entity\Country", inversedBy="events")
     */
    private $country;

    /**
     * @var ArrayCollection
     * @ORM\OneToMany(targetEntity="App\Entity\EventIncident", mappedBy="event")
     */
    private $eventIncidents;
}

/**
 * @ORM\Entity()
 */
class EventIncident {
    /**
     * @var Event
     * @ORM\ManyToOne(targetEntity="App\Entity\Event", inversedBy="eventIncidents")
     */
    private $event;
}

What I need is to select countries and order events of each country by number of eventIncidents. How can I do it with querybuilder?

I tried using 2 separate queries: one for getting events and their incidents, and one for getting countries and their events, but it doesn't work. Probably, I am doing something wrong.

$qNumberOfEventIncidents = $this->getEntityManager()->getRepository(Event::class)
    ->createQueryBuilder('e')
    ->select('e, COUNT(ei) AS incidentsNumber')
    ->leftJoin('e.eventIncidents', 'ei')
    ->groupBy('e');  
$query = $this->createQueryBuilder('c')
    ->addSelect('c')
    ->innerJoin('c.events', 'ce')
    ->addSelect('ce')
    ->leftJoin('ei', $qNumberOfEventIncidents)
    ->addOrderBy('ei.incidentsNumber', 'DESC');

Related Questions