Fix query, replace aliases

Add test case

diff --git a/galette/lib/Galette/Repository/Groups.php b/galette/lib/Galette/Repository/Groups.php
index 899d946c4469e44ba4ba9610d72dbd87b87d68be..e07cdcf3949a9e8750c46f0b32e3e9a9b148588d 100644 (file)
--- a/galette/lib/Galette/Repository/Groups.php
+++ b/galette/lib/Galette/Repository/Groups.php
@@ -127,41 +127,42 @@ class Groups
     public function getList(bool $full = true, int $id = null): array
     {
         try {
-            $select = $this->zdb->select(Group::TABLE, 'a');
-            $select->join(
-                array('b' => PREFIX_DB . Group::GROUPSUSERS_TABLE),
-                'a.' . Group::PK . '=b.' . Group::PK,
-                array('members' => new Expression('count(b.' . Group::PK . ')')),
-                $select::JOIN_LEFT
-            );
+            $select = $this->zdb->select(Group::TABLE, 'ggroup');
 
             if (!$this->login->isAdmin() && !$this->login->isStaff() && $full === true) {
                 $select->join(
-                    array('c' => PREFIX_DB . Group::GROUPSMANAGERS_TABLE),
-                    'a.' . Group::PK . '=c.' . Group::PK,
+                    array('gmanagers' => PREFIX_DB . Group::GROUPSMANAGERS_TABLE),
+                    'ggroup.' . Group::PK . '=gmanagers.' . Group::PK,
                     array()
-                )->where(['c.' . Adherent::PK => $this->login->id]);
+                )->where(['gmanagers.' . Adherent::PK => $this->login->id]);
             }
 
+            $select->join(
+                array('gusers' => PREFIX_DB . Group::GROUPSUSERS_TABLE),
+                'ggroup.' . Group::PK . '=gusers.' . Group::PK,
+                array('members' => new Expression('count(gusers.' . Group::PK . ')')),
+                $select::JOIN_LEFT
+            );
+
             if ($full !== true) {
-                $select->where('parent_group IS NULL');
+                $select->where('ggroup.parent_group IS NULL');
             }
 
             if ($id !== null) {
-                $select->where(
-                    array(
-                        'a.' . Group::PK => $id,
-                        'a.parent_group' => $id
-                    ),
-                    PredicateSet::OP_OR
-                );
+                $select->where
+                    ->nest()
+                        ->equalTo('ggroup.' . Group::PK, $id)
+                    ->or
+                        ->equalTo('ggroup.parent_group', $id)
+                    ->unnest()
+                ;
             }
 
-            $select->group('a.' . Group::PK)
-                ->group('a.group_name')
-                ->group('a.creation_date')
-                ->group('a.parent_group')
-                ->order('a.group_name ASC');
+            $select->group('ggroup.' . Group::PK)
+                ->group('ggroup.group_name')
+                ->group('ggroup.creation_date')
+                ->group('ggroup.parent_group')
+                ->order('ggroup.group_name ASC');
 
             $groups = array();
 
@@ -174,10 +175,6 @@ class Groups
             }
             if ($full) { // Order by tree name instead of name
                 ksort($groups);
-                Analog::log(
-                    'Groups SORTED: ' . print_r(array_keys($groups), true),
-                    Analog::DEBUG
-                );
             }
             return $groups;
         } catch (Throwable $e) {
@@ -192,7 +189,7 @@ class Groups
     /**
      * Loads managed groups for specific member
      *
-     * @param int     $id       Memebr id
+     * @param int     $id       Member id
      * @param boolean $as_group Retrieve Group[] or int[]
      *
      * @return array
@@ -218,12 +215,12 @@ class Groups
             $join_table = ($managed) ?
                 Group::GROUPSMANAGERS_TABLE : Group::GROUPSUSERS_TABLE;
 
-            $select = $zdb->select(Group::TABLE, 'a');
+            $select = $zdb->select(Group::TABLE, 'group');
             $select->join(
                 array(
                     'b' => PREFIX_DB . $join_table
                 ),
-                'a.' . Group::PK . '=b.' . Group::PK,
+                'group.' . Group::PK . '=b.' . Group::PK,
                 array()
             )->where(array('b.' . Adherent::PK => $id));
 
@@ -398,7 +395,7 @@ class Groups
             $zdb->execute($del_qry);
         } catch (Throwable $e) {
             Analog::log(
-                'Unable to remove member #' . $id . ' from his groups: ' .
+                'Unable to remove member #' . implode(', ', $ids) . ' from his groups: ' .
                 $e->getMessage(),
                 Analog::ERROR
             );
@@ -473,12 +470,12 @@ class Groups
             $groups = self::loadManagedGroups($this->login->id, false);
         }
 
-        $select = $this->zdb->select(Adherent::TABLE, 'a');
+        $select = $this->zdb->select(Adherent::TABLE, 'adh');
         $select->columns(
             [Adherent::PK]
         )->join(
             array('b' => PREFIX_DB . Group::GROUPSUSERS_TABLE),
-            'a.' . Adherent::PK . '=b.' . Adherent::PK,
+            'adh.' . Adherent::PK . '=b.' . Adherent::PK,
             []
         )->where->in('b.' . Group::PK, $groups);
 

diff --git a/tests/Galette/Repository/tests/units/Groups.php b/tests/Galette/Repository/tests/units/Groups.php
index 84cc4159e242ab2a2e7f6357b6aa43f99b22c0f0..d81739381dc42e67186d6a9d273a281ac1969e7e 100644 (file)
--- a/tests/Galette/Repository/tests/units/Groups.php
+++ b/tests/Galette/Repository/tests/units/Groups.php
@@ -56,6 +56,7 @@ class Groups extends GaletteTestCase
     private $parents = [];
     private $children = [];
     private $subchildren = [];
+    protected $seed = '855224771456';
 
     /**
      * Tear down tests
@@ -76,6 +77,12 @@ class Groups extends GaletteTestCase
     {
         $zdb = new \Galette\Core\Db();
 
+        //Clean managers
+        $zdb->db->query(
+            'TRUNCATE TABLE ' . PREFIX_DB . \Galette\Entity\Group::GROUPSMANAGERS_TABLE,
+            \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE
+        );
+
         $groups = $this->groupsProvider();
         foreach ($groups as $group) {
             foreach ($group['children'] as $child) {
@@ -91,6 +98,10 @@ class Groups extends GaletteTestCase
         $delete = $zdb->delete(\Galette\Entity\Group::TABLE);
         $zdb->execute($delete);
 
+        $delete = $zdb->delete(\Galette\Entity\Adherent::TABLE);
+        $delete->where(['fingerprint' => 'FAKER' . $this->seed]);
+        $zdb->execute($delete);
+
         //Clean logs
         $zdb->db->query(
             'TRUNCATE TABLE ' . PREFIX_DB . \Galette\Core\History::TABLE,
@@ -203,6 +214,7 @@ class Groups extends GaletteTestCase
     public function testGetList()
     {
         $this->logSuperAdmin();
+
         $groups = new \Galette\Repository\Groups($this->zdb, $this->login);
 
         $parents_list = $groups->getList(false);
@@ -218,14 +230,25 @@ class Groups extends GaletteTestCase
 
         $children_list = $groups->getList(true, $europe);
         $this->array($children_list)->hasSize(4);
+
+        //set manager on one group, impersonate him, and check it gets only one group
+        $this->getMemberOne();
+        $group = new \Galette\Entity\Group((int)$europe);
+        $this->boolean($group->setManagers([$this->adh]))->isTrue();
+
+        $this->login->impersonate($this->adh->id);
+
+        $groups = new \Galette\Repository\Groups($this->zdb, $this->login);
+        $parents_list = $groups->getList();
+        $this->array($parents_list)->hasSize(1);
     }
 
     /**
-     * Test group name unicity
+     * Test group name uniqueness
      *
      * @return void
      */
-    public function testUnicity()
+    public function testUniqueness()
     {
         $group = new \Galette\Entity\Group();
         $group->setLogin($this->login);