Get all users and order by the number of post in symfony

Question

I have two entity, User & Post. These entities are connected with onetomany and manytoone relations. The connections are working fine. I've been trying to create a query that would get all users with post and order them by their number of post in descending order.

User Entity:

/**
     * @ORM\OneToMany(targetEntity="Post", mappedBy="user")
     */
     private $post;

Post Entity:

/**
     * @ORM\ManyToOne(targetEntity="User", inversedBy="post")
     * @ORM\JoinColumn(name="user", referencedColumnName="id", onDelete="CASCADE")
     */
     private $user;

There's no problem, basically, I just can't figure out how to make the query in symfony.

So, here's the basic query that returns all users

$query = $this->createQueryBuilder('u')
            ->leftJoin('u.post', 'p')
            ->getQuery();

        return $query->getResult();

Answer 1

Ok, so I seem to manage to sort his out. Here is the query:

$query = $this->createQueryBuilder('u')
            ->select('u')
            ->leftJoin('u.post', 'p')
            ->addSelect('COUNT(p) AS total')
            ->groupBy('u.id')
            ->orderBy('total', 'DESC')
            ->getQuery();

        return $query->getResult();

Answer 2

Sorry I can't reply as comments, I'm glad you got it sorted. But please consider the future, and scalability. It is not a good idea to count posts every time, or even once to be brutally honest. Consider doing what most forum softwares do now and when a post is made increment a field instead (call it post_count or something).

You can either decrement it on post deletion, or offer a 'recount' option where admins can force a recount but this should be a last resort.