easy-quotes / includes / data.php

data.php in Easy Quotes trunk, at includes/data.php

382 lines 14.2 KB

Raw Download Zip

1	<?php
2	/**
3	* Quotes Data Handler Class
4	*
5	* Optimized data handler with direct SQL queries for maximum performance.
6	*
7	* @package EasyQuotes
8	* @since 1.0.0
9	*/
10	declare(strict_types=1);
11
12	// Exit if accessed directly.
13	if (!defined('ABSPATH')) {
14	exit;
15	}
16
17	class Easy_Quotes_Data
18	{
19	function __construct() {
20	add_action('rest_api_init', [$this, 'rest_api_init'] );
21	}
22
23	function rest_api_init() {
24	register_rest_route('easy-quotes/v1', '/quotes', [
25	'methods' => 'GET',
26	'callback' => [$this, 'get_quotes_rest'],
27	'permission_callback' => function() {
28	return current_user_can('edit_posts');
29	}
30	]);
31	}
32
33	/**
34	* Get quotes with optional category, limit and random ordering
35	*
36	* @param int $category_id Category ID (-1 for all categories)
37	* @param int $limit Number of quotes to retrieve (0 for all)
38	* @param bool $random Whether to return quotes in random order
39	* @return array Array of quote objects with meta data
40	*/
41	public static function get_quotes(int $category_id = -1, int $limit = 0, bool $random = false) {
42	global $wpdb;
43
44	$order_by = $random ? "RAND()" : "post.post_date DESC";
45
46	if ($category_id === -1) {
47	// No category filter - simple query
48	$sql = $wpdb->prepare("
49	SELECT
50	post.ID,
51	post.post_date,
52	post.post_content,
53	post.post_title,
54	IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_author' THEN meta.meta_value END), '') AS meta_author,
55	IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_date' THEN meta.meta_value END), '') AS meta_date,
56	IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_rating' THEN meta.meta_value END), 0) AS meta_rating
57	FROM %i post
58	LEFT JOIN %i meta ON post.ID = meta.post_id
59	WHERE post.post_status = 'publish' AND post.post_type = 'quote'
60	GROUP BY post.ID
61	ORDER BY {$order_by}
62	", $wpdb->posts, $wpdb->postmeta);
63	} else {
64	// Simple category filter - no recursive children
65	$sql = $wpdb->prepare("
66	SELECT
67	post.ID,
68	post.post_date,
69	post.post_content,
70	post.post_title,
71	IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_author' THEN meta.meta_value END), '') AS meta_author,
72	IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_date' THEN meta.meta_value END), '') AS meta_date,
73	IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_rating' THEN meta.meta_value END), 0) AS meta_rating
74	FROM %i post
75	LEFT JOIN %i tr ON post.ID = tr.object_id
76	LEFT JOIN %i meta ON post.ID = meta.post_id
77	WHERE post.post_status = 'publish'
78	AND post.post_type = 'quote'
79	AND tr.term_taxonomy_id = %d
80	GROUP BY post.ID
81	ORDER BY {$order_by}
82	", $wpdb->posts, $wpdb->term_relationships, $wpdb->postmeta, $category_id);
83	}
84
85	// Apply limit if needed
86	if ($limit > 0) {
87	$sql .= $wpdb->prepare(" LIMIT %d", $limit);
88	}
89
90	$results = $wpdb->get_results($sql);
91
92	return self::render_post_content($results);
93	}
94
95	/**
96	* Get fallback quote (helper for get_daily_quotes)
97	*
98	* Returns the most recent published quote as a fallback when no quote
99	* matches the current date. This ensures there's always a quote to display.
100	*
101	* @param int $category_id Category ID (-1 for all categories)
102	* @return array Array with single fallback quote object
103	*/
104	private static function get_daily_quotes_fallback(int $category_id)
105	{
106	global $wpdb;
107
108	$sql = $wpdb->prepare("
109	SELECT
110	post.ID,
111	post.post_date,
112	post.post_content,
113	post.post_title,
114	IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_author' THEN meta.meta_value END), '') AS meta_author,
115	IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_date' THEN meta.meta_value END), '') AS meta_date,
116	IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_rating' THEN meta.meta_value END), 0) AS meta_rating,
117	DATE_FORMAT(post.post_date, '%%m%%d') AS daily
118	FROM %i post
119	LEFT JOIN %i meta ON post.ID = meta.post_id
120	LEFT JOIN %i tr ON post.ID = tr.object_id
121	WHERE post.post_status = 'publish'
122	AND post.post_type = 'quote'
123	AND (%d = -1 OR tr.term_taxonomy_id = %d)
124	GROUP BY post.ID
125	ORDER BY post.post_date DESC
126	LIMIT 1
127	", $wpdb->posts, $wpdb->postmeta, $wpdb->term_relationships, $category_id, $category_id);
128
129	return $wpdb->get_results($sql);
130	}
131
132	/**
133	* Get quotes within the date window (helper for get_daily_quotes)
134	*
135	* Uses a self-join to get only the most recent quote for each MMDD date
136	* within the specified date range. Excludes quotes where a newer quote
137	* exists with the same MMDD date.
138	*
139	* @param int $category_id Category ID (-1 for all categories)
140	* @return array Array of quote objects matching the date window
141	*/
142	private static function get_daily_quotes_window(int $category_id) {
143	global $wpdb;
144
145	$sql = $wpdb->prepare("
146	SELECT
147	post.ID,
148	post.post_date,
149	post.post_content,
150	post.post_title,
151	IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_author' THEN meta.meta_value END), '') AS meta_author,
152	IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_date' THEN meta.meta_value END), '') AS meta_date,
153	IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_rating' THEN meta.meta_value END), 0) AS meta_rating,
154	DATE_FORMAT(post.post_date, '%%m%%d') AS daily
155	FROM %i post
156	LEFT JOIN %i meta ON post.ID = meta.post_id
157	LEFT JOIN %i tr ON post.ID = tr.object_id
158	LEFT JOIN %i newer
159	ON DATE_FORMAT(newer.post_date, '%%m%%d') = DATE_FORMAT(post.post_date, '%%m%%d')
160	AND newer.post_date > post.post_date
161	AND newer.post_status = 'publish'
162	AND newer.post_type = 'quote'
163	WHERE post.post_status = 'publish'
164	AND post.post_type = 'quote'
165	AND newer.ID IS NULL
166	AND (%d = -1 OR tr.term_taxonomy_id = %d)
167	AND (
168	(@start_int <= @end_int AND CAST(DATE_FORMAT(post.post_date,'%%m%%d') AS UNSIGNED)
169	BETWEEN @start_int AND @end_int)
170	OR (@start_int > @end_int AND (
171	CAST(DATE_FORMAT(post.post_date,'%%m%%d') AS UNSIGNED) >= @start_int
172	OR CAST(DATE_FORMAT(post.post_date,'%%m%%d') AS UNSIGNED) <= @end_int
173	))
174	)
175	GROUP BY post.ID
176	ORDER BY post.post_date DESC
177	", $wpdb->posts, $wpdb->postmeta, $wpdb->term_relationships, $wpdb->posts, $category_id, $category_id);
178
179	return $wpdb->get_results($sql);
180	}
181
182	/**
183	* Get daily quotes for the next X days (cache-proof)
184	*
185	* Returns:
186	* - Index 0: Fallback quote (most recent published)
187	* - Index 1+: All quotes matching dates in the next X days
188	*
189	* This allows the frontend to work with cached pages by having all possible
190	* quotes pre-rendered, then JavaScript selects the correct one based on current date.
191	*
192	* @param int $category_id Category ID (-1 for all categories)
193	* @param int $days Number of days to look ahead (default 14)
194	* @return array Array of quote objects with meta data
195	*/
196	public static function get_daily_quotes(int $category_id = -1, int $days = 14) {
197	global $wpdb;
198
199	// Set rolling window (session vars are fine here)
200	$wpdb->query("SET @start_int = DATE_FORMAT(CURDATE(), '%m%d') + 0");
201	$wpdb->query(
202	$wpdb->prepare(
203	"SET @end_int = DATE_FORMAT(CURDATE() + INTERVAL %d DAY, '%%m%%d') + 0",
204	$days
205	)
206	);
207
208	$fallback = self::get_daily_quotes_fallback($category_id);
209	$daily = self::get_daily_quotes_window($category_id);
210
211	// Always row[0] = fallback
212	$results = [];
213
214	if (!empty($fallback)) {
215	$results[] = $fallback[0];
216	}
217
218	if (!empty($daily)) {
219	$results = array_merge($results, $daily);
220	}
221
222	return self::render_post_content($results);
223	}
224
225	/**
226	* Get a specific quote by post ID
227	*
228	* @param int $post_id Quote post ID
229	* @return object\|null Quote object with meta data or null
230	*/
231	public static function get_quote(int $post_id): ?object
232	{
233	if ($post_id <= 0) {
234	return null;
235	}
236
237	global $wpdb;
238
239	$sql = $wpdb->prepare("
240	SELECT
241	post.ID,
242	post.post_date,
243	post.post_content,
244	post.post_title,
245	IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_author' THEN meta.meta_value END), '') AS meta_author,
246	IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_date' THEN meta.meta_value END), '') AS meta_date,
247	IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_rating' THEN meta.meta_value END), 0) AS meta_rating
248	FROM %i post
249	LEFT JOIN %i meta ON post.ID = meta.post_id
250	WHERE post.ID = %d AND post.post_status = 'publish' AND post.post_type = 'quote'
251	GROUP BY post.ID
252	", $wpdb->posts, $wpdb->postmeta, $post_id);
253
254	$result = $wpdb->get_row($sql);
255	if ($result) {
256	$results = [$result];
257	$results = self::render_post_content($results);
258	return $results[0];
259	}
260	return null;
261	}
262
263	/**
264	* Get quote suggestions for autocomplete
265	*
266	* @param string $search Search term to filter quotes
267	* @param int $limit Maximum number of suggestions to return
268	* @return array Array of quote objects with minimal fields
269	*/
270	public static function get_quote_suggestions(string $search = '', int $limit = 8): array
271	{
272	global $wpdb;
273
274	if (empty($search)) {
275	// Return recent quotes when no search term provided
276	$sql = $wpdb->prepare("
277	SELECT
278	post.ID,
279	post.post_title
280	FROM %i post
281	WHERE post.post_status = 'publish'
282	AND post.post_type = 'quote'
283	ORDER BY post.post_date DESC
284	LIMIT %d
285	", $wpdb->posts, $limit);
286	} else {
287	// Return quotes matching search term
288	$sql = $wpdb->prepare("
289	SELECT
290	post.ID,
291	post.post_title
292	FROM %i post
293	WHERE post.post_status = 'publish'
294	AND post.post_type = 'quote'
295	AND post.post_title LIKE %s
296	ORDER BY post.post_date DESC
297	LIMIT %d
298	", $wpdb->posts, '%' . $wpdb->esc_like($search) . '%', $limit);
299	}
300
301	return $wpdb->get_results($sql);
302	}
303
304	/**
305	* Get quotes for our REST API endpoint
306	*
307	* @param WP_REST_Request $request
308	* @return WP_REST_Response
309	*/
310	public function get_quotes_rest(WP_REST_Request $request) {
311	// Extract parameters
312	$mode = $request->get_param('mode') ?: 'all';
313	$category_id = (int)($request->get_param('category') ?: -1);
314	$limit = (int)($request->get_param('limit') ?: 0);
315	$specific_id = (int)($request->get_param('id') ?: 0);
316	$search = $request->get_param('search') ?: '';
317
318	// Determine which function to call based on mode
319	switch ($mode) {
320	case 'suggestions':
321	// Get title suggestions for autocomplete
322	$result = self::get_quote_suggestions($search);
323	break;
324
325	case 'random':
326	// Get a single random quote
327	$result = self::get_quotes($category_id, 1, true);
328	break;
329
330	case 'daily':
331	// Get today's daily quote
332	$quotes = self::get_daily_quotes($category_id, 0);
333	// $quotes[1] = exact day match / $quotes[0] = fallback to the most recent published in this category
334	$result = $quotes[1] ?? $quotes[0] ?? [];
335	break;
336
337	case 'specific':
338	// Get a specific quote by ID
339	if ($specific_id > 0) {
340	$result = self::get_quote($specific_id);
341	if (!$result) {
342	return new WP_Error('not_found', 'Quote not found', ['status' => 404]);
343	}
344	} else {
345	return new WP_Error('invalid_id', 'Invalid quote ID', ['status' => 400]);
346	}
347	break;
348
349	case 'list':
350	// Get a limited list of quotes
351	$result = self::get_quotes($category_id, $limit);
352	break;
353
354	case 'rotation':
355	case 'all':
356	default:
357	// Get all quotes (possibly filtered by category)
358	$result = self::get_quotes($category_id);
359	break;
360	}
361
362	// Return the result
363	return rest_ensure_response($result);
364	}
365
366	/**
367	* Adds rendered post content to quote results
368	*
369	* @param array $results Quote result objects
370	* @return array Modified results with post_content_rendered added
371	*/
372	private static function render_post_content($results) {
373	// Add rendered content to each result
374	foreach ($results as $quote) {
375	$quote->post_content_rendered = wp_kses_post(wpautop(do_shortcode($quote->post_content)));
376	}
377
378	return $results; // Return the modified results
379	}
380
381	}
382