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
|
<?php
namespace ORM;
trait RepositorySearch {
# See "search" method for more details.
private $lastSearchOverallCount = 0;
#===============================================================================
# Get entities based on search query
#===============================================================================
public function search(string $search, array $filter = [], int $limit = NULL, int $offset = 0): array {
if(strlen($filter['year'] ?? '') !== 0) {
$extend[] = 'YEAR(time_insert) = ? AND';
$params[] = $filter['year'];
}
if(strlen($filter['month'] ?? '') !== 0) {
$extend[] = 'MONTH(time_insert) = ? AND';
$params[] = $filter['month'];
}
if(strlen($filter['day'] ?? '') !== 0) {
$extend[] = 'DAY(time_insert) = ? AND';
$params[] = $filter['day'];
}
if($limit) {
$limit = "LIMIT $offset,$limit";
}
$dateparts = implode(' ', $extend ?? []);
$query = 'SELECT *, COUNT(*) OVER() AS _count FROM %s WHERE %s MATCH(name, body)
AGAINST(? IN BOOLEAN MODE) %s';
$query = sprintf($query, static::getTableName(), $dateparts, $limit ?? 'LIMIT 20');
$Statement = $this->Database->prepare($query);
$Statement->execute(array_merge($params ?? [], [$search]));
if($entities = $this->fetchEntities($Statement)) {
# Temporary (maybe crappy) solution to prevent a second count query.
# Virtual column "_count" does not belong into the entities.
$this->lastSearchOverallCount = $entities[0]->get('_count');
}
return $entities;
}
#===============================================================================
# Get the number of overall results for the last performed search
#===============================================================================
public function getLastSearchOverallCount(): int {
return $this->lastSearchOverallCount;
}
#===============================================================================
# Get a list of distinct days
#===============================================================================
public function getDistinctDays(): array {
$query = 'SELECT DISTINCT DAY(time_insert) AS d FROM %s ORDER BY d';
$query = sprintf($query, static::getTableName());
$Statement = $this->Database->query($query);
if($result = $Statement->fetchAll($this->Database::FETCH_COLUMN)) {
return $result;
}
return [];
}
#===============================================================================
# Get a list of distinct months
#===============================================================================
public function getDistinctMonths(): array {
$query = 'SELECT DISTINCT MONTH(time_insert) AS m FROM %s ORDER BY m';
$query = sprintf($query, static::getTableName());
$Statement = $this->Database->query($query);
if($result = $Statement->fetchAll($this->Database::FETCH_COLUMN)) {
return $result;
}
return [];
}
#===============================================================================
# Get a list of distinct years
#===============================================================================
public function getDistinctYears(): array {
$query = 'SELECT DISTINCT YEAR(time_insert) AS y FROM %s ORDER BY y';
$query = sprintf($query, static::getTableName());
$Statement = $this->Database->query($query);
if($result = $Statement->fetchAll($this->Database::FETCH_COLUMN)) {
return $result;
}
return [];
}
}
|
