how to execute JOIN query with 3 tables, order by count of ID

Question

I have 3 tables: Event, EventSeans, Firms. I need to return Firm, and order by count Event in this Firm Event entity:

 /**
 * @ORM\OneToMany(
 *     targetEntity="EventSeans",
 *     cascade={"persist", "remove", "merge"},
 *    mappedBy="event"
 *  )
 *  @ORM\OrderBy({"start_time"= "ASC"})
 */
protected $seanses;

EventSeans entity:

 /**
 * @ORM\ManyToOne(
 *      targetEntity="Application\GidBundle\Entity\Firm")
 */
protected $place;

 /**
 * @ORM\ManyToOne(
 *      targetEntity="Event",
 *      inversedBy="seanses"
 * )* 
 */
protected $event;

I tried this:

        $qb
        ->select('f, count(e.id) cnt')
        ->from('AfishaBundle:Event', 'e')
        ->from('GidBundle:Firm', 'f')
        ->leftJoin('e.seanses', 's')
        ->where('s.place = f.id')
        ->orderBy('cnt','ASC')
        ->setMaxResults(3)
    ;

EDIT i add relation in Firm Entity

    /**
 * @ORM\OneToMany(
 *     targetEntity="Application\AfishaBundle\Entity\EventSeans",
 *     cascade={"persist", "remove", "merge"},
 *    mappedBy="place"
 *  )
 *  @ORM\OrderBy({"start_time"= "ASC"})
 */
protected $seanses;

and my query is

        $qb
        ->select('f, count(e.id) cnt')
        ->from('GidBundle:Firm', 'f')
        ->leftJoin('f.seanses','s')
        ->leftJoin('s.event', 'e')
        ->orderBy('cnt', 'DESC')
        ->groupBy('f.id')
        ->setMaxResults(20)
    ;

But cnt - is a count of my seanses... It's nothing changes:(

add

CREATE TABLE IF NOT EXISTS `afisha_event_seanses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `place_id` int(11) DEFAULT NULL,
  `event_id` int(11) DEFAULT NULL,
  `start_time` datetime NOT NULL,
  `allday` tinyint(1) NOT NULL,
  `cost` int(11) NOT NULL,
  `hall` varchar(255) NOT NULL,
  `region` varchar(255) NOT NULL,
  `end_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_3164A4B7DA6A219` (`place_id`),
  KEY `IDX_3164A4B771F7E88B` (`event_id`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

 ALTER TABLE `afisha_event_seanses`
  ADD CONSTRAINT `FK_3164A4B771F7E88B` FOREIGN KEY (`event_id`) REFERENCES `afisha_events` (`id`),
   ADD CONSTRAINT `FK_3164A4B7DA6A219` FOREIGN KEY (`place_id`) REFERENCES `gid_firms` (`id`);

Answer 1

If I suppose your entity relationships are well defined.

The following code should return Firms with count of Events related to each Firm.

Add a method in your FirmRepository which contain,

       $query = $this->createQueryBuilder('f')
           ->select(' f, count(distinct e.id)')
           ->leftJoin('f.seanses', 's')
           ->leftJoin('s.event', 'e')
           ->groupBy('f.id')
           ->getQuery()
           ->getArrayResult();

Where event define a many-to-one relationship from your EventSeans Entity to your Event Entity as follow,

/**
 * @ORM\ManyToOne(targetEntity="Application\GidBundle\Entity\Event")
 */
protected $event;