]> Raphaël G. Git Repositories - airbundle/blob - Repository/DanceRepository.php
Add user dance table
[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\Query\ResultSetMapping;
15
16 /**
17 * DanceRepository
18 */
19 class DanceRepository extends EntityRepository {
20 /**
21 * Find dance names as array
22 *
23 * @return array The dance names
24 */
25 public function findNamesAsArray() {
26 //Set the request
27 $req = <<<SQL
28 SELECT
29 d.name,
30 GROUP_CONCAT(d.id ORDER BY d.id SEPARATOR "\\n") AS ids,
31 GROUP_CONCAT(d.type ORDER BY d.id SEPARATOR "\\n") AS types,
32 MAX(d.updated) AS modified
33 FROM RapsysAirBundle:Dance AS d
34 GROUP BY d.name
35 ORDER BY d.name
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->addScalarResult('name', 'name', 'string')
49 ->addScalarResult('ids', 'ids', 'string')
50 ->addScalarResult('types', 'types', 'string')
51 ->addScalarResult('modified', 'modified', 'datetime')
52 ->addIndexByScalar('name');
53
54 //Get result
55 $result = $this->_em
56 ->createNativeQuery($req, $rsm)
57 ->getArrayResult();
58
59 //Set return
60 $return = [];
61
62 //Iterate on each name
63 foreach($result as $name) {
64 //Set name slug
65 $slug = $this->slugger->slug($tname = $this->translator->trans($name['name']));
66
67 //Set types
68 $types = [];
69
70 //Explode ids
71 $name['ids'] = explode("\n", $name['ids']);
72
73 //Explode types
74 $name['types'] = explode("\n", $name['types']);
75
76 //Iterate on each type
77 foreach($name['ids'] as $k => $id) {
78 //Add to types
79 $types[$this->slugger->short($name['types'][$k])] = [
80 'id' => $id,
81 'type' => $type = $this->translator->trans($name['types'][$k]),
82 'slug' => $stype = $this->slugger->slug($type),
83 'link' => $this->router->generate('rapsys_air_dance_view', ['id' => $id, 'name' => $slug, 'type' => $stype])
84 ];
85 }
86
87 //Add to return
88 $return[$sname = $this->slugger->short($name['name'])] = [
89 'name' => $tname,
90 'slug' => $slug,
91 'link' => $this->router->generate('rapsys_air_dance_name', ['name' => $sname, 'dance' => $slug]),
92 'types' => $types,
93 'modified' => $name['modified']
94 ];
95 }
96
97 //Return return
98 return $return;
99 }
100
101 /**
102 * Find dances by user id
103 *
104 * @param $id The user id
105 * @return array The user dances
106 */
107 public function findByUserId($userId) {
108 //Set the request
109 $req = 'SELECT d.id, d.name, d.type
110 FROM RapsysAirBundle:UserDance AS ud
111 JOIN RapsysAirBundle:Dance AS d ON (d.id = ud.dance_id)
112 WHERE ud.user_id = :uid';
113
114 //Replace bundle entity name by table name
115 $req = str_replace($this->tableKeys, $this->tableValues, $req);
116
117 //Get result set mapping instance
118 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
119 $rsm = new ResultSetMapping();
120
121 //Declare result set for our request
122 $rsm->addEntityResult('RapsysAirBundle:Dance', 'd');
123 $rsm->addFieldResult('d', 'id', 'id');
124 $rsm->addFieldResult('d', 'name', 'name');
125 $rsm->addFieldResult('d', 'type', 'type');
126
127 //Send result
128 return $this->_em
129 ->createNativeQuery($req, $rsm)
130 ->setParameter('uid', $userId)
131 ->getResult();
132 }
133 }