I use Symfony 3 and I am making my own forum and I am currently at the permissions.
As you can see, my user can have many group, so I would like to get with my query, the list of my categorie (with its perms) and their forum (with perms of each of them). I want to get the best of all of his perms, in my screen, the (user_group == 2) have no perm, and the (user_group == 1) have all of the perm. I want get all of the user line and group by them and using MAX on each autorization (can_*) to know what he can do.
(I don't want the get the better group perms of the user, but rather the best of each fields)
Other example with my category_id == 1
I would get all of true result
My current query is like:
public function getCategoriesWithForums(\UserBundle\Entity\User $user)
{
$ids = array();
foreach ($user->getGroups() as $group)
{
$ids[] = $group->getId();
}
return $this->createQueryBuilder('c')
// permissions catégory
->innerJoin('c.permissions', 'cp')->addSelect('cp, MAX(cp.canSee), MAX(cp.canEnter), MAX(cp.canPost), MAX(cp.canReply), MAX(cp.canEdit), MAX(cp.canDelete)')
->andWhere('cp.userGroup IN (:ids)')->setParameter('ids', implode(',', $ids))
->innerJoin('c.forums', 'f')->addSelect('f')
// permissions forums
->innerJoin('f.permissions', 'fp')->addSelect('fp, MAX(fp.canSee), MAX(fp.canEnter), MAX(fp.canPost), MAX(fp.canReply), MAX(fp.canEdit), MAX(fp.canDelete)')
->andWhere('fp.userGroup IN (:ids)')->setParameter('ids', implode(',', $ids))
->leftJoin('f.lastPost', 'lastPost')->addSelect('lastPost')
->leftJoin('lastPost.topic', 'topicLastPost')->addSelect('topicLastPost')
->orderBy('c.position', 'ASC')
->addOrderBy('f.position', 'ASC')
->getQuery()->getResult();
}
Query generated by Symfony 3: (need 10 reputations): pastebin dot com/0vX9ej2a
And, last thing, I got this error..
SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY
expression #13 of
SELECT list contains nonaggregated column 'gtav.f2_.id'; this is incompatible with sql_mode=only_full_group_by
Do you have any idea to fix that and to get the correct result? I hope you will can help me about that