2

I use Symfony 3 and I am making my own forum and I am currently at the permissions.

My table

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

Nikhil Vaghela
  • 2,088
  • 2
  • 15
  • 30
lbrugero
  • 31
  • 3
  • In the mysql configs you have [`ONLY_FULL_GROUP_BY`](http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html) enabled and thus aggregate functions(in some cases), `MAX` require a `GROUP BY` statement after. [This](http://stackoverflow.com/questions/19296704/mysql-sql-error-1140-sqlstate-42000) question explains it better than I can. – Andrei Sep 26 '16 at 08:22
  • @Andrew: I know about GROUP BY, but if I use it I got only one result and my query is really broken, that's why I am asking your help. – lbrugero Sep 26 '16 at 08:29

0 Answers0