Passed
Push — Showing-Posts ( 81c45d...58dd87 )
by Stone
02:10
created

PostModel::deletePost()   A

Complexity

Conditions 1
Paths 1

Size

Total Lines 9
Code Lines 5

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
cc 1
eloc 5
nc 1
nop 1
dl 0
loc 9
rs 10
c 0
b 0
f 0
1
<?php
2
3
namespace App\Models;
4
5
use Core\Constant;
6
use Core\Container;
7
use Core\Model;
8
use Core\Traits\StringFunctions;
9
use Exception;
10
11
class PostModel extends Model
12
{
13
    use StringFunctions;
14
15
    private $postsTbl;
16
    private $categoriesTbl;
17
    private $usersTbl;
18
    private $postTagTbl;
19
20
    //does our query need the tags table to be joined ?
21
    private $queryWithTags = false;
22
23
    public function __construct(Container $container)
24
    {
25
        parent::__construct($container);
26
        $this->postsTbl = $this->getTablePrefix("posts");
27
        $this->categoriesTbl = $this->getTablePrefix("categories");
28
        $this->usersTbl = $this->getTablePrefix("users");
29
        $this->postTagTbl = $this->getTablePrefix("posts_has_tags");
30
    }
31
32
    /**
33
     * the base Select SQL to get the information from the post table and joined tables
34
     * @param bool $withTags
35
     * @return string
36
     */
37
    private function basePostSelect(): string
38
    {
39
        $sql = "SELECT idposts, title, post_image,article,$this->postsTbl.last_update, posts_slug, categories_idcategories, category_name, published, on_front_page, categories_slug, pseudo as author, idusers
40
                FROM $this->postsTbl 
41
                INNER JOIN $this->categoriesTbl ON $this->postsTbl.categories_idcategories = $this->categoriesTbl.idcategories 
42
                INNER JOIN $this->usersTbl ON $this->postsTbl.author_iduser = $this->usersTbl.idusers";
43
        if ($this->queryWithTags) {
44
            $sql .= " LEFT JOIN $this->postTagTbl ON $this->postsTbl.idposts = $this->postTagTbl.post_idposts";
45
        }
46
        return $sql;
47
    }
48
49
    /**
50
     * add the excerpt to a post list
51
     * @param array $posts
52
     * @return array
53
     * @throws \ErrorException
54
     */
55
    private function addExcerpt(array $posts): array
56
    {
57
        $sendResults = [];
58
        //we create the excerpt for the text and add it to the object
59
        foreach ($posts as $post) {
60
            $post->{'excerpt'} = $this->getExcerpt($post->article);
61
            $sendResults[] = $post;
62
        }
63
        return $sendResults;
64
    }
65
66
    /**
67
     * get all posts, no restriction
68
     */
69
    private function getAllPosts(int $offset, int $limit)
70
    {
71
        $sql = $this->basePostSelect();
72
        $sql .= " ORDER BY $this->postsTbl.creation_date DESC";
73
        $sql .= " LIMIT :limit OFFSET :offset";
74
        $this->query($sql);
75
        $this->bind(":limit", $limit);
76
        $this->bind(":offset", $offset);
77
        $this->execute();
78
        $results = $this->fetchAll();
79
        return $this->addExcerpt($results);
80
    }
81
82
    /**
83
     * get all the posts with details. Only selecting posts that are published
84
     * @param int $offset where to start (for pagination)
85
     * @param int $limit the number of posts
86
     * @param bool $isFrontPage extract only front page posts
87
     * @param array $select list of select limiters
88
     * @param bool $withTags
89
     * @return array list of posts
90
     * @throws \ErrorException
91
     */
92
    private function getAllPublishedPosts(
93
        int $offset,
94
        int $limit,
95
        bool $isFrontPage = false,
96
        array $select = []
97
    ): array {
98
        $sql = $this->basePostSelect();
99
        $sql .= " WHERE published = 1";
100
        if ($isFrontPage) {
101
            $sql .= " AND on_front_page = 1";
102
        }
103
        //if we have a limiting parameter
104
        if ($select != null) {
105
            foreach ($select as $col => $val) {
106
                if (!$this->isAlphaNum($col)) {
107
                    throw new Exception("Invalid column name");
108
                }
109
                $sql .= " AND $col = :$col";
110
            }
111
        }
112
        $sql .= " ORDER BY $this->postsTbl.creation_date DESC";
113
        $sql .= " LIMIT :limit OFFSET :offset";
114
        $this->query($sql);
115
        if ($select != null) {
116
            foreach ($select as $col => $val) {
117
                $this->bind(":" . $col, $val);
118
            }
119
        }
120
        $this->bind(":limit", $limit);
121
        $this->bind(":offset", $offset);
122
        $this->execute();
123
        $results = $this->fetchAll();
124
        return $this->addExcerpt($results);
125
    }
126
127
    /**
128
     * Count the number of published posts
129
     * @param array $select list of select limiters
130
     * @param bool $withTags
131
     * @return int number of posts
132
     * @throws Exception
133
     */
134
    private function countNumberPosts(array $select = [], $published=true): int
135
    {
136
        $sql = "SELECT COUNT(*) FROM $this->postsTbl";
137
        if ($this->queryWithTags) {
138
            $sql .= " LEFT JOIN $this->postTagTbl ON $this->postsTbl.idposts = $this->postTagTbl.post_idposts";
139
        }
140
        if($published)
141
        {
142
            $sql .= " WHERE published = 1";
143
        }
144
        if ($select != null) {
145
            foreach ($select as $col => $val) {
146
                if (!$this->isAlphaNum($col)) {
147
                    throw new Exception("Invalid column name");
148
                }
149
                $sql .= " AND $col = :$col";
150
            }
151
        }
152
        $this->query($sql);
153
        if ($select != null) {
154
            foreach ($select as $col => $val) {
155
                $this->bind(":" . $col, $val);
156
            }
157
        }
158
        $this->execute();
159
        return $this->stmt->fetchColumn();
160
    }
161
162
    /**
163
     * get the total number of posts
164
     * @return int
165
     * @throws Exception
166
     */
167
    public function totalNumberPosts(): int
168
    {
169
        return $this->countNumberPosts();
170
    }
171
172
    /**
173
     * get the total number of posts + unpublished
174
     * @return int
175
     * @throws Exception
176
     */
177
    public function totalNumberFullPosts(): int
178
    {
179
        return $this->countNumberPosts([], false);
180
    }
181
182
    /**
183
     * get the total number of posts in a category
184
     * @param int $categoryId
185
     * @return int
186
     * @throws Exception
187
     */
188
    public function totalNumberPostsInCategory(int $categoryId): int
189
    {
190
        return $this->countNumberPosts(["categories_idcategories" => $categoryId]);
191
    }
192
193
    /**
194
     * get the total number of posts by an author
195
     * @param int $authorId
196
     * @return int
197
     * @throws Exception
198
     */
199
    public function totalNumberPostsByAuthor(int $authorId): int
200
    {
201
        return $this->countNumberPosts(["author_iduser" => $authorId]);
202
    }
203
204
    /**
205
     * get the total number of posts with tag
206
     * @param int $tagId
207
     * @return int
208
     * @throws Exception
209
     */
210
    public function totalNumberPostsByTag(int $tagId): int
211
    {
212
        $this->queryWithTags = true;
213
        return $this->countNumberPosts(["tag_idtags" => $tagId]);
214
    }
215
216
    /**
217
     * get the list of front posts
218
     * @param int $offset
219
     * @param int $limit
220
     * @return array
221
     * @throws \ErrorException
222
     */
223
    public function getFrontPosts(int $offset = 0, int $limit = Constant::FRONT_PAGE_POSTS): array
224
    {
225
        return $this->getAllPublishedPosts($offset, $limit, true);
226
    }
227
228
    /**
229
 * get the list of all the posts.
230
 * @param int $offset
231
 * @param array $select array of limiters [$key => $val] will convert to "where $key = $val"
232
 * @param int $limit
233
 * @return array
234
 * @throws \ErrorException
235
 */
236
    public function getPosts(int $offset = 0, array $select = [], int $limit = Constant::POSTS_PER_PAGE): array
237
    {
238
        return $this->getAllPublishedPosts($offset, $limit, false, $select);
239
    }
240
241
    /**
242
     *
243
     */
244
    public function getFullPosts(int $offset = 0, int $limit = Constant::POSTS_PER_PAGE): array
245
    {
246
        return $this->getAllPosts($offset, $limit);
247
    }
248
249
250
    /**
251
     * get all the posts from a certain category
252
     * @param int $categoryId the id of the category
253
     * @param int $offset the offset for pagination
254
     * @param int $limit the limit to display
255
     * @return array list of posts in set category
256
     * @throws Exception
257
     */
258
    public function getPostsInCategory(int $categoryId, int $offset = 0, int $limit = Constant::POSTS_PER_PAGE): array
259
    {
260
        return $this->getPosts($offset, ["categories_idcategories" => $categoryId], $limit);
261
    }
262
263
    /**
264
     * get all the posts with a specific author
265
     * @param int $authorId
266
     * @param int $offset
267
     * @param int $limit
268
     * @return array
269
     * @throws \ErrorException
270
     */
271
    public function getPostsWithAuthor(int $authorId, int $offset = 0, int $limit = Constant::POSTS_PER_PAGE): array
272
    {
273
        return $this->getPosts($offset, ["author_iduser" => $authorId], $limit);
274
    }
275
276
    /**
277
     * get all the posts with a certain tag
278
     * @param int $tagId
279
     * @param int $offset
280
     * @param int $limit
281
     * @return array
282
     * @throws \ErrorException
283
     */
284
    public function getPostsWithTag(int $tagId, int $offset = 0, int $limit = Constant::POSTS_PER_PAGE): array
285
    {
286
        $this->queryWithTags = true;
287
        return $this->getPosts($offset, ["tag_idtags" => $tagId], $limit);
288
    }
289
290
    /**
291
     * get a single post from it's ID
292
     * @param int $postid the post ID to get
293
     * @return array the single post details
294
     * @throws Exception
295
     */
296
    public function getSinglePost(int $postid)
297
    {
298
        $sql = $this->basePostSelect();
299
        $sql .= " WHERE idposts = :postId;";
300
        $this->query($sql);
301
        $this->bind(":postId", $postid, \PDO::PARAM_INT);
302
        $this->execute();
303
304
        return $this->fetch();
305
    }
306
307
    /**
308
     * Create a new post
309
     * @param string $title
310
     * @param string $postImage
311
     * @param int $idCategory
312
     * @param string $article
313
     * @param int $idUser
314
     * @param int $published
315
     * @param int $onFrontPage
316
     * @param string $postSlug
317
     * @return int the id of created post
318
     * @throws Exception
319
     */
320
    public function newPost(
321
        string $title,
322
        string $postImage,
323
        int $idCategory,
324
        string $article,
325
        int $idUser,
326
        int $published,
327
        int $onFrontPage,
328
        string $postSlug
329
    ): int {
330
        $sql = "
331
          INSERT INTO $this->postsTbl (title, post_image, categories_idcategories, article, author_iduser, creation_date, last_update, published, on_front_page, posts_slug)
332
          VALUES (:title, :post_image, :categories_idcategories, :article, :author_iduser, NOW(), NOW(), :published, :on_front_page, :posts_slug)
333
        ";
334
        $this->query($sql);
335
        $this->bind(':title', $title);
336
        $this->bind(':post_image', $postImage);
337
        $this->bind(':categories_idcategories', $idCategory);
338
        $this->bind(':article', $article);
339
        $this->bind(':author_iduser', $idUser);
340
        $this->bind(':published', $published);
341
        $this->bind(':on_front_page', $onFrontPage);
342
        $this->bind(':posts_slug', $postSlug);
343
344
        $this->execute();
345
346
        return (int)$this->dbh->lastInsertId();
347
    }
348
349
    /**
350
     * Update a post with new values
351
     * @param int $postId
352
     * @param string $title
353
     * @param string $postImage
354
     * @param int $idCategory
355
     * @param string $article
356
     * @param int $published
357
     * @param int $onFrontPage
358
     * @param string $postSlug
359
     * @return bool success
360
     * @throws Exception
361
     */
362
    public function modifyPost(
363
        int $postId,
364
        string $title,
365
        string $postImage,
366
        int $idCategory,
367
        string $article,
368
        int $published,
369
        int $onFrontPage,
370
        string $postSlug
371
    ): bool {
372
        $sql = "
373
            UPDATE $this->postsTbl 
374
            SET 
375
                title = :title,
376
                post_image = :postImage,
377
                categories_idcategories = :idCategory,
378
                article = :article,
379
                last_update = NOW(),
380
                published = :published,
381
                on_front_page = :onFrontPage,
382
                posts_slug = :postSlug
383
            WHERE
384
              idposts = :postId
385
        ;";
386
        $this->query($sql);
387
        $this->bind(":title", $title);
388
        $this->bind(":postImage", $postImage);
389
        $this->bind(":idCategory", $idCategory);
390
        $this->bind(":article", $article);
391
        $this->bind(":published", $published);
392
        $this->bind(":onFrontPage", $onFrontPage);
393
        $this->bind(":postSlug", $postSlug);
394
        $this->bind(":postId", $postId);
395
396
        return $this->execute();
397
    }
398
399
    /**
400
     * Removes a post from the DataBase
401
     * @param int $postId
402
     * @return bool
403
     * @throws Exception
404
     */
405
    public function deletePost(int $postId):bool
406
    {
407
        $sql = "
408
        DELETE FROM $this->postsTbl 
409
        WHERE idposts = :postId
410
        ";
411
        $this->query($sql);
412
        $this->bind(":postId", $postId);
413
        return $this->execute();
414
    }
415
416
417
    /**
418
     * get the post title from ID
419
     * @param int $postId
420
     * @return string
421
     * @throws Exception
422
     */
423
    public function getTitleFromId(int $postId):string
424
    {
425
        $sql = "SELECT title from $this->postsTbl WHERE idposts = :postId";
426
        $this->query($sql);
427
        $this->bind(":postId", $postId);
428
        $this->execute();
429
        return $this->stmt->fetchColumn();
430
    }
431
432
433
}