I’m using Symfony 2 with Doctrine, and I’ve got two entities joined in a many to many association. Let’s say I have two entities: User and Group, and the related tables on db are users, groups and users_groups.
I’d like to get the top 10 most populated groups in DQL, but I don’t know the syntax to perform queries on the join table (users_groups). I already looked on the Doctrine manual but I didn’t found the solution, I guess I still have a lot to learn about DQL.
In plain sql that would be:
select distinct group_id, count(*) as cnt from users_groups group by group_id order by cnt desc limit 10
Can you please help me to translate this to DQL?
Update (classes):
/** * EntityE_User * * @ORMTable(name="users") * @ORMEntity */ class E_User { /** * @ORMManyToMany(targetEntity="E_Group", cascade={"persist"}) * @ORMJoinTable(name="users_groups", * joinColumns={@ORMJoinColumn(name="user_id", referencedColumnName="id", onDelete="cascade")}, * inverseJoinColumns={@ORMJoinColumn(name="group_id", referencedColumnName="id", onDelete="cascade")} * ) */ protected $groups; /** * @var integer $id * * @ORMColumn(name="id", type="integer") * @ORMId * @ORMGeneratedValue(strategy="AUTO") */ private $id; /** * @var string $name * * @ORMColumn(name="name", type="string", length=255) */ private $name; /* ... other attributes & getters and setters ...*/ } /** * EntityE_Group * * @ORMTable(name="groups") * @ORMEntity */ class E_Group { /** * @var integer $id * * @ORMColumn(name="id", type="integer") * @ORMId * @ORMGeneratedValue(strategy="AUTO") */ private $id; /** * @var string $name * * @ORMColumn(name="text", type="string", length=255) */ private $name; /* ... other attributes & getters and setters ...*/ }
Advertisement
Answer
It’s not easy without seeing the actual classes, but by guessing you have a many-to-many bidirectional relationship:
$dql = "SELECT g.id, count(u.id) as cnt FROM EntityGroup g " . "JOIN g.users u GROUP BY g.id ORDER BY cnt DESC LIMIT 10;"; $query = $em->createQuery($dql); $popularGroups = $query->getArrayResult();
UPDATE:
You don’t have to use a bidirectional relationship, you can query the other way around:
$dql = "SELECT g.id, count(u.id) as cnt FROM EntityUser u " . "JOIN u.groups g GROUP BY g.id ORDER BY cnt DESC LIMIT 10;";