Try to sort a result with a count on a manytomany relation

Question

I'm facing a problem when I'm trying to do a count on a many-to-many relation and after order the result by it.

Here is my relation Entity :

/**
 * @ORM\ManyToMany(targetEntity="QuoteBox\Bundle\UserBundle\Entity\User", inversedBy="likedQuotes")
 * @ORM\JoinTable(
 *     name="JNT_liker",
 *     joinColumns={
 *         @ORM\JoinColumn(
 *             name="QUOTE_id",
 *             referencedColumnName="QUOTE_id",
 *             nullable=false
 *         )
 *     },
 *     inverseJoinColumns={@ORM\JoinColumn(name="USER_id", referencedColumnName="USER_id", nullable=false)}
 * )
 */
private $likers;

Here is my Repository method :

/**
 * Find All by number of Likes Adapter
 * @return Pagerfanta
 */
public function findAllByLikesAdapter(Location $location)
{
    $query = $this->getCommonQuery($location);
    $query->select('q, count(q.likers) as HIDDEN num_like');
    $query->innerJoin('q.likers', 'likers');
    $query->groupBy('q.id');
    $query->orderBy('num_like', 'ASC');
    return new Pagerfanta(new DoctrineORMAdapter($query));
}

Here is my controller :

    public function topAction(Location $location, $page = 1)
    {
    $pager = $this->getDoctrine()->getRepository("QuoteBoxQuoteBundle:Quote")->findAllByLikesAdapter($location);
    $pager->setMaxPerPage(5);
    $pager->setCurrentPage($page);
    return [ 'pager' => $pager, 'location' => $location ];
    }

Here is my view using twig and pagerfanta :

{% for quote in pager.currentPageResults %}
    {% include "QuoteBoxQuoteBundle:line:quote.html.twig" with {'quote':quote, 'currentPage':pager.currentPage } only %}
{% endfor %}

And when I execute it here is the error :

> An exception has been thrown during the rendering of a template ("[Semantical Error] line 0, col 18 near 'likers) as HIDDEN': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.") in QuoteBoxQuoteBundle:Quotes:quotes.html.twig at line 13.

Can you help me ?

Answer 1

thanks you all.

It works, Here is my solution

    $query = $this->getCommonQuery($location);
    $query->addSelect('q, COUNT(likers.id) AS HIDDEN num_likers');
    $query->leftJoin('q.likers', 'likers');
    $query->groupBy('q.id');
    $query->orderBy('num_likers', 'DESC');

I had to add leftJoin instead of join in order to have all of my quotes.