aboutsummaryrefslogtreecommitdiffstats
path: root/core/namespace/ORM/RepositorySearch.php
blob: aac46ef311e0575c99a69205d2b9d892d11d9142 (plain)
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
<?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(is_numeric($filter['user'] ?? NULL)) {
			$extend[] = 'user = ? AND';
			$params[] = $filter['user'];
		}

		if(is_numeric($filter['category'] ?? NULL)) {
			$extend[] = 'category = ? AND';
			$params[] = $filter['category'];
		}

		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 [];
	}
}