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