From 7fb2b50c2ae429b15291f422a4bdc6a4a289126c Mon Sep 17 00:00:00 2001 From: Johan Cwiklinski Date: Sun, 14 Nov 2021 18:36:14 +0100 Subject: [PATCH] Fix query, replace aliases Add test case --- galette/lib/Galette/Repository/Groups.php | 63 +++++++++---------- .../Galette/Repository/tests/units/Groups.php | 27 +++++++- 2 files changed, 55 insertions(+), 35 deletions(-) diff --git a/galette/lib/Galette/Repository/Groups.php b/galette/lib/Galette/Repository/Groups.php index 899d946c4..e07cdcf39 100644 --- 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 84cc4159e..d81739381 100644 --- 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); -- 2.39.2