]> Raphaël G. Git Repositories - airbundle/blob - Repository/DanceRepository.php
e7dd4753dd717e5922921d1c08e0560b87faeb96
[airbundle] / Repository / DanceRepository.php
1 <?php declare(strict_types=1);
2
3 /*
4 * This file is part of the Rapsys AirBundle package.
5 *
6 * (c) Raphaël Gertz <symfony@rapsys.eu>
7 *
8 * For the full copyright and license information, please view the LICENSE
9 * file that was distributed with this source code.
10 */
11
12 namespace Rapsys\AirBundle\Repository;
13
14 use Doctrine\ORM\AbstractQuery;
15 use Doctrine\ORM\Query\ResultSetMapping;
16
17 /**
18 * DanceRepository
19 */
20 class DanceRepository extends Repository {
21 /**
22 * Find dances indexed by id
23 *
24 * @return array The dances
25 */
26 public function findAllIndexed(): array {
27 //Set the request
28 $req = <<<SQL
29 SELECT
30 d.id,
31 d.name,
32 d.type
33 FROM RapsysAirBundle:Dance AS d
34 SQL;
35
36 //Replace bundle entity name by table name
37 $req = str_replace($this->tableKeys, $this->tableValues, $req);
38
39 //Get result set mapping instance
40 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
41 $rsm = new ResultSetMapping();
42
43 //Declare all fields
44 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
45 //addScalarResult($sqlColName, $resColName, $type = 'string');
46 $rsm->addEntityResult('RapsysAirBundle:Dance', 'd')
47 ->addFieldResult('d', 'id', 'id')
48 ->addFieldResult('d', 'name', 'name')
49 ->addFieldResult('d', 'type', 'type')
50 ->addIndexByColumn('d', 'id');
51
52 //Return return
53 return $this->_em
54 ->createNativeQuery($req, $rsm)
55 ->getResult();
56 }
57
58 /**
59 * Find dance choices as array
60 *
61 * @return array The dance choices
62 */
63 public function findChoicesAsArray(): array {
64 //Set the request
65 $req = <<<SQL
66 SELECT
67 d.name,
68 GROUP_CONCAT(d.id ORDER BY d.id SEPARATOR "\\n") AS ids,
69 GROUP_CONCAT(d.type ORDER BY d.id SEPARATOR "\\n") AS types
70 FROM RapsysAirBundle:Dance AS d
71 GROUP BY d.name
72 ORDER BY d.name
73 SQL;
74
75 //Replace bundle entity name by table name
76 $req = str_replace($this->tableKeys, $this->tableValues, $req);
77
78 //Get result set mapping instance
79 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
80 $rsm = new ResultSetMapping();
81
82 //Declare all fields
83 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
84 //addScalarResult($sqlColName, $resColName, $type = 'string');
85 $rsm->addScalarResult('name', 'name', 'string')
86 ->addScalarResult('ids', 'ids', 'string')
87 ->addScalarResult('types', 'types', 'string')
88 ->addIndexByScalar('name');
89
90 //Get result
91 $result = $this->_em
92 ->createNativeQuery($req, $rsm)
93 ->getArrayResult();
94
95 //Set return
96 $return = [];
97
98 //Iterate on each name
99 foreach($result as $name) {
100 //Set types
101 $types = [];
102
103 //Explode ids
104 $name['ids'] = explode("\n", $name['ids']);
105
106 //Explode types
107 $name['types'] = explode("\n", $name['types']);
108
109 //Iterate on each type
110 foreach($name['ids'] as $k => $id) {
111 //Add to types
112 $types[$this->translator->trans($name['types'][$k]).' ('.$id.')'] = intval($id);
113 }
114
115 //Add to return
116 $return[$this->translator->trans($name['name'])] = $types;
117 }
118
119 //Return return
120 return $return;
121 }
122
123 /**
124 * Find dances ids by nametype
125 *
126 * @param array $nametype The nametype filter
127 * @return array The dance ids
128 */
129 public function findIdByNameTypeAsArray(array $nametype): array {
130 //Set the request
131 $req = <<<SQL
132 SELECT
133 d.id
134 FROM RapsysAirBundle:Dance AS d
135 WHERE CONCAT_WS(' ', d.name, d.type) IN (:nametype)
136 ORDER BY d.name, d.type
137 SQL;
138
139 //Replace bundle entity name by table name
140 $req = str_replace($this->tableKeys, $this->tableValues, $req);
141
142 //Get result set mapping instance
143 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
144 $rsm = new ResultSetMapping();
145
146 //Declare all fields
147 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
148 //XXX: we don't use a result set as we want to translate group and civility
149 $rsm->addScalarResult('id', 'id', 'integer');
150
151 //Return result
152 return $this->_em
153 ->createNativeQuery($req, $rsm)
154 ->setParameter('nametype', $nametype)
155 //XXX: instead of array_column on the result
156 ->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN);
157 }
158
159 /**
160 * Find dance names as array
161 *
162 * @return array The dance names
163 */
164 public function findNamesAsArray(): array {
165 //Set the request
166 $req = <<<SQL
167 SELECT
168 d.name,
169 GROUP_CONCAT(d.id ORDER BY d.id SEPARATOR "\\n") AS ids,
170 GROUP_CONCAT(d.type ORDER BY d.id SEPARATOR "\\n") AS types,
171 MAX(d.updated) AS modified
172 FROM RapsysAirBundle:Dance AS d
173 GROUP BY d.name
174 ORDER BY d.name
175 SQL;
176
177 //Replace bundle entity name by table name
178 $req = str_replace($this->tableKeys, $this->tableValues, $req);
179
180 //Get result set mapping instance
181 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
182 $rsm = new ResultSetMapping();
183
184 //Declare all fields
185 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
186 //addScalarResult($sqlColName, $resColName, $type = 'string');
187 $rsm->addScalarResult('name', 'name', 'string')
188 ->addScalarResult('ids', 'ids', 'string')
189 ->addScalarResult('types', 'types', 'string')
190 ->addScalarResult('modified', 'modified', 'datetime')
191 ->addIndexByScalar('name');
192
193 //Get result
194 $result = $this->_em
195 ->createNativeQuery($req, $rsm)
196 ->getArrayResult();
197
198 //Set return
199 $return = [];
200
201 //Iterate on each name
202 foreach($result as $name) {
203 //Set name slug
204 $slug = $this->slugger->slug($tname = $this->translator->trans($name['name']));
205
206 //Set types
207 $types = [];
208
209 //Explode ids
210 $name['ids'] = explode("\n", $name['ids']);
211
212 //Explode types
213 $name['types'] = explode("\n", $name['types']);
214
215 //Iterate on each type
216 foreach($name['ids'] as $k => $id) {
217 //Add to types
218 $types[$this->slugger->short($name['types'][$k])] = [
219 'id' => $id,
220 'type' => $type = $this->translator->trans($name['types'][$k]),
221 'slug' => $stype = $this->slugger->slug($type),
222 'link' => $this->router->generate('rapsys_air_dance_view', ['id' => $id, 'name' => $slug, 'type' => $stype])
223 ];
224 }
225
226 //Add to return
227 $return[$sname = $this->slugger->short($name['name'])] = [
228 'name' => $tname,
229 'slug' => $slug,
230 'link' => $this->router->generate('rapsys_air_dance_name', ['name' => $sname, 'dance' => $slug]),
231 'types' => $types,
232 'modified' => $name['modified']
233 ];
234 }
235
236 //Return return
237 return $return;
238 }
239
240 /**
241 * Find dances by user id
242 *
243 * @param $id The user id
244 * @return array The user dances
245 */
246 public function findByUserId($userId): array {
247 //Set the request
248 $req = 'SELECT d.id, d.name, d.type
249 FROM RapsysAirBundle:UserDance AS ud
250 JOIN RapsysAirBundle:Dance AS d ON (d.id = ud.dance_id)
251 WHERE ud.user_id = :uid';
252
253 //Replace bundle entity name by table name
254 $req = str_replace($this->tableKeys, $this->tableValues, $req);
255
256 //Get result set mapping instance
257 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
258 $rsm = new ResultSetMapping();
259
260 //Declare result set for our request
261 $rsm->addEntityResult('RapsysAirBundle:Dance', 'd');
262 $rsm->addFieldResult('d', 'id', 'id');
263 $rsm->addFieldResult('d', 'name', 'name');
264 $rsm->addFieldResult('d', 'type', 'type');
265
266 //Send result
267 return $this->_em
268 ->createNativeQuery($req, $rsm)
269 ->setParameter('uid', $userId)
270 ->getResult();
271 }
272 }