]> Raphaƫl G. Git Repositories - airbundle/blob - Repository/UserRepository.php
Add user dance table
[airbundle] / 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 civility
14 *
15 * @param $translator The TranslatorInterface instance
16 */
17 public function findAllWithTranslatedGroupAndCivility(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:UserGroup' => $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:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp)
31 ];
32
33 //Set the request
34 $req = 'SELECT a.id, a.pseudonym, a.g_id, a.g_title FROM (
35 SELECT u.id, u.pseudonym, g.id AS g_id, g.title AS g_title
36 FROM RapsysAirBundle:User AS u
37 LEFT JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
38 LEFT JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
39 ORDER BY g.id DESC, NULL LIMIT '.PHP_INT_MAX.'
40 ) AS a GROUP BY a.id ORDER BY NULL';
41
42 //Replace bundle entity name by table name
43 $req = str_replace(array_keys($tables), array_values($tables), $req);
44
45 //Get result set mapping instance
46 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
47 $rsm = new ResultSetMapping();
48
49 //Declare all fields
50 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
51 //XXX: we don't use a result set as we want to translate group and civility
52 $rsm->addScalarResult('id', 'id', 'integer')
53 ->addScalarResult('pseudonym', 'pseudonym', 'string')
54 ->addScalarResult('g_id', 'g_id', 'integer')
55 ->addScalarResult('g_title', 'g_title', 'string')
56 ->addIndexByScalar('id');
57
58 //Fetch result
59 $res = $em
60 ->createNativeQuery($req, $rsm)
61 ->getResult();
62
63 //Init return
64 $ret = [];
65
66 //Process result
67 foreach($res as $data) {
68 //Without group or simple user
69 if (empty($data['g_title']) || $data['g_title'] == 'User') {
70 //Skip it
71 continue;
72 }
73 //Get translated group
74 $group = $translator->trans($data['g_title']);
75 //Init group subarray
76 if (!isset($ret[$group])) {
77 $ret[$group] = [];
78 }
79 //Set data
80 //XXX: ChoiceType use display string as key
81 $ret[$group][$data['pseudonym']] = $data['id'];
82 }
83
84 //Send result
85 return $ret;
86 }
87
88 /**
89 * Find all applicant by session
90 *
91 * @param $session The Session
92 */
93 public function findAllApplicantBySession($session) {
94 //Get entity manager
95 $em = $this->getEntityManager();
96
97 //Fetch sessions
98 $ret = $this->getEntityManager()
99 ->createQuery('SELECT u.id, u.pseudonym FROM RapsysAirBundle:Application a JOIN RapsysAirBundle:User u WITH u.id = a.user WHERE a.session = :session')
100 ->setParameter('session', $session)
101 ->getResult();
102
103 //Process result
104 $ret = array_column($ret, 'id', 'pseudonym');
105
106 //Send result
107 return $ret;
108 }
109
110 /**
111 * Find all users grouped by translated group
112 *
113 * @param $translator The TranslatorInterface instance
114 * @return array|null The user array or null
115 */
116 public function findUserGroupedByTranslatedGroup(TranslatorInterface $translator) {
117 //Get entity manager
118 $em = $this->getEntityManager();
119
120 //Get quote strategy
121 $qs = $em->getConfiguration()->getQuoteStrategy();
122 $dp = $em->getConnection()->getDatabasePlatform();
123
124 //Get quoted table names
125 //XXX: this allow to make this code table name independent
126 $tables = [
127 'RapsysAirBundle:UserGroup' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
128 'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
129 'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp),
130 //XXX: Set limit used to workaround mariadb subselect optimization
131 ':limit' => PHP_INT_MAX,
132 "\t" => '',
133 "\n" => ' '
134 ];
135
136 //Set the request
137 $req = <<<SQL
138 SELECT u.id, u.mail, u.pseudonym, g.id AS g_id, g.title AS g_title
139 FROM RapsysAirBundle:User AS u
140 JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
141 JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
142 ORDER BY g.id DESC, u.id ASC
143 SQL;
144
145 //Replace bundle entity name by table name
146 $req = str_replace(array_keys($tables), array_values($tables), $req);
147
148 //Get result set mapping instance
149 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
150 $rsm = new ResultSetMapping();
151
152 //Declare all fields
153 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
154 //addScalarResult($sqlColName, $resColName, $type = 'string');
155 $rsm->addScalarResult('id', 'id', 'integer')
156 ->addScalarResult('mail', 'mail', 'string')
157 ->addScalarResult('pseudonym', 'pseudonym', 'string')
158 ->addScalarResult('g_id', 'g_id', 'integer')
159 ->addScalarResult('g_title', 'g_title', 'string');
160
161 //Fetch result
162 $res = $em
163 ->createNativeQuery($req, $rsm)
164 ->getResult();
165
166 //Init return
167 $ret = [];
168
169 //Process result
170 foreach($res as $data) {
171 //Get translated group
172 $group = $translator->trans($data['g_title']);
173
174 //Init group subarray
175 if (!isset($ret[$group])) {
176 $ret[$group] = [];
177 }
178
179 //Set data
180 $ret[$group][$data['id']] = [
181 'mail' => $data['mail'],
182 'pseudonym' => $data['pseudonym']
183 ];
184 }
185
186 //Send result
187 return $ret;
188 }
189 }