85 lines
2.3 KiB
PHP
85 lines
2.3 KiB
PHP
|
<?php
|
||
|
|
|
||
|
|
namespace Models;
|
||
|
|
|
||
|
|
use Core\Model;
|
||
|
|
use PDO;
|
||
|
|
|
||
|
|
class TagModel extends Model
|
||
|
|
{
|
||
|
|
public function createIfNotExists($tagName)
|
||
|
|
{
|
||
|
|
// Check if a tag exists
|
||
|
|
$stmt = $this->db->prepare("SELECT id FROM tags WHERE name = :name LIMIT 1");
|
||
|
|
$stmt->execute(['name' => $tagName]);
|
||
|
|
$tag = $stmt->fetch(PDO::FETCH_ASSOC);
|
||
|
|
|
||
|
|
if ($tag) {
|
||
|
|
return $tag['id'];
|
||
|
|
}
|
||
|
|
|
||
|
|
// If not, then create
|
||
|
|
$stmt = $this->db->prepare("INSERT INTO tags (name) VALUES (:name)");
|
||
|
|
$stmt->execute(['name' => $tagName]);
|
||
|
|
return $this->db->lastInsertId();
|
||
|
|
}
|
||
|
|
|
||
|
|
public function addTagToPost($postId, $tagId)
|
||
|
|
{
|
||
|
|
$stmt = $this->db->prepare("INSERT IGNORE INTO post_tags (post_id, tag_id) VALUES (:post_id, :tag_id)");
|
||
|
|
return $stmt->execute([
|
||
|
|
'post_id' => $postId,
|
||
|
|
'tag_id' => $tagId
|
||
|
|
]);
|
||
|
|
}
|
||
|
|
|
||
|
|
public function getTagsByPostId($postId)
|
||
|
|
{
|
||
|
|
$stmt = $this->db->prepare("
|
||
|
|
SELECT t.* FROM tags t
|
||
|
|
JOIN post_tags pt ON t.id = pt.tag_id
|
||
|
|
WHERE pt.post_id = :post_id
|
||
|
|
");
|
||
|
|
$stmt->execute(['post_id' => $postId]);
|
||
|
|
return $stmt->fetchAll(PDO::FETCH_ASSOC);
|
||
|
|
}
|
||
|
|
|
||
|
|
public function getPostsByTagName($tagName)
|
||
|
|
{
|
||
|
|
$stmt = $this->db->prepare("
|
||
|
|
SELECT p.*
|
||
|
|
FROM posts p
|
||
|
|
JOIN post_tags pt ON p.id = pt.post_id
|
||
|
|
JOIN tags t ON t.id = pt.tag_id
|
||
|
|
WHERE t.name = :tagName
|
||
|
|
ORDER BY p.created_at DESC
|
||
|
|
");
|
||
|
|
$stmt->execute(['tagName' => $tagName]);
|
||
|
|
return $stmt->fetchAll(PDO::FETCH_ASSOC);
|
||
|
|
}
|
||
|
|
|
||
|
|
public function removeAllTagsFromPost($postId)
|
||
|
|
{
|
||
|
|
$stmt = $this->db->prepare("DELETE FROM post_tags WHERE post_id = :post_id");
|
||
|
|
return $stmt->execute(['post_id' => $postId]);
|
||
|
|
}
|
||
|
|
|
||
|
|
public function getAllTagsWithCounts()
|
||
|
|
{
|
||
|
|
$sql = "
|
||
|
|
SELECT
|
||
|
|
t.id,
|
||
|
|
t.name,
|
||
|
|
COUNT(pt.post_id) AS total
|
||
|
|
FROM tags t
|
||
|
|
LEFT JOIN post_tags pt ON t.id = pt.tag_id
|
||
|
|
GROUP BY t.id, t.name
|
||
|
|
HAVING COUNT(pt.post_id) != 0
|
||
|
|
ORDER BY total DESC;
|
||
|
|
";
|
||
|
|
$stmt = $this->db->query($sql);
|
||
|
|
return $stmt->fetchAll(\PDO::FETCH_ASSOC);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|