1 <?php
declare(strict_types
=1);
4 * This file is part of the Rapsys BlogBundle 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\BlogBundle\Repository
;
14 use Doctrine\ORM\Query\ResultSetMapping
;
19 * This class was generated by the Doctrine ORM. Add your own custom
20 * repository methods below.
22 class ArticleRepository
extends EntityRepository
{
24 * Find article count as int
26 * @return integer The articles count
28 public function findCountAsInt(): int {
31 SELECT COUNT(at.article_id) AS count
32 FROM RapsysBlogBundle:ArticleTranslation AS at
33 WHERE at.locale = :locale
36 //Get result set mapping instance
37 $req = $this->replace($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->addScalarResult('count', 'count', 'integer');
50 ->createNativeQuery($req, $rsm)
51 ->getSingleScalarResult();
55 * Find articles as array
57 * @param integer $page The page
58 * @param integer $count The count
59 * @return array The articles array
61 public function findAllAsArray(int $page, int $count): array {
66 GREATEST(a.created, at.created, k.created, kt.created) AS created,
67 GREATEST(a.updated, at.updated, k.updated, kt.updated) AS updated,
68 GREATEST(a.created, at.created, k.created, kt.created, a.updated, at.updated, k.updated, kt.updated) as modified,
73 GROUP_CONCAT(k.id ORDER BY k.id SEPARATOR "\\n") AS k_ids,
74 GROUP_CONCAT(kt.slug ORDER BY k.id SEPARATOR "\\n") AS k_slugs,
75 GROUP_CONCAT(kt.title ORDER BY k.id SEPARATOR "\\n") AS k_titles
76 FROM RapsysBlogBundle:Article AS a
77 JOIN RapsysBlogBundle:ArticleTranslation AS at ON (at.article_id = a.id AND at.locale = :locale)
78 LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak ON (ak.article_id = a.id)
79 LEFT JOIN RapsysBlogBundle:Keyword AS k ON (k.id = ak.keyword_id)
80 LEFT JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = k.id AND kt.locale = :locale)
82 ORDER BY updated, created DESC
86 //Replace bundle entity name by table name
87 $req = $this->replace($req);
89 //Get result set mapping instance
90 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
91 $rsm = new ResultSetMapping();
94 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
95 //addScalarResult($sqlColName, $resColName, $type = 'string');
96 $rsm->addScalarResult('id', 'id', 'integer')
97 ->addScalarResult('body', 'body', 'string')
98 ->addScalarResult('description', 'description', 'string')
99 ->addScalarResult('slug', 'slug', 'string')
100 ->addScalarResult('title', 'title', 'string')
101 ->addScalarResult('k_ids', 'k_ids', 'string')
102 ->addScalarResult('k_slugs', 'k_slugs', 'string')
103 ->addScalarResult('k_titles', 'k_titles', 'string')
104 ->addScalarResult('created', 'created', 'datetime')
105 ->addScalarResult('updated', 'updated', 'datetime')
106 ->addScalarResult('modified', 'modified', 'datetime')
107 ->addIndexByScalar('id');
111 ->createNativeQuery($req, $rsm)
112 ->setParameter('offset', $page * $count)
113 ->setParameter('count', $count)
119 //Iterate on each city
120 foreach($result as $data) {
122 $return[$data['id']] = [
123 'id' => $id = $data['id'],
124 'body' => $data['body'],
125 'description' => $data['description'],
126 'slug' => $slug = $data['slug'],
127 'title' => $data['title'],
128 'created' => $data['created'],
129 'updated' => $data['updated'],
130 'modified' => $data['modified'],
131 'link' => $this->router
->generate('rapsys_blog_article_view', ['id' => $id, 'slug' => $slug]),
135 //Explode keyword ids
136 $data['k_ids'] = explode("\n", $data['k_ids']);
138 //Explode keyword slugs
139 $data['k_slugs'] = explode("\n", $data['k_slugs']);
141 //Explode keyword titles
142 $data['k_titles'] = explode("\n", $data['k_titles']);
144 foreach($data['k_ids'] as $k => $id) {
145 $return[$data['id']]['keywords'][$id] = [
147 'slug' => $slug = $data['k_slugs'][$k],
148 'title' => $data['k_titles'][$k],
149 'link' => $this->router
->generate('rapsys_blog_keyword_view', ['id' => $id, 'slug' => $slug]),
159 * Find article by id as array
161 * @param integer $id The article id
162 * @return array The article array
164 public function findByIdAsArray(int $id): array {
179 GROUP_CONCAT(kat.locale ORDER BY kat.locale SEPARATOR "\\n") AS kat_locales,
180 GROUP_CONCAT(kat.slug ORDER BY kat.locale SEPARATOR "\\n") AS kat_slugs,
181 GROUP_CONCAT(kat.title ORDER BY kat.locale SEPARATOR "\\n") AS kat_titles
185 GREATEST(a.created, at.created, k.created, kt.created) AS created,
186 GREATEST(a.updated, at.updated, k.updated, kt.updated) AS updated,
187 GREATEST(a.created, at.created, k.created, kt.created, a.updated, at.updated, k.updated, kt.updated) AS modified,
192 GROUP_CONCAT(k.id ORDER BY k.id SEPARATOR "\\n") AS k_ids,
193 GROUP_CONCAT(kt.slug ORDER BY k.id SEPARATOR "\\n") AS k_slugs,
194 GROUP_CONCAT(kt.title ORDER BY k.id SEPARATOR "\\n") AS k_titles
195 FROM RapsysBlogBundle:Article AS a
196 JOIN RapsysBlogBundle:ArticleTranslation AS at ON (at.article_id = a.id AND at.locale = :locale)
197 LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak ON (ak.article_id = a.id)
198 LEFT JOIN RapsysBlogBundle:Keyword AS k ON (k.id = ak.keyword_id)
199 LEFT JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = k.id AND kt.locale = :locale)
205 LEFT JOIN RapsysBlogBundle:ArticleTranslation AS kat ON (kat.article_id = b.id AND kat.locale <> :locale)
209 //Replace bundle entity name by table name
210 $req = $this->replace($req);
212 //Get result set mapping instance
213 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
214 $rsm = new ResultSetMapping();
217 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
218 //addScalarResult($sqlColName, $resColName, $type = 'string');
219 $rsm->addScalarResult('id', 'id', 'integer')
220 ->addScalarResult('body', 'body', 'string')
221 ->addScalarResult('description', 'description', 'string')
222 ->addScalarResult('slug', 'slug', 'string')
223 ->addScalarResult('title', 'title', 'string')
224 ->addScalarResult('k_ids', 'k_ids', 'string')
225 ->addScalarResult('k_slugs', 'k_slugs', 'string')
226 ->addScalarResult('k_titles', 'k_titles', 'string')
227 ->addScalarResult('kat_locales', 'kat_locales', 'string')
228 ->addScalarResult('kat_slugs', 'kat_slugs', 'string')
229 ->addScalarResult('kat_titles', 'kat_titles', 'string')
230 ->addScalarResult('created', 'created', 'datetime')
231 ->addScalarResult('updated', 'updated', 'datetime')
232 ->addScalarResult('modified', 'modified', 'datetime');
236 ->createNativeQuery($req, $rsm)
237 ->setParameter('id', $id)
238 ->getOneOrNullResult();
242 'id' => $id = $data['id'],
243 'body' => $data['body'],
244 'description' => $data['description'],
245 'slug' => $slug = $data['slug'],
246 'title' => $data['title'],
247 'created' => $data['created'],
248 'updated' => $data['updated'],
249 'modified' => $data['modified'],
250 'link' => $this->router
->generate('rapsys_blog_article_view', ['id' => $id, 'slug' => $slug]),
255 //Explode keyword ids
256 $data['k_ids'] = explode("\n", $data['k_ids']);
258 //Explode keyword slugs
259 $data['k_slugs'] = explode("\n", $data['k_slugs']);
261 //Explode keyword titles
262 $data['k_titles'] = explode("\n", $data['k_titles']);
264 foreach($data['k_ids'] as $k => $kid) {
265 $return['keywords'][$kid] = [
267 'slug' => $slug = $data['k_slugs'][$k],
268 'title' => $data['k_titles'][$k],
269 'link' => $this->router
->generate('rapsys_blog_keyword_view', ['id' => $kid, 'slug' => $slug]),
273 //Explode alternate locales
274 $data['kat_locales'] = explode("\n", $data['kat_locales']);
276 //Explode alternate slugs
277 $data['kat_slugs'] = explode("\n", $data['kat_slugs']);
279 //Explode alternate titles
280 $data['kat_titles'] = explode("\n", $data['kat_titles']);
282 foreach($data['kat_locales'] as $k => $locale) {
283 $return['alternates'][$locale] = [
285 'slug' => $slug = $data['kat_slugs'][$k],
286 'title' => $data['kat_titles'][$k],
287 'link' => $this->router
->generate('rapsys_blog_article_view', ['_locale' => $locale, 'id' => $id, 'slug' => $slug]),