44 * @var array |
52 * @var array |
45 */ |
53 */ |
46 public $compare = '='; |
54 public $compare = '='; |
47 |
55 |
48 /** |
56 /** |
|
57 * Supported time-related parameter keys. |
|
58 * |
|
59 * @since 4.1.0 |
|
60 * @access public |
|
61 * @var array |
|
62 */ |
|
63 public $time_keys = array( 'after', 'before', 'year', 'month', 'monthnum', 'week', 'w', 'dayofyear', 'day', 'dayofweek', 'dayofweek_iso', 'hour', 'minute', 'second' ); |
|
64 |
|
65 /** |
49 * Constructor. |
66 * Constructor. |
50 * |
67 * |
|
68 * Time-related parameters that normally require integer values ('year', 'month', 'week', 'dayofyear', 'day', |
|
69 * 'dayofweek', 'dayofweek_iso', 'hour', 'minute', 'second') accept arrays of integers for some values of |
|
70 * 'compare'. When 'compare' is 'IN' or 'NOT IN', arrays are accepted; when 'compare' is 'BETWEEN' or 'NOT |
|
71 * BETWEEN', arrays of two valid values are required. See individual argument descriptions for accepted values. |
|
72 * |
|
73 * @since 3.7.0 |
|
74 * @since 4.0.0 The $inclusive logic was updated to include all times within the date range. |
|
75 * @since 4.1.0 Introduced 'dayofweek_iso' time type parameter. |
|
76 * @access public |
|
77 * |
51 * @param array $date_query { |
78 * @param array $date_query { |
52 * One or more associative arrays of date query parameters. |
79 * Array of date query clauses. |
53 * |
80 * |
54 * @type array { |
81 * @type array { |
55 * @type string $column Optional. The column to query against. If undefined, inherits the value of |
82 * @type string $column Optional. The column to query against. If undefined, inherits the value of |
56 * the $default_column parameter. Default 'post_date'. Accepts 'post_date', |
83 * the `$default_column` parameter. Accepts 'post_date', 'post_date_gmt', |
57 * 'post_date_gmt', 'post_modified','post_modified_gmt', 'comment_date', |
84 * 'post_modified','post_modified_gmt', 'comment_date', 'comment_date_gmt'. |
58 * 'comment_date_gmt'. |
85 * Default 'post_date'. |
59 * @type string $compare Optional. The comparison operator. |
86 * @type string $compare Optional. The comparison operator. Accepts '=', '!=', '>', '>=', '<', '<=', |
60 * Default '='. Accepts '=', '!=', '>', '>=', '<', '<=', 'IN', 'NOT IN', |
87 * 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN'. Default '='. |
61 * 'BETWEEN', 'NOT BETWEEN'. |
88 * @type string $relation Optional. The boolean relationship between the date queries. Accepts 'OR' or 'AND'. |
62 * @type string $relation Optional. The boolean relationship between the date queryies. |
89 * Default 'OR'. |
63 * Default 'OR'. Accepts 'OR', 'AND'. |
|
64 * @type array { |
90 * @type array { |
65 * @type string|array $before Optional. Date to retrieve posts before. Accepts strtotime()-compatible |
91 * Optional. An array of first-order clause parameters, or another fully-formed date query. |
66 * string, or array of 'year', 'month', 'day' values. { |
92 * |
|
93 * @type string|array $before { |
|
94 * Optional. Date to retrieve posts before. Accepts `strtotime()`-compatible string, |
|
95 * or array of 'year', 'month', 'day' values. |
67 * |
96 * |
68 * @type string $year The four-digit year. Default empty. Accepts any four-digit year. |
97 * @type string $year The four-digit year. Default empty. Accepts any four-digit year. |
69 * @type string $month Optional when passing array.The month of the year. |
98 * @type string $month Optional when passing array.The month of the year. |
70 * Default (string:empty)|(array:1). Accepts numbers 1-12. |
99 * Default (string:empty)|(array:1). Accepts numbers 1-12. |
71 * @type string $day Optional when passing array.The day of the month. |
100 * @type string $day Optional when passing array.The day of the month. |
72 * Default (string:empty)|(array:1). Accepts numbers 1-31. |
101 * Default (string:empty)|(array:1). Accepts numbers 1-31. |
73 * } |
102 * } |
74 * @type string|array $after Optional. Date to retrieve posts before. Accepts strtotime()-compatible |
103 * @type string|array $after { |
75 * string, or array of 'year', 'month', 'day' values. { |
104 * Optional. Date to retrieve posts after. Accepts `strtotime()`-compatible string, |
76 * |
105 * or array of 'year', 'month', 'day' values. |
77 * @type string $year The four-digit year. Default empty. Accepts any four-digit year. |
106 * |
78 * @type string $month Optional when passing array.The month of the year. |
107 * @type string $year The four-digit year. Accepts any four-digit year. Default empty. |
79 * Default (string:empty)|(array:12). Accepts numbers 1-12. |
108 * @type string $month Optional when passing array. The month of the year. Accepts numbers 1-12. |
80 * @type string $day Optional when passing array.The day of the month. |
109 * Default (string:empty)|(array:12). |
81 * Default (string:empty)|(array:last day of month). Accepts numbers 1-31. |
110 * @type string $day Optional when passing array.The day of the month. Accepts numbers 1-31. |
|
111 * Default (string:empty)|(array:last day of month). |
82 * } |
112 * } |
83 * @type string $column Optional. Used to add a clause comparing a column other than the column |
113 * @type string $column Optional. Used to add a clause comparing a column other than the |
84 * specified in the top-level $column paramater. Default is the value |
114 * column specified in the top-level `$column` parameter. Accepts |
85 * of top-level $column. Accepts 'post_date', 'post_date_gmt', |
115 * 'post_date', 'post_date_gmt', 'post_modified', 'post_modified_gmt', |
86 * 'post_modified', 'post_modified_gmt', 'comment_date', 'comment_date_gmt'. |
116 * 'comment_date', 'comment_date_gmt'. Default is the value of |
87 * @type string $compare Optional. The comparison operator. Default '='. Accepts '=', '!=', |
117 * top-level `$column`. |
88 * '>', '>=', '<', '<=', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN'. |
118 * @type string $compare Optional. The comparison operator. Accepts '=', '!=', '>', '>=', |
89 * @type bool $inclusive Optional. Include results from dates specified in 'before' or 'after'. |
119 * '<', '<=', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN'. 'IN', |
90 * Default. Accepts. |
120 * 'NOT IN', 'BETWEEN', and 'NOT BETWEEN'. Comparisons support |
91 * @type int $year Optional. The four-digit near number. Default empty. Accepts any |
121 * arrays in some time-related parameters. Default '='. |
92 * four-digit year. |
122 * @type bool $inclusive Optional. Include results from dates specified in 'before' or |
93 * @type int $month Optional. The two-digit month number. Default empty. Accepts numbers 1-12. |
123 * 'after'. Default false. |
94 * @type int $week Optional. The week number of the year. Default empty. Accepts numbers 0-53. |
124 * @type int|array $year Optional. The four-digit year number. Accepts any four-digit year |
95 * @type int $day Optional. The day of the month. Default empty. Accepts numbers 1-31. |
125 * or an array of years if `$compare` supports it. Default empty. |
96 * @type int $hour Optional. The hour of the day. Default empty. Accepts numbers 0-23. |
126 * @type int|array $month Optional. The two-digit month number. Accepts numbers 1-12 or an |
97 * @type int $minute Optional. The minute of the hour. Default empty. Accepts numbers 0-60. |
127 * array of valid numbers if `$compare` supports it. Default empty. |
98 * @type int $second Optional. The second of the minute. Default empty. Accepts numbers 0-60. |
128 * @type int|array $week Optional. The week number of the year. Accepts numbers 0-53 or an |
|
129 * array of valid numbers if `$compare` supports it. Default empty. |
|
130 * @type int|array $dayofyear Optional. The day number of the year. Accepts numbers 1-366 or an |
|
131 * array of valid numbers if `$compare` supports it. |
|
132 * @type int|array $day Optional. The day of the month. Accepts numbers 1-31 or an array |
|
133 * of valid numbers if `$compare` supports it. Default empty. |
|
134 * @type int|array $dayofweek Optional. The day number of the week. Accepts numbers 1-7 (1 is |
|
135 * Sunday) or an array of valid numbers if `$compare` supports it. |
|
136 * Default empty. |
|
137 * @type int|array $dayofweek_iso Optional. The day number of the week (ISO). Accepts numbers 1-7 |
|
138 * (1 is Monday) or an array of valid numbers if `$compare` supports it. |
|
139 * Default empty. |
|
140 * @type int|array $hour Optional. The hour of the day. Accepts numbers 0-23 or an array |
|
141 * of valid numbers if `$compare` supports it. Default empty. |
|
142 * @type int|array $minute Optional. The minute of the hour. Accepts numbers 0-60 or an array |
|
143 * of valid numbers if `$compare` supports it. Default empty. |
|
144 * @type int|array $second Optional. The second of the minute. Accepts numbers 0-60 or an |
|
145 * array of valid numbers if `$compare` supports it. Default empty. |
99 * } |
146 * } |
100 * } |
147 * } |
101 * } |
148 * } |
102 * @param array $default_column Optional. Default column to query against. Default 'post_date'. |
149 * @param array $default_column Optional. Default column to query against. Default 'post_date'. |
103 * Accepts 'post_date', 'post_date_gmt', 'post_modified', 'post_modified_gmt', |
150 * Accepts 'post_date', 'post_date_gmt', 'post_modified', 'post_modified_gmt', |
104 * 'comment_date', 'comment_date_gmt'. |
151 * 'comment_date', 'comment_date_gmt'. |
105 */ |
152 */ |
106 function __construct( $date_query, $default_column = 'post_date' ) { |
153 public function __construct( $date_query, $default_column = 'post_date' ) { |
107 if ( empty( $date_query ) || ! is_array( $date_query ) ) |
154 |
|
155 if ( isset( $date_query['relation'] ) && 'OR' === strtoupper( $date_query['relation'] ) ) { |
|
156 $this->relation = 'OR'; |
|
157 } else { |
|
158 $this->relation = 'AND'; |
|
159 } |
|
160 |
|
161 if ( ! is_array( $date_query ) ) { |
108 return; |
162 return; |
109 |
163 } |
110 if ( isset( $date_query['relation'] ) && strtoupper( $date_query['relation'] ) == 'OR' ) |
164 |
111 $this->relation = 'OR'; |
165 // Support for passing time-based keys in the top level of the $date_query array. |
112 else |
166 if ( ! isset( $date_query[0] ) && ! empty( $date_query ) ) { |
113 $this->relation = 'AND'; |
167 $date_query = array( $date_query ); |
114 |
168 } |
115 if ( ! empty( $date_query['column'] ) ) |
169 |
116 $this->column = esc_sql( $date_query['column'] ); |
170 if ( empty( $date_query ) ) { |
117 else |
171 return; |
118 $this->column = esc_sql( $default_column ); |
172 } |
|
173 |
|
174 if ( ! empty( $date_query['column'] ) ) { |
|
175 $date_query['column'] = esc_sql( $date_query['column'] ); |
|
176 } else { |
|
177 $date_query['column'] = esc_sql( $default_column ); |
|
178 } |
119 |
179 |
120 $this->column = $this->validate_column( $this->column ); |
180 $this->column = $this->validate_column( $this->column ); |
121 |
181 |
122 $this->compare = $this->get_compare( $date_query ); |
182 $this->compare = $this->get_compare( $date_query ); |
123 |
183 |
124 // If an array of arrays wasn't passed, fix it |
184 $this->queries = $this->sanitize_query( $date_query ); |
125 if ( ! isset( $date_query[0] ) ) |
185 } |
126 $date_query = array( $date_query ); |
186 |
127 |
187 /** |
128 $this->queries = array(); |
188 * Recursive-friendly query sanitizer. |
129 foreach ( $date_query as $key => $query ) { |
189 * |
130 if ( ! is_array( $query ) ) |
190 * Ensures that each query-level clause has a 'relation' key, and that |
|
191 * each first-order clause contains all the necessary keys from |
|
192 * `$defaults`. |
|
193 * |
|
194 * @since 4.1.0 |
|
195 * @access public |
|
196 * |
|
197 * @param array $queries |
|
198 * @param array $parent_query |
|
199 * |
|
200 * @return array Sanitized queries. |
|
201 */ |
|
202 public function sanitize_query( $queries, $parent_query = null ) { |
|
203 $cleaned_query = array(); |
|
204 |
|
205 $defaults = array( |
|
206 'column' => 'post_date', |
|
207 'compare' => '=', |
|
208 'relation' => 'AND', |
|
209 ); |
|
210 |
|
211 // Numeric keys should always have array values. |
|
212 foreach ( $queries as $qkey => $qvalue ) { |
|
213 if ( is_numeric( $qkey ) && ! is_array( $qvalue ) ) { |
|
214 unset( $queries[ $qkey ] ); |
|
215 } |
|
216 } |
|
217 |
|
218 // Each query should have a value for each default key. Inherit from the parent when possible. |
|
219 foreach ( $defaults as $dkey => $dvalue ) { |
|
220 if ( isset( $queries[ $dkey ] ) ) { |
131 continue; |
221 continue; |
132 |
222 } |
133 $this->queries[$key] = $query; |
223 |
134 } |
224 if ( isset( $parent_query[ $dkey ] ) ) { |
|
225 $queries[ $dkey ] = $parent_query[ $dkey ]; |
|
226 } else { |
|
227 $queries[ $dkey ] = $dvalue; |
|
228 } |
|
229 } |
|
230 |
|
231 // Validate the dates passed in the query. |
|
232 if ( $this->is_first_order_clause( $queries ) ) { |
|
233 $this->validate_date_values( $queries ); |
|
234 } |
|
235 |
|
236 foreach ( $queries as $key => $q ) { |
|
237 if ( ! is_array( $q ) || in_array( $key, $this->time_keys, true ) ) { |
|
238 // This is a first-order query. Trust the values and sanitize when building SQL. |
|
239 $cleaned_query[ $key ] = $q; |
|
240 } else { |
|
241 // Any array without a time key is another query, so we recurse. |
|
242 $cleaned_query[] = $this->sanitize_query( $q, $queries ); |
|
243 } |
|
244 } |
|
245 |
|
246 return $cleaned_query; |
|
247 } |
|
248 |
|
249 /** |
|
250 * Determine whether this is a first-order clause. |
|
251 * |
|
252 * Checks to see if the current clause has any time-related keys. |
|
253 * If so, it's first-order. |
|
254 * |
|
255 * @param array $query Query clause. |
|
256 * @return bool True if this is a first-order clause. |
|
257 */ |
|
258 protected function is_first_order_clause( $query ) { |
|
259 $time_keys = array_intersect( $this->time_keys, array_keys( $query ) ); |
|
260 return ! empty( $time_keys ); |
135 } |
261 } |
136 |
262 |
137 /** |
263 /** |
138 * Determines and validates what comparison operator to use. |
264 * Determines and validates what comparison operator to use. |
139 * |
265 * |
140 * @since 3.7.0 |
266 * @since 3.7.0 |
141 * @access public |
267 * @access public |
142 * |
268 * |
143 * @param array $query A date query or a date subquery |
269 * @param array $query A date query or a date subquery. |
144 * @return string The comparison operator |
270 * @return string The comparison operator. |
145 */ |
271 */ |
146 public function get_compare( $query ) { |
272 public function get_compare( $query ) { |
147 if ( ! empty( $query['compare'] ) && in_array( $query['compare'], array( '=', '!=', '>', '>=', '<', '<=', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ) ) ) |
273 if ( ! empty( $query['compare'] ) && in_array( $query['compare'], array( '=', '!=', '>', '>=', '<', '<=', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ) ) ) |
148 return strtoupper( $query['compare'] ); |
274 return strtoupper( $query['compare'] ); |
149 |
275 |
150 return $this->compare; |
276 return $this->compare; |
151 } |
277 } |
152 |
278 |
153 /** |
279 /** |
|
280 * Validates the given date_query values and triggers errors if something is not valid. |
|
281 * |
|
282 * Note that date queries with invalid date ranges are allowed to |
|
283 * continue (though of course no items will be found for impossible dates). |
|
284 * This method only generates debug notices for these cases. |
|
285 * |
|
286 * @since 4.1.0 |
|
287 * @access public |
|
288 * |
|
289 * @param array $date_query The date_query array. |
|
290 * @return bool True if all values in the query are valid, false if one or more fail. |
|
291 */ |
|
292 public function validate_date_values( $date_query = array() ) { |
|
293 if ( empty( $date_query ) ) { |
|
294 return false; |
|
295 } |
|
296 |
|
297 $valid = true; |
|
298 |
|
299 /* |
|
300 * Validate 'before' and 'after' up front, then let the |
|
301 * validation routine continue to be sure that all invalid |
|
302 * values generate errors too. |
|
303 */ |
|
304 if ( array_key_exists( 'before', $date_query ) && is_array( $date_query['before'] ) ){ |
|
305 $valid = $this->validate_date_values( $date_query['before'] ); |
|
306 } |
|
307 |
|
308 if ( array_key_exists( 'after', $date_query ) && is_array( $date_query['after'] ) ){ |
|
309 $valid = $this->validate_date_values( $date_query['after'] ); |
|
310 } |
|
311 |
|
312 // Array containing all min-max checks. |
|
313 $min_max_checks = array(); |
|
314 |
|
315 // Days per year. |
|
316 if ( array_key_exists( 'year', $date_query ) ) { |
|
317 /* |
|
318 * If a year exists in the date query, we can use it to get the days. |
|
319 * If multiple years are provided (as in a BETWEEN), use the first one. |
|
320 */ |
|
321 if ( is_array( $date_query['year'] ) ) { |
|
322 $_year = reset( $date_query['year'] ); |
|
323 } else { |
|
324 $_year = $date_query['year']; |
|
325 } |
|
326 |
|
327 $max_days_of_year = date( 'z', mktime( 0, 0, 0, 12, 31, $_year ) ) + 1; |
|
328 } else { |
|
329 // otherwise we use the max of 366 (leap-year) |
|
330 $max_days_of_year = 366; |
|
331 } |
|
332 |
|
333 $min_max_checks['dayofyear'] = array( |
|
334 'min' => 1, |
|
335 'max' => $max_days_of_year |
|
336 ); |
|
337 |
|
338 // Days per week. |
|
339 $min_max_checks['dayofweek'] = array( |
|
340 'min' => 1, |
|
341 'max' => 7 |
|
342 ); |
|
343 |
|
344 // Days per week. |
|
345 $min_max_checks['dayofweek_iso'] = array( |
|
346 'min' => 1, |
|
347 'max' => 7 |
|
348 ); |
|
349 |
|
350 // Months per year. |
|
351 $min_max_checks['month'] = array( |
|
352 'min' => 1, |
|
353 'max' => 12 |
|
354 ); |
|
355 |
|
356 // Weeks per year. |
|
357 if ( isset( $_year ) ) { |
|
358 // If we have a specific year, use it to calculate number of weeks. |
|
359 $date = new DateTime(); |
|
360 $date->setISODate( $_year, 53 ); |
|
361 $week_count = $date->format( "W" ) === "53" ? 53 : 52; |
|
362 |
|
363 } else { |
|
364 // Otherwise set the week-count to a maximum of 53. |
|
365 $week_count = 53; |
|
366 } |
|
367 |
|
368 $min_max_checks['week'] = array( |
|
369 'min' => 1, |
|
370 'max' => $week_count |
|
371 ); |
|
372 |
|
373 // Days per month. |
|
374 $min_max_checks['day'] = array( |
|
375 'min' => 1, |
|
376 'max' => 31 |
|
377 ); |
|
378 |
|
379 // Hours per day. |
|
380 $min_max_checks['hour'] = array( |
|
381 'min' => 0, |
|
382 'max' => 23 |
|
383 ); |
|
384 |
|
385 // Minutes per hour. |
|
386 $min_max_checks['minute'] = array( |
|
387 'min' => 0, |
|
388 'max' => 59 |
|
389 ); |
|
390 |
|
391 // Seconds per minute. |
|
392 $min_max_checks['second'] = array( |
|
393 'min' => 0, |
|
394 'max' => 59 |
|
395 ); |
|
396 |
|
397 // Concatenate and throw a notice for each invalid value. |
|
398 foreach ( $min_max_checks as $key => $check ) { |
|
399 if ( ! array_key_exists( $key, $date_query ) ) { |
|
400 continue; |
|
401 } |
|
402 |
|
403 // Throw a notice for each failing value. |
|
404 foreach ( (array) $date_query[ $key ] as $_value ) { |
|
405 $is_between = $_value >= $check['min'] && $_value <= $check['max']; |
|
406 |
|
407 if ( ! is_numeric( $_value ) || ! $is_between ) { |
|
408 $error = sprintf( |
|
409 /* translators: Date query invalid date message: 1: invalid value, 2: type of value, 3: minimum valid value, 4: maximum valid value */ |
|
410 __( 'Invalid value %1$s for %2$s. Expected value should be between %3$s and %4$s.' ), |
|
411 '<code>' . esc_html( $_value ) . '</code>', |
|
412 '<code>' . esc_html( $key ) . '</code>', |
|
413 '<code>' . esc_html( $check['min'] ) . '</code>', |
|
414 '<code>' . esc_html( $check['max'] ) . '</code>' |
|
415 ); |
|
416 |
|
417 _doing_it_wrong( __CLASS__, $error, '4.1.0' ); |
|
418 |
|
419 $valid = false; |
|
420 } |
|
421 } |
|
422 } |
|
423 |
|
424 // If we already have invalid date messages, don't bother running through checkdate(). |
|
425 if ( ! $valid ) { |
|
426 return $valid; |
|
427 } |
|
428 |
|
429 $day_month_year_error_msg = ''; |
|
430 |
|
431 $day_exists = array_key_exists( 'day', $date_query ) && is_numeric( $date_query['day'] ); |
|
432 $month_exists = array_key_exists( 'month', $date_query ) && is_numeric( $date_query['month'] ); |
|
433 $year_exists = array_key_exists( 'year', $date_query ) && is_numeric( $date_query['year'] ); |
|
434 |
|
435 if ( $day_exists && $month_exists && $year_exists ) { |
|
436 // 1. Checking day, month, year combination. |
|
437 if ( ! wp_checkdate( $date_query['month'], $date_query['day'], $date_query['year'], sprintf( '%s-%s-%s', $date_query['year'], $date_query['month'], $date_query['day'] ) ) ) { |
|
438 /* translators: 1: year, 2: month, 3: day of month */ |
|
439 $day_month_year_error_msg = sprintf( |
|
440 __( 'The following values do not describe a valid date: year %1$s, month %2$s, day %3$s.' ), |
|
441 '<code>' . esc_html( $date_query['year'] ) . '</code>', |
|
442 '<code>' . esc_html( $date_query['month'] ) . '</code>', |
|
443 '<code>' . esc_html( $date_query['day'] ) . '</code>' |
|
444 ); |
|
445 |
|
446 $valid = false; |
|
447 } |
|
448 |
|
449 } elseif ( $day_exists && $month_exists ) { |
|
450 /* |
|
451 * 2. checking day, month combination |
|
452 * We use 2012 because, as a leap year, it's the most permissive. |
|
453 */ |
|
454 if ( ! wp_checkdate( $date_query['month'], $date_query['day'], 2012, sprintf( '2012-%s-%s', $date_query['month'], $date_query['day'] ) ) ) { |
|
455 /* translators: 1: month, 2: day of month */ |
|
456 $day_month_year_error_msg = sprintf( |
|
457 __( 'The following values do not describe a valid date: month %1$s, day %2$s.' ), |
|
458 '<code>' . esc_html( $date_query['month'] ) . '</code>', |
|
459 '<code>' . esc_html( $date_query['day'] ) . '</code>' |
|
460 ); |
|
461 |
|
462 $valid = false; |
|
463 } |
|
464 } |
|
465 |
|
466 if ( ! empty( $day_month_year_error_msg ) ) { |
|
467 _doing_it_wrong( __CLASS__, $day_month_year_error_msg, '4.1.0' ); |
|
468 } |
|
469 |
|
470 return $valid; |
|
471 } |
|
472 |
|
473 /** |
154 * Validates a column name parameter. |
474 * Validates a column name parameter. |
|
475 * |
|
476 * Column names without a table prefix (like 'post_date') are checked against a whitelist of |
|
477 * known tables, and then, if found, have a table prefix (such as 'wp_posts.') prepended. |
|
478 * Prefixed column names (such as 'wp_posts.post_date') bypass this whitelist check, |
|
479 * and are only sanitized to remove illegal characters. |
155 * |
480 * |
156 * @since 3.7.0 |
481 * @since 3.7.0 |
157 * @access public |
482 * @access public |
158 * |
483 * |
159 * @param string $column The user-supplied column name. |
484 * @param string $column The user-supplied column name. |
160 * @return string A validated column name value. |
485 * @return string A validated column name value. |
161 */ |
486 */ |
162 public function validate_column( $column ) { |
487 public function validate_column( $column ) { |
|
488 global $wpdb; |
|
489 |
163 $valid_columns = array( |
490 $valid_columns = array( |
164 'post_date', 'post_date_gmt', 'post_modified', |
491 'post_date', 'post_date_gmt', 'post_modified', |
165 'post_modified_gmt', 'comment_date', 'comment_date_gmt' |
492 'post_modified_gmt', 'comment_date', 'comment_date_gmt', |
166 ); |
493 'user_registered', |
167 /** |
494 ); |
168 * Filter the list of valid date query columns. |
495 |
169 * |
496 // Attempt to detect a table prefix. |
170 * @since 3.7.0 |
497 if ( false === strpos( $column, '.' ) ) { |
171 * |
498 /** |
172 * @param array $valid_columns An array of valid date query columns. Defaults are 'post_date', 'post_date_gmt', |
499 * Filter the list of valid date query columns. |
173 * 'post_modified', 'post_modified_gmt', 'comment_date', 'comment_date_gmt' |
500 * |
174 */ |
501 * @since 3.7.0 |
175 if ( ! in_array( $column, apply_filters( 'date_query_valid_columns', $valid_columns ) ) ) |
502 * @since 4.1.0 Added 'user_registered' to the default recognized columns. |
176 $column = 'post_date'; |
503 * |
177 |
504 * @param array $valid_columns An array of valid date query columns. Defaults |
178 return $column; |
505 * are 'post_date', 'post_date_gmt', 'post_modified', |
179 } |
506 * 'post_modified_gmt', 'comment_date', 'comment_date_gmt', |
180 |
507 * 'user_registered' |
181 /** |
508 */ |
182 * Turns an array of date query parameters into a MySQL string. |
509 if ( ! in_array( $column, apply_filters( 'date_query_valid_columns', $valid_columns ) ) ) { |
|
510 $column = 'post_date'; |
|
511 } |
|
512 |
|
513 $known_columns = array( |
|
514 $wpdb->posts => array( |
|
515 'post_date', |
|
516 'post_date_gmt', |
|
517 'post_modified', |
|
518 'post_modified_gmt', |
|
519 ), |
|
520 $wpdb->comments => array( |
|
521 'comment_date', |
|
522 'comment_date_gmt', |
|
523 ), |
|
524 $wpdb->users => array( |
|
525 'user_registered', |
|
526 ), |
|
527 ); |
|
528 |
|
529 // If it's a known column name, add the appropriate table prefix. |
|
530 foreach ( $known_columns as $table_name => $table_columns ) { |
|
531 if ( in_array( $column, $table_columns ) ) { |
|
532 $column = $table_name . '.' . $column; |
|
533 break; |
|
534 } |
|
535 } |
|
536 |
|
537 } |
|
538 |
|
539 // Remove unsafe characters. |
|
540 return preg_replace( '/[^a-zA-Z0-9_$\.]/', '', $column ); |
|
541 } |
|
542 |
|
543 /** |
|
544 * Generate WHERE clause to be appended to a main query. |
183 * |
545 * |
184 * @since 3.7.0 |
546 * @since 3.7.0 |
185 * @access public |
547 * @access public |
186 * |
548 * |
187 * @return string MySQL WHERE parameters |
549 * @return string MySQL WHERE clause. |
188 */ |
550 */ |
189 public function get_sql() { |
551 public function get_sql() { |
190 // The parts of the final query |
552 $sql = $this->get_sql_clauses(); |
191 $where = array(); |
553 |
192 |
554 $where = $sql['where']; |
193 foreach ( $this->queries as $key => $query ) { |
|
194 $where_parts = $this->get_sql_for_subquery( $query ); |
|
195 if ( $where_parts ) { |
|
196 // Combine the parts of this subquery into a single string |
|
197 $where[ $key ] = '( ' . implode( ' AND ', $where_parts ) . ' )'; |
|
198 } |
|
199 } |
|
200 |
|
201 // Combine the subquery strings into a single string |
|
202 if ( $where ) |
|
203 $where = ' AND ( ' . implode( " {$this->relation} ", $where ) . ' )'; |
|
204 else |
|
205 $where = ''; |
|
206 |
555 |
207 /** |
556 /** |
208 * Filter the date query WHERE clause. |
557 * Filter the date query WHERE clause. |
209 * |
558 * |
210 * @since 3.7.0 |
559 * @since 3.7.0 |
214 */ |
563 */ |
215 return apply_filters( 'get_date_sql', $where, $this ); |
564 return apply_filters( 'get_date_sql', $where, $this ); |
216 } |
565 } |
217 |
566 |
218 /** |
567 /** |
219 * Turns a single date subquery into pieces for a WHERE clause. |
568 * Generate SQL clauses to be appended to a main query. |
220 * |
569 * |
221 * @since 3.7.0 |
570 * Called by the public {@see WP_Date_Query::get_sql()}, this method |
222 * return array |
571 * is abstracted out to maintain parity with the other Query classes. |
|
572 * |
|
573 * @since 4.1.0 |
|
574 * @access protected |
|
575 * |
|
576 * @return array { |
|
577 * Array containing JOIN and WHERE SQL clauses to append to the main query. |
|
578 * |
|
579 * @type string $join SQL fragment to append to the main JOIN clause. |
|
580 * @type string $where SQL fragment to append to the main WHERE clause. |
|
581 * } |
|
582 */ |
|
583 protected function get_sql_clauses() { |
|
584 $sql = $this->get_sql_for_query( $this->queries ); |
|
585 |
|
586 if ( ! empty( $sql['where'] ) ) { |
|
587 $sql['where'] = ' AND ' . $sql['where']; |
|
588 } |
|
589 |
|
590 return $sql; |
|
591 } |
|
592 |
|
593 /** |
|
594 * Generate SQL clauses for a single query array. |
|
595 * |
|
596 * If nested subqueries are found, this method recurses the tree to |
|
597 * produce the properly nested SQL. |
|
598 * |
|
599 * @since 4.1.0 |
|
600 * @access protected |
|
601 * |
|
602 * @param array $query Query to parse. |
|
603 * @param int $depth Optional. Number of tree levels deep we currently are. |
|
604 * Used to calculate indentation. Default 0. |
|
605 * @return array { |
|
606 * Array containing JOIN and WHERE SQL clauses to append to a single query array. |
|
607 * |
|
608 * @type string $join SQL fragment to append to the main JOIN clause. |
|
609 * @type string $where SQL fragment to append to the main WHERE clause. |
|
610 * } |
|
611 */ |
|
612 protected function get_sql_for_query( $query, $depth = 0 ) { |
|
613 $sql_chunks = array( |
|
614 'join' => array(), |
|
615 'where' => array(), |
|
616 ); |
|
617 |
|
618 $sql = array( |
|
619 'join' => '', |
|
620 'where' => '', |
|
621 ); |
|
622 |
|
623 $indent = ''; |
|
624 for ( $i = 0; $i < $depth; $i++ ) { |
|
625 $indent .= " "; |
|
626 } |
|
627 |
|
628 foreach ( $query as $key => $clause ) { |
|
629 if ( 'relation' === $key ) { |
|
630 $relation = $query['relation']; |
|
631 } elseif ( is_array( $clause ) ) { |
|
632 |
|
633 // This is a first-order clause. |
|
634 if ( $this->is_first_order_clause( $clause ) ) { |
|
635 $clause_sql = $this->get_sql_for_clause( $clause, $query ); |
|
636 |
|
637 $where_count = count( $clause_sql['where'] ); |
|
638 if ( ! $where_count ) { |
|
639 $sql_chunks['where'][] = ''; |
|
640 } elseif ( 1 === $where_count ) { |
|
641 $sql_chunks['where'][] = $clause_sql['where'][0]; |
|
642 } else { |
|
643 $sql_chunks['where'][] = '( ' . implode( ' AND ', $clause_sql['where'] ) . ' )'; |
|
644 } |
|
645 |
|
646 $sql_chunks['join'] = array_merge( $sql_chunks['join'], $clause_sql['join'] ); |
|
647 // This is a subquery, so we recurse. |
|
648 } else { |
|
649 $clause_sql = $this->get_sql_for_query( $clause, $depth + 1 ); |
|
650 |
|
651 $sql_chunks['where'][] = $clause_sql['where']; |
|
652 $sql_chunks['join'][] = $clause_sql['join']; |
|
653 } |
|
654 } |
|
655 } |
|
656 |
|
657 // Filter to remove empties. |
|
658 $sql_chunks['join'] = array_filter( $sql_chunks['join'] ); |
|
659 $sql_chunks['where'] = array_filter( $sql_chunks['where'] ); |
|
660 |
|
661 if ( empty( $relation ) ) { |
|
662 $relation = 'AND'; |
|
663 } |
|
664 |
|
665 // Filter duplicate JOIN clauses and combine into a single string. |
|
666 if ( ! empty( $sql_chunks['join'] ) ) { |
|
667 $sql['join'] = implode( ' ', array_unique( $sql_chunks['join'] ) ); |
|
668 } |
|
669 |
|
670 // Generate a single WHERE clause with proper brackets and indentation. |
|
671 if ( ! empty( $sql_chunks['where'] ) ) { |
|
672 $sql['where'] = '( ' . "\n " . $indent . implode( ' ' . "\n " . $indent . $relation . ' ' . "\n " . $indent, $sql_chunks['where'] ) . "\n" . $indent . ')'; |
|
673 } |
|
674 |
|
675 return $sql; |
|
676 } |
|
677 |
|
678 /** |
|
679 * Turns a single date clause into pieces for a WHERE clause. |
|
680 * |
|
681 * A wrapper for get_sql_for_clause(), included here for backward |
|
682 * compatibility while retaining the naming convention across Query classes. |
|
683 * |
|
684 * @since 3.7.0 |
|
685 * @access protected |
|
686 * |
|
687 * @param array $query Date query arguments. |
|
688 * @return array { |
|
689 * Array containing JOIN and WHERE SQL clauses to append to the main query. |
|
690 * |
|
691 * @type string $join SQL fragment to append to the main JOIN clause. |
|
692 * @type string $where SQL fragment to append to the main WHERE clause. |
|
693 * } |
223 */ |
694 */ |
224 protected function get_sql_for_subquery( $query ) { |
695 protected function get_sql_for_subquery( $query ) { |
|
696 return $this->get_sql_for_clause( $query, '' ); |
|
697 } |
|
698 |
|
699 /** |
|
700 * Turns a first-order date query into SQL for a WHERE clause. |
|
701 * |
|
702 * @since 4.1.0 |
|
703 * @access protected |
|
704 * |
|
705 * @param array $query Date query clause. |
|
706 * @param array $parent_query Parent query of the current date query. |
|
707 * @return array { |
|
708 * Array containing JOIN and WHERE SQL clauses to append to the main query. |
|
709 * |
|
710 * @type string $join SQL fragment to append to the main JOIN clause. |
|
711 * @type string $where SQL fragment to append to the main WHERE clause. |
|
712 * } |
|
713 */ |
|
714 protected function get_sql_for_clause( $query, $parent_query ) { |
225 global $wpdb; |
715 global $wpdb; |
226 |
716 |
227 // The sub-parts of a $where part |
717 // The sub-parts of a $where part. |
228 $where_parts = array(); |
718 $where_parts = array(); |
229 |
719 |
230 $column = ( ! empty( $query['column'] ) ) ? esc_sql( $query['column'] ) : $this->column; |
720 $column = ( ! empty( $query['column'] ) ) ? esc_sql( $query['column'] ) : $this->column; |
231 |
721 |
232 $column = $this->validate_column( $column ); |
722 $column = $this->validate_column( $column ); |
233 |
723 |
234 $compare = $this->get_compare( $query ); |
724 $compare = $this->get_compare( $query ); |
235 |
725 |
|
726 $inclusive = ! empty( $query['inclusive'] ); |
|
727 |
|
728 // Assign greater- and less-than values. |
236 $lt = '<'; |
729 $lt = '<'; |
237 $gt = '>'; |
730 $gt = '>'; |
238 if ( ! empty( $query['inclusive'] ) ) { |
731 |
|
732 if ( $inclusive ) { |
239 $lt .= '='; |
733 $lt .= '='; |
240 $gt .= '='; |
734 $gt .= '='; |
241 } |
735 } |
242 |
736 |
243 // Range queries |
737 // Range queries. |
244 if ( ! empty( $query['after'] ) ) |
738 if ( ! empty( $query['after'] ) ) |
245 $where_parts[] = $wpdb->prepare( "$column $gt %s", $this->build_mysql_datetime( $query['after'], true ) ); |
739 $where_parts[] = $wpdb->prepare( "$column $gt %s", $this->build_mysql_datetime( $query['after'], ! $inclusive ) ); |
246 |
740 |
247 if ( ! empty( $query['before'] ) ) |
741 if ( ! empty( $query['before'] ) ) |
248 $where_parts[] = $wpdb->prepare( "$column $lt %s", $this->build_mysql_datetime( $query['before'], false ) ); |
742 $where_parts[] = $wpdb->prepare( "$column $lt %s", $this->build_mysql_datetime( $query['before'], $inclusive ) ); |
249 |
743 |
250 // Specific value queries |
744 // Specific value queries. |
251 |
745 |
252 if ( isset( $query['year'] ) && $value = $this->build_value( $compare, $query['year'] ) ) |
746 if ( isset( $query['year'] ) && $value = $this->build_value( $compare, $query['year'] ) ) |
253 $where_parts[] = "YEAR( $column ) $compare $value"; |
747 $where_parts[] = "YEAR( $column ) $compare $value"; |
254 |
748 |
255 if ( isset( $query['month'] ) && $value = $this->build_value( $compare, $query['month'] ) ) |
749 if ( isset( $query['month'] ) && $value = $this->build_value( $compare, $query['month'] ) ) { |
256 $where_parts[] = "MONTH( $column ) $compare $value"; |
750 $where_parts[] = "MONTH( $column ) $compare $value"; |
257 |
751 } elseif ( isset( $query['monthnum'] ) && $value = $this->build_value( $compare, $query['monthnum'] ) ) { |
258 // Legacy |
|
259 if ( isset( $query['monthnum'] ) && $value = $this->build_value( $compare, $query['monthnum'] ) ) |
|
260 $where_parts[] = "MONTH( $column ) $compare $value"; |
752 $where_parts[] = "MONTH( $column ) $compare $value"; |
261 |
753 } |
262 if ( isset( $query['week'] ) && false !== ( $value = $this->build_value( $compare, $query['week'] ) ) ) |
754 if ( isset( $query['week'] ) && false !== ( $value = $this->build_value( $compare, $query['week'] ) ) ) { |
263 $where_parts[] = _wp_mysql_week( $column ) . " $compare $value"; |
755 $where_parts[] = _wp_mysql_week( $column ) . " $compare $value"; |
264 |
756 } elseif ( isset( $query['w'] ) && false !== ( $value = $this->build_value( $compare, $query['w'] ) ) ) { |
265 // Legacy |
|
266 if ( isset( $query['w'] ) && false !== ( $value = $this->build_value( $compare, $query['w'] ) ) ) |
|
267 $where_parts[] = _wp_mysql_week( $column ) . " $compare $value"; |
757 $where_parts[] = _wp_mysql_week( $column ) . " $compare $value"; |
268 |
758 } |
269 if ( isset( $query['dayofyear'] ) && $value = $this->build_value( $compare, $query['dayofyear'] ) ) |
759 if ( isset( $query['dayofyear'] ) && $value = $this->build_value( $compare, $query['dayofyear'] ) ) |
270 $where_parts[] = "DAYOFYEAR( $column ) $compare $value"; |
760 $where_parts[] = "DAYOFYEAR( $column ) $compare $value"; |
271 |
761 |
272 if ( isset( $query['day'] ) && $value = $this->build_value( $compare, $query['day'] ) ) |
762 if ( isset( $query['day'] ) && $value = $this->build_value( $compare, $query['day'] ) ) |
273 $where_parts[] = "DAYOFMONTH( $column ) $compare $value"; |
763 $where_parts[] = "DAYOFMONTH( $column ) $compare $value"; |
274 |
764 |
275 if ( isset( $query['dayofweek'] ) && $value = $this->build_value( $compare, $query['dayofweek'] ) ) |
765 if ( isset( $query['dayofweek'] ) && $value = $this->build_value( $compare, $query['dayofweek'] ) ) |
276 $where_parts[] = "DAYOFWEEK( $column ) $compare $value"; |
766 $where_parts[] = "DAYOFWEEK( $column ) $compare $value"; |
277 |
767 |
|
768 if ( isset( $query['dayofweek_iso'] ) && $value = $this->build_value( $compare, $query['dayofweek_iso'] ) ) |
|
769 $where_parts[] = "WEEKDAY( $column ) + 1 $compare $value"; |
|
770 |
278 if ( isset( $query['hour'] ) || isset( $query['minute'] ) || isset( $query['second'] ) ) { |
771 if ( isset( $query['hour'] ) || isset( $query['minute'] ) || isset( $query['second'] ) ) { |
279 // Avoid notices |
772 // Avoid notices. |
280 foreach ( array( 'hour', 'minute', 'second' ) as $unit ) { |
773 foreach ( array( 'hour', 'minute', 'second' ) as $unit ) { |
281 if ( ! isset( $query[$unit] ) ) { |
774 if ( ! isset( $query[ $unit ] ) ) { |
282 $query[$unit] = null; |
775 $query[ $unit ] = null; |
283 } |
776 } |
284 } |
777 } |
285 |
778 |
286 if ( $time_query = $this->build_time_query( $column, $compare, $query['hour'], $query['minute'], $query['second'] ) ) { |
779 if ( $time_query = $this->build_time_query( $column, $compare, $query['hour'], $query['minute'], $query['second'] ) ) { |
287 $where_parts[] = $time_query; |
780 $where_parts[] = $time_query; |
288 } |
781 } |
289 } |
782 } |
290 |
783 |
291 return $where_parts; |
784 /* |
|
785 * Return an array of 'join' and 'where' for compatibility |
|
786 * with other query classes. |
|
787 */ |
|
788 return array( |
|
789 'where' => $where_parts, |
|
790 'join' => array(), |
|
791 ); |
292 } |
792 } |
293 |
793 |
294 /** |
794 /** |
295 * Builds and validates a value string based on the comparison operator. |
795 * Builds and validates a value string based on the comparison operator. |
296 * |
796 * |
297 * @since 3.7.0 |
797 * @since 3.7.0 |
298 * @access public |
798 * @access public |
299 * |
799 * |
300 * @param string $compare The compare operator to use |
800 * @param string $compare The compare operator to use |
301 * @param string|array $value The value |
801 * @param string|array $value The value |
302 * @return string|int|false The value to be used in SQL or false on error. |
802 * @return string|false|int The value to be used in SQL or false on error. |
303 */ |
803 */ |
304 public function build_value( $compare, $value ) { |
804 public function build_value( $compare, $value ) { |
305 if ( ! isset( $value ) ) |
805 if ( ! isset( $value ) ) |
306 return false; |
806 return false; |
307 |
807 |
308 switch ( $compare ) { |
808 switch ( $compare ) { |
309 case 'IN': |
809 case 'IN': |
310 case 'NOT IN': |
810 case 'NOT IN': |
311 return '(' . implode( ',', array_map( 'intval', (array) $value ) ) . ')'; |
811 $value = (array) $value; |
|
812 |
|
813 // Remove non-numeric values. |
|
814 $value = array_filter( $value, 'is_numeric' ); |
|
815 |
|
816 if ( empty( $value ) ) { |
|
817 return false; |
|
818 } |
|
819 |
|
820 return '(' . implode( ',', array_map( 'intval', $value ) ) . ')'; |
312 |
821 |
313 case 'BETWEEN': |
822 case 'BETWEEN': |
314 case 'NOT BETWEEN': |
823 case 'NOT BETWEEN': |
315 if ( ! is_array( $value ) || 2 != count( $value ) || ! isset( $value[0] ) || ! isset( $value[1] ) ) |
824 if ( ! is_array( $value ) || 2 != count( $value ) ) { |
316 $value = array( $value, $value ); |
825 $value = array( $value, $value ); |
|
826 } else { |
|
827 $value = array_values( $value ); |
|
828 } |
|
829 |
|
830 // If either value is non-numeric, bail. |
|
831 foreach ( $value as $v ) { |
|
832 if ( ! is_numeric( $v ) ) { |
|
833 return false; |
|
834 } |
|
835 } |
317 |
836 |
318 $value = array_map( 'intval', $value ); |
837 $value = array_map( 'intval', $value ); |
319 |
838 |
320 return $value[0] . ' AND ' . $value[1]; |
839 return $value[0] . ' AND ' . $value[1]; |
321 |
840 |
322 default; |
841 default; |
|
842 if ( ! is_numeric( $value ) ) { |
|
843 return false; |
|
844 } |
|
845 |
323 return (int) $value; |
846 return (int) $value; |
324 } |
847 } |
325 } |
848 } |
326 |
849 |
327 /** |
850 /** |