Shorten air bundle route aliases

[airbundle] / Repository / UserRepository.php

diff --git a/Repository/UserRepository.php b/Repository/UserRepository.php
index 9e869e1cd4f1122be62734e2ae19b766b47d3a20..39185516d7f45c61d2e0a9129485cc6919766d52 100644 (file)
--- a/Repository/UserRepository.php
+++ b/Repository/UserRepository.php
@@ -11,19 +11,23 @@
 
 namespace Rapsys\AirBundle\Repository;
 
 
 namespace Rapsys\AirBundle\Repository;
 

+use Doctrine\ORM\AbstractQuery;

 use Doctrine\ORM\Query\ResultSetMapping;
 use Doctrine\ORM\Query\ResultSetMapping;

+

 use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
 
 use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
 

+use Rapsys\AirBundle\Repository;
+

 /**
  * UserRepository
  */
 /**
  * UserRepository
  */

-class UserRepository extends EntityRepository {
+class UserRepository extends Repository {

        /**
         * Find users with translated highest group and civility
         *
         * @return array The user ids keyed by group and pseudonym
         */
        /**
         * Find users with translated highest group and civility
         *
         * @return array The user ids keyed by group and pseudonym
         */

-       public function findIndexByGroupPseudonym(): array {
+       public function findChoicesAsArray(): array {

                //Set the request
                $req =<<<SQL
 SELECT
                //Set the request
                $req =<<<SQL
 SELECT


@@ -37,10 +41,11 @@ FROM (
                u.pseudonym,
                g.id AS g_id,
                g.title AS g_title
                u.pseudonym,
                g.id AS g_id,
                g.title AS g_title

-       FROM RapsysAirBundle:User AS u
-       LEFT JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
-       LEFT JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
-       ORDER BY g.id DESC
+       FROM Rapsys\AirBundle\Entity\User AS u
+       JOIN Rapsys\AirBundle\Entity\UserGroup AS gu ON (gu.user_id = u.id)
+       JOIN Rapsys\AirBundle\Entity\Group AS g ON (g.id = gu.group_id)
+       WHERE g.title <> 'User'
+       ORDER BY g.id DESC, u.pseudonym ASC

        LIMIT 0, :limit
 ) AS a
 GROUP BY a.id
        LIMIT 0, :limit
 ) AS a
 GROUP BY a.id


@@ -74,10 +79,11 @@ SQL;
                //Process result
                foreach($res as $data) {
                        //Without group or simple user
                //Process result
                foreach($res as $data) {
                        //Without group or simple user

-                       if (empty($data['g_title']) || $data['g_title'] == 'User') {
-                               //Skip it
-                               continue;
-                       }
+                       #XXX: moved in sql by removing LEFT JOIN and excluding user group
+                       #if (empty($data['g_title']) || $data['g_title'] == 'User') {
+                       #       //Skip it
+                       #       continue;
+                       #}

 
                        //Get translated group
                        $group = $this->translator->trans($data['g_title']);
 
                        //Get translated group
                        $group = $this->translator->trans($data['g_title']);


@@ -89,13 +95,57 @@ SQL;
 
                        //Set data
                        //XXX: ChoiceType use display string as key
 
                        //Set data
                        //XXX: ChoiceType use display string as key

-                       $ret[$group][$data['pseudonym']] = $data['id'];
+                       $ret[$group][trim($data['pseudonym'].' ('.$data['id'].')')] = intval($data['id']);

                }
 
                //Send result
                return $ret;
        }
 
                }
 
                //Send result
                return $ret;
        }
 

+       /**
+        * Find user ids by pseudonym
+        *
+        * @param array $pseudonym The pseudonym filter
+        * @return array The user ids
+        */
+       public function findIdByPseudonymAsArray(array $pseudonym): array {
+               //Set the request
+               $req =<<<SQL
+SELECT
+       a.id
+FROM (
+       SELECT
+               u.id
+       FROM Rapsys\AirBundle\Entity\User AS u
+       LEFT JOIN Rapsys\AirBundle\Entity\UserGroup AS gu ON (gu.user_id = u.id)
+       WHERE u.pseudonym IN (:pseudonym)
+       ORDER BY gu.group_id DESC, u.pseudonym ASC
+       LIMIT 0, :limit
+) AS a
+GROUP BY a.id
+ORDER BY NULL
+SQL;
+
+               //Replace bundle entity name by table name
+               $req = str_replace($this->tableKeys, $this->tableValues, $req);
+
+               //Get result set mapping instance
+               //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
+               $rsm = new ResultSetMapping();
+
+               //Declare all fields
+               //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
+               //XXX: we don't use a result set as we want to translate group and civility
+               $rsm->addScalarResult('id', 'id', 'integer');
+
+               //Return result
+               return $this->_em
+                       ->createNativeQuery($req, $rsm)
+                       ->setParameter('pseudonym', $pseudonym)
+                       //XXX: instead of array_column on the result
+                       ->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN);
+       }
+

        /**
         * Find applicant by session id
         *
        /**
         * Find applicant by session id
         *


@@ -106,8 +156,8 @@ SQL;
                //Set the request
                $req =<<<SQL
 SELECT u.id, u.pseudonym
                //Set the request
                $req =<<<SQL
 SELECT u.id, u.pseudonym

-FROM RapsysAirBundle:Application AS a
-JOIN RapsysAirBundle:User AS u ON (u.id = a.user_id)
+FROM Rapsys\AirBundle\Entity\Application AS a
+JOIN Rapsys\AirBundle\Entity\User AS u ON (u.id = a.user_id)

 WHERE a.session_id = :id
 SQL;
 
 WHERE a.session_id = :id
 SQL;
 


@@ -171,11 +221,11 @@ SELECT
        GROUP_CONCAT(g.id ORDER BY g.id SEPARATOR "\\n") AS ids,
        GROUP_CONCAT(g.title ORDER BY g.id SEPARATOR "\\n") AS titles,
        GREATEST(COALESCE(u.updated, 0), COALESCE(c.updated, 0), COALESCE(o.updated, 0)) AS modified
        GROUP_CONCAT(g.id ORDER BY g.id SEPARATOR "\\n") AS ids,
        GROUP_CONCAT(g.title ORDER BY g.id SEPARATOR "\\n") AS titles,
        GREATEST(COALESCE(u.updated, 0), COALESCE(c.updated, 0), COALESCE(o.updated, 0)) AS modified

-FROM RapsysAirBundle:User AS u
-LEFT JOIN RapsysAirBundle:Civility AS c ON (c.id = u.civility_id)
-LEFT JOIN RapsysAirBundle:Country AS o ON (o.id = u.country_id)
-LEFT JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
-LEFT JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
+FROM Rapsys\AirBundle\Entity\User AS u
+LEFT JOIN Rapsys\AirBundle\Entity\Civility AS c ON (c.id = u.civility_id)
+LEFT JOIN Rapsys\AirBundle\Entity\Country AS o ON (o.id = u.country_id)
+LEFT JOIN Rapsys\AirBundle\Entity\UserGroup AS gu ON (gu.user_id = u.id)
+LEFT JOIN Rapsys\AirBundle\Entity\Group AS g ON (g.id = gu.group_id)

 WHERE u.id = :id
 SQL;
 
 WHERE u.id = :id
 SQL;
 


@@ -276,7 +326,8 @@ SQL;
                //Iterate on each location
                foreach(explode("\n", $result['ids']) as $k => $id) {
                        //Add role
                //Iterate on each location
                foreach(explode("\n", $result['ids']) as $k => $id) {
                        //Add role

-                       $roles[$role = 'ROLE_'.strtoupper($titles[$k])] = $role;
+                       //XXX: roles are keyes by id
+                       $roles[$id] = 'ROLE_'.strtoupper($titles[$k]);

 
                        //Add group
                        $groups[$id] = $this->translator->trans($titles[$k]);
 
                        //Add group
                        $groups[$id] = $this->translator->trans($titles[$k]);


@@ -352,14 +403,14 @@ FROM (
                        u.pseudonym,
                        g.id AS g_id,
                        g.title AS g_title
                        u.pseudonym,
                        g.id AS g_id,
                        g.title AS g_title

-               FROM RapsysAirBundle:User AS u
-               JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
-               JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
+               FROM Rapsys\AirBundle\Entity\User AS u
+               JOIN Rapsys\AirBundle\Entity\UserGroup AS gu ON (gu.user_id = u.id)
+               JOIN Rapsys\AirBundle\Entity\Group AS g ON (g.id = gu.group_id)

                ORDER BY NULL
                LIMIT 0, :limit
        ) AS c
                ORDER BY NULL
                LIMIT 0, :limit
        ) AS c

-       LEFT JOIN RapsysAirBundle:Application AS a ON (a.user_id = c.id)
-       LEFT JOIN RapsysAirBundle:Dance AS d ON (d.id = a.dance_id)
+       LEFT JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.user_id = c.id)
+       LEFT JOIN Rapsys\AirBundle\Entity\Dance AS d ON (d.id = a.dance_id)

        GROUP BY d.id
        ORDER BY NULL
        LIMIT 0, :limit
        GROUP BY d.id
        ORDER BY NULL
        LIMIT 0, :limit