]> Raphaël G. Git Repositories - treebundle/blob - Repository/AlbumRepository.php
Import repository base class
[treebundle] / Repository / AlbumRepository.php
1 <?php declare(strict_types=1);
2
3 /*
4 * This file is part of the Rapsys TreeBundle 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\TreeBundle\Repository;
13
14 use Doctrine\ORM\Query\ResultSetMapping;
15
16 use Rapsys\TreeBundle\Repository;
17
18 /**
19 * Album repository
20 */
21 class AlbumRepository extends Repository {
22 /**
23 * Find album count as int
24 *
25 * @param ?integer $id The user id
26 * @return integer The albums count
27 */
28 public function findCountAsInt(?int $id): int {
29 //Set user sql
30 $userSql = <<<SQL
31 a.user_id = :id
32 SQL;
33
34 //With null id
35 if ($id === null) {
36 //Set user sql
37 $userSql = <<<SQL
38 a.user_id IS NULL
39 SQL;
40 }
41
42 //Set the request
43 $req = <<<SQL
44 SELECT COUNT(DISTINCT a.album_id) AS count
45 FROM Rapsys\TreeBundle\Entity\Asset AS a
46 WHERE $userSql
47 SQL;
48
49 //Get result set mapping instance
50 $req = $this->replace($req);
51
52 //Get result set mapping instance
53 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
54 $rsm = new ResultSetMapping();
55
56 //Declare all fields
57 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
58 //addScalarResult($sqlColName, $resColName, $type = 'string');
59 $rsm->addScalarResult('count', 'count', 'integer');
60
61 //Get result
62 return $this->_em
63 ->createNativeQuery($req, $rsm)
64 ->setParameter('id', $id)
65 ->getSingleScalarResult();
66 }
67
68 /**
69 * Find albums as array
70 *
71 * @param ?integer $id The user id
72 * @param integer $page The page
73 * @param integer $count The count
74 * @return array The albums array
75 */
76 public function findAllAsArray(?int $id, int $page, int $count): array {
77 //Set user sql
78 $userSql = <<<SQL
79 s.user_id = :id
80 SQL;
81
82 //With null id
83 if ($id === null) {
84 //Set user sql
85 $userSql = <<<SQL
86 s.user_id IS NULL
87 SQL;
88 }
89
90 //Set the request
91 $req = <<<SQL
92 SELECT
93 a.id,
94 a.path,
95 GROUP_CONCAT(s.id ORDER BY s.id SEPARATOR "\\n") AS s_ids,
96 GROUP_CONCAT(IFNULL(s.path, '/') ORDER BY s.id SEPARATOR "\\n") AS s_paths,
97 a.slug,
98 GREATEST(a.created, s.created) AS created,
99 GREATEST(a.updated, s.updated) AS updated,
100 GREATEST(a.created, s.created, a.updated, s.updated) AS modified
101 FROM Rapsys\TreeBundle\Entity\Album AS a
102 JOIN Rapsys\TreeBundle\Entity\Asset AS s ON (s.album_id = a.id AND {$userSql})
103 GROUP BY a.id
104 ORDER BY updated, created DESC, a.id
105 LIMIT :offset, :count
106 SQL;
107
108 //Replace bundle entity name by table name
109 $req = $this->replace($req);
110
111 //Get result set mapping instance
112 $rsm = new ResultSetMapping();
113
114 //Declare all fields
115 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
116 //addScalarResult($sqlColName, $resColName, $type = 'string');
117 $rsm->addScalarResult('id', 'id', 'integer')
118 ->addScalarResult('path', 'path', 'string')
119 ->addScalarResult('slug', 'slug', 'string')
120 ->addScalarResult('s_ids', 's_ids', 'string')
121 ->addScalarResult('s_paths', 's_paths', 'string')
122 ->addScalarResult('created', 'created', 'datetime')
123 ->addScalarResult('updated', 'updated', 'datetime')
124 ->addScalarResult('modified', 'modified', 'datetime')
125 ->addIndexByScalar('id');
126
127 //Get result
128 $result = $this->_em
129 ->createNativeQuery($req, $rsm)
130 ->setParameter('offset', $page * $count)
131 ->setParameter('count', $count)
132 ->setParameter('id', $id)
133 ->getArrayResult();
134
135 //Set return
136 $return = [];
137
138 //Iterate on each city
139 foreach($result as $data) {
140 //Add to return
141 $return[$data['id']] = [
142 'id' => $id = $data['id'],
143 'slug' => $slug = $data['slug'],
144 'path' => $data['path'],
145 'created' => $data['created'],
146 'updated' => $data['updated'],
147 'modified' => $data['modified'],
148 'link' => $this->router->generate('rapsystree_album', ['id' => $id, 'slug' => $slug, 'path' => '/']),
149 'assets' => []
150 ];
151
152 //Explode asset ids
153 $data['s_ids'] = explode("\n", $data['s_ids']);
154
155 //Explode asset paths
156 $data['s_paths'] = explode("\n", $data['s_paths']);
157
158 foreach($data['s_ids'] as $s => $id) {
159 $return[$data['id']]['assets'][$id] = [
160 'id' => $id,
161 'path' => $path = $data['s_paths'][$s],
162 'link' => $this->router->generate('rapsystree_asset', ['id' => $id, 'path' => $path]),
163 ];
164 }
165 }
166
167 //Return return
168 return $return;
169 }
170 }