1 <?php
declare(strict_types
=1);
4 * This file is part of the Rapsys AirBundle package.
6 * (c) Raphaël Gertz <symfony@rapsys.eu>
8 * For the full copyright and license information, please view the LICENSE
9 * file that was distributed with this source code.
12 namespace Rapsys\AirBundle\Repository
;
14 use Doctrine\ORM\AbstractQuery
;
15 use Doctrine\ORM\Query\ResultSetMapping
;
20 class DanceRepository
extends Repository
{
22 * Find dances indexed by id
24 * @return array The dances
26 public function findAllIndexed(): array {
33 FROM RapsysAirBundle:Dance AS d
36 //Replace bundle entity name by table name
37 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
39 //Get result set mapping instance
40 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
41 $rsm = new ResultSetMapping();
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');
54 ->createNativeQuery($req, $rsm)
59 * Find dance choices as array
61 * @return array The dance choices
63 public function findChoicesAsArray(): array {
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
75 //Replace bundle entity name by table name
76 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
78 //Get result set mapping instance
79 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
80 $rsm = new ResultSetMapping();
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');
92 ->createNativeQuery($req, $rsm)
98 //Iterate on each name
99 foreach($result as $name) {
104 $name['ids'] = explode("\n", $name['ids']);
107 $name['types'] = explode("\n", $name['types']);
109 //Iterate on each type
110 foreach($name['ids'] as $k => $id) {
112 $types[$this->translator
->trans($name['types'][$k]).' ('.$id.')'] = intval($id);
116 $return[$this->translator
->trans($name['name'])] = $types;
124 * Find dances ids by nametype
126 * @param array $nametype The nametype filter
127 * @return array The dance ids
129 public function findIdByNameTypeAsArray(array $nametype): array {
134 FROM RapsysAirBundle:Dance AS d
135 WHERE CONCAT_WS(' ', d.name, d.type) IN (:nametype)
136 ORDER BY d.name, d.type
139 //Replace bundle entity name by table name
140 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
142 //Get result set mapping instance
143 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
144 $rsm = new ResultSetMapping();
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');
153 ->createNativeQuery($req, $rsm)
154 ->setParameter('nametype', $nametype)
155 //XXX: instead of array_column on the result
156 ->getResult(AbstractQuery
::HYDRATE_SCALAR_COLUMN
);
160 * Find dance names as array
162 * @return array The dance names
164 public function findNamesAsArray(): array {
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
177 //Replace bundle entity name by table name
178 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
180 //Get result set mapping instance
181 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
182 $rsm = new ResultSetMapping();
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');
195 ->createNativeQuery($req, $rsm)
201 //Iterate on each name
202 foreach($result as $name) {
204 $slug = $this->slugger
->slug($tname = $this->translator
->trans($name['name']));
210 $name['ids'] = explode("\n", $name['ids']);
213 $name['types'] = explode("\n", $name['types']);
215 //Iterate on each type
216 foreach($name['ids'] as $k => $id) {
218 $types[$this->slugger
->short($name['types'][$k])] = [
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])
227 $return[$sname = $this->slugger
->short($name['name'])] = [
230 'link' => $this->router
->generate('rapsys_air_dance_name', ['name' => $sname, 'dance' => $slug]),
232 'modified' => $name['modified']
241 * Find dances by user id
243 * @param $id The user id
244 * @return array The user dances
246 public function findByUserId($userId): array {
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';
253 //Replace bundle entity name by table name
254 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
256 //Get result set mapping instance
257 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
258 $rsm = new ResultSetMapping();
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');
268 ->createNativeQuery($req, $rsm)
269 ->setParameter('uid', $userId)