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
;
17 use Rapsys\AirBundle\Repository
;
22 class DanceRepository
extends Repository
{
24 * Find dances indexed by id
26 * @return array The dances
28 public function findAllIndexed(): array {
35 FROM RapsysAirBundle:Dance AS d
38 //Replace bundle entity name by table name
39 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
41 //Get result set mapping instance
42 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
43 $rsm = new ResultSetMapping();
46 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
47 //addScalarResult($sqlColName, $resColName, $type = 'string');
48 $rsm->addEntityResult('RapsysAirBundle:Dance', 'd')
49 ->addFieldResult('d', 'id', 'id')
50 ->addFieldResult('d', 'name', 'name')
51 ->addFieldResult('d', 'type', 'type')
52 ->addIndexByColumn('d', 'id');
56 ->createNativeQuery($req, $rsm)
61 * Find dance choices as array
63 * @return array The dance choices
65 public function findChoicesAsArray(): array {
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 RapsysAirBundle:Dance AS d
77 //Replace bundle entity name by table name
78 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
80 //Get result set mapping instance
81 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
82 $rsm = new ResultSetMapping();
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');
94 ->createNativeQuery($req, $rsm)
100 //Iterate on each name
101 foreach($result as $name) {
106 $name['ids'] = explode("\n", $name['ids']);
109 $name['types'] = explode("\n", $name['types']);
111 //Iterate on each type
112 foreach($name['ids'] as $k => $id) {
114 $types[$this->translator
->trans($name['types'][$k]).' ('.$id.')'] = intval($id);
118 $return[$this->translator
->trans($name['name'])] = $types;
126 * Find dances ids by nametype
128 * @param array $nametype The nametype filter
129 * @return array The dance ids
131 public function findIdByNameTypeAsArray(array $nametype): array {
136 FROM RapsysAirBundle:Dance AS d
137 WHERE CONCAT_WS(' ', d.name, d.type) IN (:nametype)
138 ORDER BY d.name, d.type
141 //Replace bundle entity name by table name
142 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
144 //Get result set mapping instance
145 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
146 $rsm = new ResultSetMapping();
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');
155 ->createNativeQuery($req, $rsm)
156 ->setParameter('nametype', $nametype)
157 //XXX: instead of array_column on the result
158 ->getResult(AbstractQuery
::HYDRATE_SCALAR_COLUMN
);
162 * Find dance names as array
164 * @return array The dance names
166 public function findNamesAsArray(): array {
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 RapsysAirBundle:Dance AS d
179 //Replace bundle entity name by table name
180 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
182 //Get result set mapping instance
183 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
184 $rsm = new ResultSetMapping();
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');
197 ->createNativeQuery($req, $rsm)
203 //Iterate on each name
204 foreach($result as $name) {
206 $slug = $this->slugger
->slug($tname = $this->translator
->trans($name['name']));
212 $name['ids'] = explode("\n", $name['ids']);
215 $name['types'] = explode("\n", $name['types']);
217 //Iterate on each type
218 foreach($name['ids'] as $k => $id) {
220 $types[$this->slugger
->short($name['types'][$k])] = [
222 'type' => $type = $this->translator
->trans($name['types'][$k]),
223 'slug' => $stype = $this->slugger
->slug($type),
224 'link' => $this->router
->generate('rapsys_air_dance_view', ['id' => $id, 'name' => $slug, 'type' => $stype])
229 $return[$sname = $this->slugger
->short($name['name'])] = [
232 'link' => $this->router
->generate('rapsys_air_dance_name', ['name' => $sname, 'dance' => $slug]),
234 'modified' => $name['modified']
243 * Find dances by user id
245 * @param $id The user id
246 * @return array The user dances
248 public function findByUserId($userId): array {
250 $req = 'SELECT d.id, d.name, d.type
251 FROM RapsysAirBundle:UserDance AS ud
252 JOIN RapsysAirBundle:Dance AS d ON (d.id = ud.dance_id)
253 WHERE ud.user_id = :uid';
255 //Replace bundle entity name by table name
256 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
258 //Get result set mapping instance
259 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
260 $rsm = new ResultSetMapping();
262 //Declare result set for our request
263 $rsm->addEntityResult('RapsysAirBundle:Dance', 'd');
264 $rsm->addFieldResult('d', 'id', 'id');
265 $rsm->addFieldResult('d', 'name', 'name');
266 $rsm->addFieldResult('d', 'type', 'type');
270 ->createNativeQuery($req, $rsm)
271 ->setParameter('uid', $userId)