site-reviews / plugin / Database / Query.php

Query.php in Site Reviews trunk, at plugin/Database/Query.php

337 lines 10.8 KB

Raw Download Zip

1	<?php
2
3	namespace GeminiLabs\SiteReviews\Database;
4
5	use GeminiLabs\SiteReviews\Database;
6	use GeminiLabs\SiteReviews\Defaults\ReviewsDefaults;
7	use GeminiLabs\SiteReviews\Helper;
8	use GeminiLabs\SiteReviews\Helpers\Arr;
9	use GeminiLabs\SiteReviews\Helpers\Cast;
10	use GeminiLabs\SiteReviews\Modules\Rating;
11	use GeminiLabs\SiteReviews\Review;
12
13	/**
14	* @property array $args
15	* @property \wpdb $db
16	*/
17	class Query
18	{
19	use Sql;
20
21	public function __construct()
22	{
23	global $wpdb;
24	$this->db = $wpdb;
25	}
26
27	public function export(array $args = []): array
28	{
29	$this->setArgs($args);
30	return glsr(Database::class)->dbGetResults($this->queryExport(), \ARRAY_A);
31	}
32
33	public function hasRevisions(int $postId): bool
34	{
35	return (int) glsr(Database::class)->dbGetVar($this->queryHasRevisions($postId)) > 0;
36	}
37
38	public function import(array $args = []): array
39	{
40	$this->setArgs($args);
41	return glsr(Database::class)->dbGetResults($this->queryImport(), \ARRAY_A);
42	}
43
44	public function ratings(array $args = []): array
45	{
46	$this->setArgs($args, $unset = ['orderby']);
47	$results = glsr(Database::class)->dbGetResults($this->queryRatings(), \ARRAY_A);
48	return $this->normalizeRatings($results);
49	}
50
51	public function ratingsFor(string $metaType, array $args = []): array
52	{
53	$method = Helper::buildMethodName('queryRatingsFor', $metaType);
54	if (!method_exists($this, $method)) {
55	return [];
56	}
57	$this->setArgs($args, $unset = ['orderby']);
58	$results = glsr(Database::class)->dbGetResults($this->$method(), \ARRAY_A);
59	return $this->normalizeRatingsByAssignedId($results);
60	}
61
62	public function review(int $postId, bool $bypassCache = false): Review
63	{
64	$reviewId = Cast::toInt($postId);
65	$review = Helper::ifTrue($bypassCache, null,
66	fn () => glsr(Cache::class)->get($reviewId, 'reviews')
67	);
68	if (!$review instanceof Review) {
69	$result = $reviewId > 0
70	? glsr(Database::class)->dbGetRow($this->queryReviews($reviewId), \ARRAY_A)
71	: [];
72	$review = new Review($result);
73	glsr()->action('get/review', $review, $reviewId);
74	if ($review->isValid()) {
75	glsr(Cache::class)->store($review->ID, 'reviews', $review);
76	}
77	}
78	return $review;
79	}
80
81	public function reviewIds(array $args = []): array
82	{
83	$this->setArgs($args);
84	$postIds = glsr(Database::class)->dbGetCol($this->queryReviewIds());
85	return array_map('intval', $postIds);
86	}
87
88	public function reviews(array $args = [], array $postIds = []): array
89	{
90	if (!empty($postIds)) {
91	$this->setArgs($args);
92	$reviewIds = Arr::uniqueInt(Cast::toArray($postIds));
93	} else {
94	// We previously used a subquery here, but MariaDB doesn't support LIMIT in subqueries
95	// https://mariadb.com/kb/en/subquery-limitations/
96	$reviewIds = $this->reviewIds($args);
97	}
98	$reviewIds = implode(',', $reviewIds);
99	if (empty($reviewIds)) { // if there are no review IDs, return an empty result
100	return [];
101	}
102	$reviews = glsr(Database::class)->dbGetResults($this->queryReviews($reviewIds), \ARRAY_A);
103	foreach ($reviews as &$review) {
104	$review = new Review($review);
105	glsr()->action('get/review', $review, $review->ID);
106	glsr(Cache::class)->store($review->ID, 'reviews', $review);
107	}
108	return $reviews;
109	}
110
111	public function revisionIds(int $postId): array
112	{
113	return glsr(Database::class)->dbGetCol($this->queryRevisionIds($postId));
114	}
115
116	public function setArgs(array $args = [], array $unset = []): void
117	{
118	$args = glsr(ReviewsDefaults::class)->restrict($args);
119	foreach ($unset as $key) {
120	$args[$key] = '';
121	}
122	$this->args = $args;
123	}
124
125	public function totalReviews(array $args = [], array $reviews = []): int
126	{
127	$this->setArgs($args, $unset = ['orderby']);
128	if (empty($this->sqlLimit()) && !empty($reviews)) {
129	return count($reviews);
130	}
131	return (int) glsr(Database::class)->dbGetVar($this->queryTotalReviews());
132	}
133
134	protected function normalizeRatings(array $ratings = []): array
135	{
136	$normalized = [];
137	foreach ($ratings as $result) {
138	$count = $result['count'] ?? 0;
139	$rating = $result['rating'] ?? 0;
140	$type = $result['type'] ?? 'local';
141	if (!array_key_exists($type, $normalized)) {
142	$normalized[$type] = glsr(Rating::class)->emptyArray();
143	}
144	if (array_key_exists($rating, $normalized[$type])) {
145	$normalized[$type][$rating] = $count;
146	}
147	}
148	return $normalized;
149	}
150
151	protected function normalizeRatingsByAssignedId(array $ratings = []): array
152	{
153	$normalized = [];
154	foreach ($ratings as $result) {
155	$id = $result['ID'];
156	unset($result['ID']);
157	if (!array_key_exists($id, $normalized)) {
158	$normalized[$id] = [];
159	}
160	$normalized[$id][] = $result;
161	}
162	return array_map([$this, 'normalizeRatings'], $normalized);
163	}
164
165	protected function queryExport(): string
166	{
167	return $this->sql("
168	SELECT r.*,
169	GROUP_CONCAT(DISTINCT apt.post_id) AS post_ids,
170	GROUP_CONCAT(DISTINCT aut.user_id) AS user_ids
171	FROM table\|ratings AS r
172	{$this->join('assigned_posts', 'LEFT JOIN')}
173	{$this->join('assigned_users', 'LEFT JOIN')}
174	GROUP BY r.ID
175	ORDER BY r.ID
176	{$this->sqlLimit()}
177	{$this->sqlOffset()}
178	");
179	}
180
181	protected function queryHasRevisions(int $reviewId): string
182	{
183	$sql = "
184	SELECT COUNT(*)
185	FROM table\|posts
186	WHERE post_type = 'revision' AND post_parent = %d
187	";
188	return $this->sql($sql, $reviewId);
189	}
190
191	protected function queryImport(): string
192	{
193	$sql = "
194	SELECT m.post_id, m.meta_value
195	FROM table\|postmeta AS m
196	INNER JOIN table\|posts AS p ON (p.ID = m.post_id)
197	WHERE 1=1
198	AND p.post_type = %s AND m.meta_key = %s
199	ORDER BY m.meta_id
200	{$this->sqlLimit()}
201	{$this->sqlOffset()}
202	";
203	return $this->sql($sql, glsr()->post_type, glsr()->export_key);
204	}
205
206	/**
207	* @see 001cf37 — COUNT(DISTINCT r.ID) because reviews can have multiple assignments
208	*/
209	protected function queryRatings(): string
210	{
211	return $this->sql("
212	SELECT {$this->ratingColumn()} AS rating, r.type, COUNT(DISTINCT r.ID) AS count
213	FROM table\|ratings AS r
214	{$this->sqlJoin()}
215	{$this->sqlWhere()}
216	GROUP BY r.type, {$this->ratingColumn()}
217	");
218	}
219
220	/**
221	* @see 001cf37 — COUNT(DISTINCT r.ID) because reviews can have multiple assignments
222	*/
223	public function queryRatingsForPostmeta(): string
224	{
225	return $this->sql("
226	SELECT apt.post_id AS ID, {$this->ratingColumn()} AS rating, r.type, COUNT(DISTINCT r.ID) AS count
227	FROM table\|ratings AS r
228	{$this->sqlJoin(['assigned_posts'])}
229	WHERE 1=1
230	{$this->clauseAndStatus()}
231	{$this->clauseAndType()}
232	GROUP BY r.type, {$this->ratingColumn()}, apt.post_id
233	");
234	}
235
236	/**
237	* @see 001cf37 — COUNT(DISTINCT r.ID) because reviews can have multiple assignments
238	*/
239	protected function queryRatingsForTermmeta(): string
240	{
241	return $this->sql("
242	SELECT att.term_id AS ID, {$this->ratingColumn()} AS rating, r.type, COUNT(DISTINCT r.ID) AS count
243	FROM table\|ratings AS r
244	{$this->sqlJoin(['assigned_terms'])}
245	WHERE 1=1
246	{$this->clauseAndStatus()}
247	{$this->clauseAndType()}
248	GROUP BY r.type, {$this->ratingColumn()}, att.term_id
249	");
250	}
251
252	/**
253	* @see 001cf37 — COUNT(DISTINCT r.ID) because reviews can have multiple assignments
254	*/
255	protected function queryRatingsForUsermeta(): string
256	{
257	return $this->sql("
258	SELECT aut.user_id AS ID, {$this->ratingColumn()} AS rating, r.type, COUNT(DISTINCT r.ID) AS count
259	FROM table\|ratings AS r
260	{$this->sqlJoin(['assigned_users'])}
261	WHERE 1=1
262	{$this->clauseAndStatus()}
263	{$this->clauseAndType()}
264	GROUP BY r.type, {$this->ratingColumn()}, aut.user_id
265	");
266	}
267
268	protected function queryReviewIds(): string
269	{
270	return $this->sql("
271	SELECT r.review_id
272	FROM table\|ratings AS r
273	{$this->sqlJoin()}
274	{$this->sqlWhere()}
275	GROUP BY r.review_id
276	{$this->sqlOrderBy()}
277	{$this->sqlLimit()}
278	{$this->sqlOffset()}
279	");
280	}
281
282	/**
283	* @param int\|string $reviewIds
284	*/
285	protected function queryReviews($reviewIds): string
286	{
287	$orderBy = !empty($this->args['order']) ? $this->sqlOrderBy() : '';
288	$postType = glsr()->post_type;
289	return $this->sql("
290	SELECT
291	r.*,
292	p.post_author AS author_id,
293	p.post_date AS date,
294	p.post_date_gmt AS date_gmt,
295	p.post_content AS content,
296	p.post_title AS title,
297	p.post_status AS status,
298	GROUP_CONCAT(DISTINCT apt.post_id) AS post_ids,
299	GROUP_CONCAT(DISTINCT att.term_id) AS term_ids,
300	GROUP_CONCAT(DISTINCT aut.user_id) AS user_ids
301	FROM table\|ratings AS r
302	{$this->join('posts', 'INNER JOIN')}
303	{$this->join('assigned_posts', 'LEFT JOIN')}
304	{$this->join('assigned_terms', 'LEFT JOIN')}
305	{$this->join('assigned_users', 'LEFT JOIN')}
306	WHERE 1=1
307	AND r.review_id IN ({$reviewIds})
308	AND p.post_type = '{$postType}'
309	GROUP BY r.ID
310	{$orderBy}
311	");
312	}
313
314	protected function queryRevisionIds(int $reviewId): string
315	{
316	$sql = "
317	SELECT ID
318	FROM table\|posts
319	WHERE post_type = 'revision' AND post_parent = %d
320	";
321	return $this->sql($sql, $reviewId);
322	}
323
324	/**
325	* @see 001cf37 — COUNT(DISTINCT r.ID) because reviews can have multiple assignments
326	*/
327	protected function queryTotalReviews(): string
328	{
329	return $this->sql("
330	SELECT COUNT(DISTINCT r.ID) AS count
331	FROM table\|ratings AS r
332	{$this->sqlJoin()}
333	{$this->sqlWhere()}
334	");
335	}
336	}
337