Raphaël G. Git Repositories - airbundle/blob - Repository/UserRepository.php

   1 <?php
   2
   3 namespace Rapsys\AirBundle\Repository;
   4
   5 use Symfony\Component\Translation\TranslatorInterface;
   6 use Doctrine\ORM\Query\ResultSetMapping;
   7
   8 /**
   9  * UserRepository
  10  */
  11 class UserRepository extends \Doctrine\ORM\EntityRepository {
  12         /**
  13          * Find users with translated highest group and title
  14          *
  15          * @param $translator The TranslatorInterface instance
  16          */
  17         public function findAllWithTranslatedGroupAndTitle(TranslatorInterface $translator) {
  18                 //Get entity manager
  19                 $em = $this->getEntityManager();
  20
  21                 //Get quote strategy
  22                 $qs = $em->getConfiguration()->getQuoteStrategy();
  23                 $dp = $em->getConnection()->getDatabasePlatform();
  24
  25                 //Get quoted table names
  26                 //XXX: this allow to make this code table name independent
  27                 $tables = [
  28                         'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
  29                         'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
  30                         'RapsysAirBundle:Title' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Title'), $dp),
  31                         'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp)
  32                 ];
  33
  34                 //Set the request
  35                 $req = 'SELECT a.id, a.forename, a.surname, a.t_id, a.t_short, a.t_title, a.g_id, a.g_title FROM (
  36                         SELECT u.id, u.forename, u.surname, t.id AS t_id, t.short AS t_short, t.title AS t_title, g.id AS g_id, g.title AS g_title
  37                         FROM RapsysAirBundle:User AS u
  38                         JOIN RapsysAirBundle:Title AS t ON (t.id = u.title_id)
  39                         LEFT JOIN RapsysAirBundle:GroupUser AS gu ON (gu.user_id = u.id)
  40                         LEFT JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
  41                         ORDER BY g.id DESC, NULL LIMIT '.PHP_INT_MAX.'
  42                 ) AS a GROUP BY a.id ORDER BY NULL';
  43
  44                 //Replace bundle entity name by table name
  45                 $req = str_replace(array_keys($tables), array_values($tables), $req);
  46
  47                 //Get result set mapping instance
  48                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
  49                 $rsm = new ResultSetMapping();
  50
  51                 /*XXX: we don't want a result set for our request
  52                 $rsm->addEntityResult('RapsysAirBundle:User', 'u');
  53                 $rsm->addFieldResult('u', 'id', 'id');
  54                 $rsm->addFieldResult('u', 'forename', 'forename');
  55                 $rsm->addFieldResult('u', 'surname', 'surname');
  56                 $rsm->addFieldResult('u', 't_id', 'title_id');
  57                 $rsm->addJoinedEntityResult('RapsysAirBundle:Title', 't', 'u', 'title');
  58                 $rsm->addFieldResult('t', 't_id', 'id');
  59                 $rsm->addFieldResult('t', 't_title', 'title');
  60                 $rsm->addJoinedEntityResult('RapsysAirBundle:Group', 'g', 'u', 'groups');
  61                 $rsm->addFieldResult('g', 'g_id', 'id');
  62                 $rsm->addFieldResult('g', 'g_title', 'title');*/
  63
  64                 //Declare all fields
  65                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
  66                 //addScalarResult($sqlColName, $resColName, $type = 'string');
  67                 $rsm->addScalarResult('id', 'id', 'integer')
  68                         ->addScalarResult('forename', 'forename', 'string')
  69                         ->addScalarResult('surname', 'surname', 'string')
  70                         ->addScalarResult('t_id', 't_id', 'integer')
  71                         ->addScalarResult('t_short', 't_short', 'string')
  72                         ->addScalarResult('t_title', 't_title', 'string')
  73                         ->addScalarResult('g_id', 'g_id', 'integer')
  74                         ->addScalarResult('g_title', 'g_title', 'string')
  75                         ->addIndexByScalar('id');
  76
  77                 //Fetch result
  78                 $res = $em
  79                         ->createNativeQuery($req, $rsm)
  80                         ->getResult();
  81
  82                 //Init return
  83                 $ret = [];
  84
  85                 //Process result
  86                 foreach($res as $data) {
  87                         //Get translated group
  88                         $group = $translator->trans($data['g_title']?:'User');
  89                         //Get translated title
  90                         $title = $translator->trans($data['t_short']);
  91                         //Init group subarray
  92                         if (!isset($ret[$group])) {
  93                                 $ret[$group] = [];
  94                         }
  95                         //Set data
  96                         //XXX: ChoiceType use display string as key
  97                         $ret[$group][$title.' '.$data['forename'].' '.$data['surname']] = $data['id'];
  98                 }
  99
 100                 //Send result
 101                 return $ret;
 102         }
 103
 104         /**
 105          * Find all applicant by session
 106          *
 107          * @param $session The Session
 108          */
 109         public function findAllApplicantBySession($session) {
 110                 //Get entity manager
 111                 $em = $this->getEntityManager();
 112
 113                 //Fetch sessions
 114                 $ret = $this->getEntityManager()
 115                         ->createQuery('SELECT u.id, u.pseudonym FROM RapsysAirBundle:Application a JOIN RapsysAirBundle:User u WITH u.id = a.user WHERE a.session = :session')
 116                         ->setParameter('session', $session)
 117                         ->getResult();
 118
 119                 //Process result
 120                 $ret = array_column($ret, 'id', 'pseudonym');
 121
 122                 //Send result
 123                 return $ret;
 124         }
 125
 126         /**
 127          * Find all organizer grouped by translated group
 128          *
 129          * @param $translator The TranslatorInterface instance
 130          * @return array|null The organizer array or null
 131          */
 132         public function findOrganizerGroupedByGroup(TranslatorInterface $translator) {
 133                 //Get entity manager
 134                 $em = $this->getEntityManager();
 135
 136                 //Get quote strategy
 137                 $qs = $em->getConfiguration()->getQuoteStrategy();
 138                 $dp = $em->getConnection()->getDatabasePlatform();
 139
 140                 //Get quoted table names
 141                 //XXX: this allow to make this code table name independent
 142                 $tables = [
 143                         'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
 144                         'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
 145                         'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp),
 146                         //XXX: Set limit used to workaround mariadb subselect optimization
 147                         ':limit' => PHP_INT_MAX,
 148                         "\t" => '',
 149                         "\n" => ' '
 150                 ];
 151
 152                 //Set the request
 153                 $req = <<<SQL
 154 SELECT a.id, a.pseudonym, a.g_id, a.g_title
 155 FROM (
 156         SELECT u.id, u.pseudonym, g.id AS g_id, g.title AS g_title
 157         FROM RapsysAirBundle:User AS u
 158         JOIN RapsysAirBundle:GroupUser AS gu ON (gu.user_id = u.id)
 159         JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
 160         ORDER BY g.id DESC
 161         LIMIT 0, :limit
 162 ) AS a
 163 GROUP BY a.id
 164 ORDER BY a.id ASC
 165 SQL;
 166
 167                 //Replace bundle entity name by table name
 168                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 169
 170                 //Get result set mapping instance
 171                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 172                 $rsm = new ResultSetMapping();
 173
 174                 //Declare all fields
 175                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 176                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 177                 $rsm->addScalarResult('id', 'id', 'integer')
 178                         ->addScalarResult('pseudonym', 'pseudonym', 'string')
 179                         ->addScalarResult('g_id', 'g_id', 'integer')
 180                         ->addScalarResult('g_title', 'g_title', 'string')
 181                         ->addIndexByScalar('id');
 182
 183                 //Fetch result
 184                 $res = $em
 185                         ->createNativeQuery($req, $rsm)
 186                         ->getResult();
 187
 188                 //Init return
 189                 $ret = [];
 190
 191                 //Process result
 192                 foreach($res as $data) {
 193                         //Get translated group
 194                         $group = $translator->trans($data['g_title']?:'User');
 195
 196                         //Init group subarray
 197                         if (!isset($ret[$group])) {
 198                                 $ret[$group] = [];
 199                         }
 200
 201                         //Set data
 202                         $ret[$group][$data['id']] = $data['pseudonym'];
 203                 }
 204
 205                 //Send result
 206                 return $ret;
 207
 208                 header('Content-Type: text/plain');
 209                 var_dump($ret);
 210                 exit;
 211
 212                 //Get entity manager
 213                 $em = $this->getEntityManager();
 214
 215                 //Fetch sessions
 216                 $ret = $this->getEntityManager()
 217                         ->createQuery('SELECT u.id, u.pseudonym, g.title FROM RapsysAirBundle:User u JOIN RapsysAirBundle:GroupUser gu WITH gu.user = u.id JOIN RapsysAirBundle:Group g WITH g.id = gu.group GROUP BY u.id ORDER BY g.id DESC')
 218                         ->getResult();
 219
 220                 header('Content-Type: text/plain');
 221                 var_dump($ret);
 222                 exit;
 223
 224                 //Process result
 225                 $ret = array_column($ret, 'id', 'pseudonym');
 226
 227                 //Send result
 228                 return $ret;
 229         }
 230 }