1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
<?php
namespace ORM\Repositories;
use ORM\Repository;
use ORM\EntityInterface;
use ORM\Entities\Category;
class CategoryRepository extends Repository {
public static function getTableName(): string { return 'category'; }
public static function getClassName(): string { return 'ORM\Entities\Category'; }
#===============================================================================
# Get number of *posts* assigned to $Category
#===============================================================================
public function getNumberOfPosts(Category $Category): int {
$query = 'SELECT COUNT(id) FROM %s WHERE category = ?';
$query = sprintf($query, PostRepository::getTableName());
$Statement = $this->Database->prepare($query);
$Statement->execute([$Category->getID()]);
return $Statement->fetchColumn();
}
#===============================================================================
# Find category with parents based on primary key
#===============================================================================
public function findWithParents($id): array {
return $this->findWithParentsBy('id', $id);
}
#===============================================================================
# Find category with parents based on specific field comparison
#===============================================================================
public function findWithParentsBy(string $field, $value): array {
$query = 'WITH RECURSIVE tree AS (
SELECT *, 0 AS _depth FROM %s WHERE %s %s UNION
SELECT c.*, _depth+1 FROM %s c, tree WHERE tree.parent = c.id
) SELECT * FROM tree ORDER BY _depth DESC';
$table = static::getTableName();
$check = is_null($value) ? 'IS NULL': '= ?';
$query = sprintf($query, $table, $field, $check, $table);
$Statement = $this->Database->prepare($query);
$Statement->execute([$value]);
# TODO: Virtual column _depth shall not be fetched into the entity class
return $this->fetchEntities($Statement);
}
#===============================================================================
# Get paginated category tree list
#===============================================================================
public function getPaginatedTree(int $limit, int $offset = 0): array {
$query = 'WITH RECURSIVE tree AS (
SELECT *, name AS _depth FROM %s WHERE parent IS NULL UNION
SELECT c.*, CONCAT(_depth, "/", c.name) AS _depth FROM %s c INNER JOIN tree ON tree.id = c.parent
) SELECT * FROM tree ORDER BY _depth %s';
$_limit = "LIMIT $limit";
if($offset) {
$_limit = "LIMIT $offset,$limit";
}
$table = static::getTableName();
$query = sprintf($query, $table, $table, $_limit);
$Statement = $this->Database->prepare($query);
$Statement->execute();
return $this->fetchEntities($Statement);
}
#===============================================================================
# Get number of children categories assigned to $Category
#===============================================================================
public function getNumberOfChildren(Category $Category): int {
$query = 'WITH RECURSIVE tree AS (
SELECT * FROM %s WHERE id = ? UNION
SELECT c.* FROM %s c, tree WHERE tree.id = c.parent
) SELECT COUNT(id) FROM tree WHERE id != ?';
$query = sprintf($query,
static::getTableName(),
static::getTableName()
);
$Statement = $this->Database->prepare($query);
$Statement->execute([$Category->getID(), $Category->getID()]);
return $Statement->fetchColumn();
}
#===============================================================================
# Update category (and check for parent/child circular loops)
#===============================================================================
public function update(EntityInterface $Entity): bool {
# Entity parent might have changed *in memory*, so we re-fetch the original
# parent of the entity from the database and save it in a variable.
# TODO: Repository/Entity class should have a mechanism to detect changes!
$query = 'SELECT parent FROM %s WHERE id = ?';
$query = sprintf($query, static::getTableName());
$Statement = $this->Database->prepare($query);
$Statement->execute([$Entity->getID()]);
$parent = $Statement->fetchColumn();
# If parent is unchanged, circular loop check is not needed.
if($parent === $Entity->get('parent')) {
return parent::update($Entity);
}
$_parent = $Entity->get('parent');
# Fetch the parent of the *new* parent category and let the while loop run through
# the tree until either a parent of "NULL" was found or if the new parent category
# is a *child* of the *current* category which would cause a circular loop.
while($Statement->execute([$_parent]) && $_parent = $Statement->fetchColumn()) {
if($_parent == $Entity->get('id')) {
# Set parent of the *new* parent category to the *original* parent category
# of the *current* category (one level up) to prevent a circular loop.
$query = 'UPDATE %s SET parent = ? WHERE id = ?';
$query = sprintf($query, static::getTableName());
$UpdateStatement = $this->Database->prepare($query);
$UpdateStatement->execute([$parent, $Entity->get('parent')]);
break;
} else if($_parent === NULL) {
break;
}
}
return parent::update($Entity);
}
}
|
