DQL is a lot for many and take

Question

I use Symfony 2 with Doctrine, and I have two entities combined in many ways. Let's say I have two objects: "User and group", and the related tables on db are users, groups and users_groups.

I would like to get the 10 most popular groups in DQL, but I don’t know the syntax for executing queries in the connection table (users_groups). I already looked at the Doctrine manual, but I did not find a solution, I think I still have a lot to learn about DQL.

In simple sql, which will be:

select distinct group_id, count(*) as cnt from users_groups group by group_id order by cnt desc limit 10 

Could you help me translate this to DQL?

Update (classes):

 /** * Entity\E_User * * @ORM\Table(name="users") * @ORM\Entity */ class E_User { /** * @ORM\ManyToMany(targetEntity="E_Group", cascade={"persist"}) * @ORM\JoinTable(name="users_groups", * joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id", onDelete="cascade")}, * inverseJoinColumns={@ORM\JoinColumn(name="group_id", referencedColumnName="id", onDelete="cascade")} * ) */ protected $groups; /** * @var integer $id * * @ORM\Column(name="id", type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") */ private $id; /** * @var string $name * * @ORM\Column(name="name", type="string", length=255) */ private $name; /* ... other attributes & getters and setters ...*/ } /** * Entity\E_Group * * @ORM\Table(name="groups") * @ORM\Entity */ class E_Group { /** * @var integer $id * * @ORM\Column(name="id", type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") */ private $id; /** * @var string $name * * @ORM\Column(name="text", type="string", length=255) */ private $name; /* ... other attributes & getters and setters ...*/ } 

Answer 1

This is not easy without seeing the actual classes, but assuming you have a many-to-many bi-directional relationship:

 $dql = "SELECT g.id, count(u.id) as cnt FROM Entity\Group g " . "JOIN g.users u GROUP BY g.id ORDER BY cnt DESC LIMIT 10;"; $query = $em->createQuery($dql); $popularGroups = $query->getArrayResult(); 

UPDATE:

You do not need to use bidirectional relationships, you can request another way:

 $dql = "SELECT g.id, count(u.id) as cnt FROM Entity\User u " . "JOIN u.groups g GROUP BY g.id ORDER BY cnt DESC LIMIT 10;"; 

Answer 2

For those who want to build a query with Doctrine QueryBuilder instead of directly using DQL, follow this solution.

Please note that my problem was not to get the top user groups, but technically the problem is pretty similar to mine. I work with posts (e.g. blog articles / posts) and tags that are added to posts. I needed to define a list of related messages (identified by the same tags). This list should be sorted by relevance (the same tags that have another message are more important).

This is the method of my PostRepository class:

 /** * Finds all related posts sorted by relavance * (from most important to least important) using * the tags of the given post entity. * * @param Post $post * * @return POST[] */ public function findRelatedPosts(Post $post) { // build query $q = $this->createQueryBuilder('p') ->addSelect('count(t.id) as relevance') ->innerJoin('p.tags', 't') ->where('t.id IN (:tags)') ->setParameter('tags', $post->getTags()) ->andWhere('p.id != :post') ->setParameter('post', $post->getId()) ->addGroupBy('p.id') ->addOrderBy('relevance', 'DESC') ->getQuery(); // execute query and retrieve database result $r = $q->execute(); // result contains arrays, each array contains // the actual post and the relevance value // --> let extract the post entities and // forget about the relevance, because the // posts are already sorted by relevance $r = array_map(function ($entry) { // first index is the post, second one // is the relevance, just return the post return reset($entry); }, $r); // array of posts return $r; } 

Thanks @Tom Imrei for your decision. Answer # 26549597 was also very helpful.