| [ Index ] |
PHP Cross Reference of WordPress Trunk (Updated Daily) |
[Summary view] [Print] [Text view]
1 <?php 2 /** 3 * Class for generating SQL clauses that filter a primary query according to date. 4 * 5 * WP_Date_Query is a helper that allows primary query classes, such as WP_Query, to filter 6 * their results by date columns, by generating `WHERE` subclauses to be attached to the 7 * primary SQL query string. 8 * 9 * Attempting to filter by an invalid date value (eg month=13) will generate SQL that will 10 * return no results. In these cases, a _doing_it_wrong() error notice is also thrown. 11 * See WP_Date_Query::validate_date_values(). 12 * 13 * @link https://developer.wordpress.org/reference/classes/wp_query/ 14 * 15 * @since 3.7.0 16 */ 17 #[AllowDynamicProperties] 18 class WP_Date_Query { 19 /** 20 * Array of date queries. 21 * 22 * See WP_Date_Query::__construct() for information on date query arguments. 23 * 24 * @since 3.7.0 25 * @var array 26 */ 27 public $queries = array(); 28 29 /** 30 * The default relation between top-level queries. Can be either 'AND' or 'OR'. 31 * 32 * @since 3.7.0 33 * @var string 34 */ 35 public $relation = 'AND'; 36 37 /** 38 * The column to query against. Can be changed via the query arguments. 39 * 40 * @since 3.7.0 41 * @var string 42 */ 43 public $column = 'post_date'; 44 45 /** 46 * The value comparison operator. Can be changed via the query arguments. 47 * 48 * @since 3.7.0 49 * @var string 50 */ 51 public $compare = '='; 52 53 /** 54 * Supported time-related parameter keys. 55 * 56 * @since 4.1.0 57 * @var string[] 58 */ 59 public $time_keys = array( 'after', 'before', 'year', 'month', 'monthnum', 'week', 'w', 'dayofyear', 'day', 'dayofweek', 'dayofweek_iso', 'hour', 'minute', 'second' ); 60 61 /** 62 * Constructor. 63 * 64 * Time-related parameters that normally require integer values ('year', 'month', 'week', 'dayofyear', 'day', 65 * 'dayofweek', 'dayofweek_iso', 'hour', 'minute', 'second') accept arrays of integers for some values of 66 * 'compare'. When 'compare' is 'IN' or 'NOT IN', arrays are accepted; when 'compare' is 'BETWEEN' or 'NOT 67 * BETWEEN', arrays of two valid values are required. See individual argument descriptions for accepted values. 68 * 69 * @since 3.7.0 70 * @since 4.0.0 The $inclusive logic was updated to include all times within the date range. 71 * @since 4.1.0 Introduced 'dayofweek_iso' time type parameter. 72 * 73 * @param array $date_query { 74 * Array of date query clauses. 75 * 76 * @type array ...$0 { 77 * @type string $column Optional. The column to query against. If undefined, inherits the value of 78 * the `$default_column` parameter. See WP_Date_Query::validate_column() and 79 * the {@see 'date_query_valid_columns'} filter for the list of accepted values. 80 * Default 'post_date'. 81 * @type string $compare Optional. The comparison operator. Accepts '=', '!=', '>', '>=', '<', '<=', 82 * 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN'. Default '='. 83 * @type string $relation Optional. The boolean relationship between the date queries. Accepts 'OR' or 'AND'. 84 * Default 'OR'. 85 * @type array ...$0 { 86 * Optional. An array of first-order clause parameters, or another fully-formed date query. 87 * 88 * @type string|array $before { 89 * Optional. Date to retrieve posts before. Accepts `strtotime()`-compatible string, 90 * or array of 'year', 'month', 'day' values. 91 * 92 * @type string $year The four-digit year. Default empty. Accepts any four-digit year. 93 * @type string $month Optional when passing array.The month of the year. 94 * Default (string:empty)|(array:1). Accepts numbers 1-12. 95 * @type string $day Optional when passing array.The day of the month. 96 * Default (string:empty)|(array:1). Accepts numbers 1-31. 97 * } 98 * @type string|array $after { 99 * Optional. Date to retrieve posts after. Accepts `strtotime()`-compatible string, 100 * or array of 'year', 'month', 'day' values. 101 * 102 * @type string $year The four-digit year. Accepts any four-digit year. Default empty. 103 * @type string $month Optional when passing array. The month of the year. Accepts numbers 1-12. 104 * Default (string:empty)|(array:12). 105 * @type string $day Optional when passing array.The day of the month. Accepts numbers 1-31. 106 * Default (string:empty)|(array:last day of month). 107 * } 108 * @type string $column Optional. Used to add a clause comparing a column other than 109 * the column specified in the top-level `$column` parameter. 110 * See WP_Date_Query::validate_column() and 111 * the {@see 'date_query_valid_columns'} filter for the list 112 * of accepted values. Default is the value of top-level `$column`. 113 * @type string $compare Optional. The comparison operator. Accepts '=', '!=', '>', '>=', 114 * '<', '<=', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN'. 'IN', 115 * 'NOT IN', 'BETWEEN', and 'NOT BETWEEN'. Comparisons support 116 * arrays in some time-related parameters. Default '='. 117 * @type bool $inclusive Optional. Include results from dates specified in 'before' or 118 * 'after'. Default false. 119 * @type int|int[] $year Optional. The four-digit year number. Accepts any four-digit year 120 * or an array of years if `$compare` supports it. Default empty. 121 * @type int|int[] $month Optional. The two-digit month number. Accepts numbers 1-12 or an 122 * array of valid numbers if `$compare` supports it. Default empty. 123 * @type int|int[] $week Optional. The week number of the year. Accepts numbers 1-53 or an 124 * array of valid numbers if `$compare` supports it. Default empty. 125 * @type int|int[] $dayofyear Optional. The day number of the year. Accepts numbers 1-366 or an 126 * array of valid numbers if `$compare` supports it. 127 * @type int|int[] $day Optional. The day of the month. Accepts numbers 1-31 or an array 128 * of valid numbers if `$compare` supports it. Default empty. 129 * @type int|int[] $dayofweek Optional. The day number of the week. Accepts numbers 1-7 (1 is 130 * Sunday) or an array of valid numbers if `$compare` supports it. 131 * Default empty. 132 * @type int|int[] $dayofweek_iso Optional. The day number of the week (ISO). Accepts numbers 1-7 133 * (1 is Monday) or an array of valid numbers if `$compare` supports it. 134 * Default empty. 135 * @type int|int[] $hour Optional. The hour of the day. Accepts numbers 0-23 or an array 136 * of valid numbers if `$compare` supports it. Default empty. 137 * @type int|int[] $minute Optional. The minute of the hour. Accepts numbers 0-59 or an array 138 * of valid numbers if `$compare` supports it. Default empty. 139 * @type int|int[] $second Optional. The second of the minute. Accepts numbers 0-59 or an 140 * array of valid numbers if `$compare` supports it. Default empty. 141 * } 142 * } 143 * } 144 * @param string $default_column Optional. Default column to query against. See WP_Date_Query::validate_column() 145 * and the {@see 'date_query_valid_columns'} filter for the list of accepted values. 146 * Default 'post_date'. 147 */ 148 public function __construct( $date_query, $default_column = 'post_date' ) { 149 if ( empty( $date_query ) || ! is_array( $date_query ) ) { 150 return; 151 } 152 153 if ( isset( $date_query['relation'] ) ) { 154 $this->relation = $this->sanitize_relation( $date_query['relation'] ); 155 } else { 156 $this->relation = 'AND'; 157 } 158 159 // Support for passing time-based keys in the top level of the $date_query array. 160 if ( ! isset( $date_query[0] ) ) { 161 $date_query = array( $date_query ); 162 } 163 164 if ( ! empty( $date_query['column'] ) ) { 165 $date_query['column'] = esc_sql( $date_query['column'] ); 166 } else { 167 $date_query['column'] = esc_sql( $default_column ); 168 } 169 170 $this->column = $this->validate_column( $this->column ); 171 172 $this->compare = $this->get_compare( $date_query ); 173 174 $this->queries = $this->sanitize_query( $date_query ); 175 } 176 177 /** 178 * Recursive-friendly query sanitizer. 179 * 180 * Ensures that each query-level clause has a 'relation' key, and that 181 * each first-order clause contains all the necessary keys from `$defaults`. 182 * 183 * @since 4.1.0 184 * 185 * @param array $queries 186 * @param array $parent_query 187 * @return array Sanitized queries. 188 */ 189 public function sanitize_query( $queries, $parent_query = null ) { 190 $cleaned_query = array(); 191 192 $defaults = array( 193 'column' => 'post_date', 194 'compare' => '=', 195 'relation' => 'AND', 196 ); 197 198 // Numeric keys should always have array values. 199 foreach ( $queries as $qkey => $qvalue ) { 200 if ( is_numeric( $qkey ) && ! is_array( $qvalue ) ) { 201 unset( $queries[ $qkey ] ); 202 } 203 } 204 205 // Each query should have a value for each default key. Inherit from the parent when possible. 206 foreach ( $defaults as $dkey => $dvalue ) { 207 if ( isset( $queries[ $dkey ] ) ) { 208 continue; 209 } 210 211 $queries[ $dkey ] = $parent_query[ $dkey ] ?? $dvalue; 212 } 213 214 // Validate the dates passed in the query. 215 if ( $this->is_first_order_clause( $queries ) ) { 216 $this->validate_date_values( $queries ); 217 } 218 219 // Sanitize the relation parameter. 220 $queries['relation'] = $this->sanitize_relation( $queries['relation'] ); 221 222 foreach ( $queries as $key => $q ) { 223 if ( ! is_array( $q ) || in_array( $key, $this->time_keys, true ) ) { 224 // This is a first-order query. Trust the values and sanitize when building SQL. 225 $cleaned_query[ $key ] = $q; 226 } else { 227 // Any array without a time key is another query, so we recurse. 228 $cleaned_query[] = $this->sanitize_query( $q, $queries ); 229 } 230 } 231 232 return $cleaned_query; 233 } 234 235 /** 236 * Determines whether this is a first-order clause. 237 * 238 * Checks to see if the current clause has any time-related keys. 239 * If so, it's first-order. 240 * 241 * @since 4.1.0 242 * 243 * @param array $query Query clause. 244 * @return bool True if this is a first-order clause. 245 */ 246 protected function is_first_order_clause( $query ) { 247 $time_keys = array_intersect( $this->time_keys, array_keys( $query ) ); 248 return ! empty( $time_keys ); 249 } 250 251 /** 252 * Determines and validates what comparison operator to use. 253 * 254 * @since 3.7.0 255 * 256 * @param array $query A date query or a date subquery. 257 * @return string The comparison operator. 258 */ 259 public function get_compare( $query ) { 260 if ( ! empty( $query['compare'] ) 261 && in_array( $query['compare'], array( '=', '!=', '>', '>=', '<', '<=', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ), true ) 262 ) { 263 return strtoupper( $query['compare'] ); 264 } 265 266 return $this->compare; 267 } 268 269 /** 270 * Validates the given date_query values and triggers errors if something is not valid. 271 * 272 * Note that date queries with invalid date ranges are allowed to 273 * continue (though of course no items will be found for impossible dates). 274 * This method only generates debug notices for these cases. 275 * 276 * @since 4.1.0 277 * 278 * @param array $date_query The date_query array. 279 * @return bool True if all values in the query are valid, false if one or more fail. 280 */ 281 public function validate_date_values( $date_query = array() ) { 282 if ( empty( $date_query ) ) { 283 return false; 284 } 285 286 $valid = true; 287 288 /* 289 * Validate 'before' and 'after' up front, then let the 290 * validation routine continue to be sure that all invalid 291 * values generate errors too. 292 */ 293 if ( array_key_exists( 'before', $date_query ) && is_array( $date_query['before'] ) ) { 294 $valid = $this->validate_date_values( $date_query['before'] ); 295 } 296 297 if ( array_key_exists( 'after', $date_query ) && is_array( $date_query['after'] ) ) { 298 $valid = $this->validate_date_values( $date_query['after'] ); 299 } 300 301 // Array containing all min-max checks. 302 $min_max_checks = array(); 303 304 // Days per year. 305 if ( array_key_exists( 'year', $date_query ) ) { 306 /* 307 * If a year exists in the date query, we can use it to get the days. 308 * If multiple years are provided (as in a BETWEEN), use the first one. 309 */ 310 if ( is_array( $date_query['year'] ) ) { 311 $_year = reset( $date_query['year'] ); 312 } else { 313 $_year = $date_query['year']; 314 } 315 316 $max_days_of_year = (int) gmdate( 'z', mktime( 0, 0, 0, 12, 31, $_year ) ) + 1; 317 } else { 318 // Otherwise we use the max of 366 (leap-year). 319 $max_days_of_year = 366; 320 } 321 322 $min_max_checks['dayofyear'] = array( 323 'min' => 1, 324 'max' => $max_days_of_year, 325 ); 326 327 // Days per week. 328 $min_max_checks['dayofweek'] = array( 329 'min' => 1, 330 'max' => 7, 331 ); 332 333 // Days per week. 334 $min_max_checks['dayofweek_iso'] = array( 335 'min' => 1, 336 'max' => 7, 337 ); 338 339 // Months per year. 340 $min_max_checks['month'] = array( 341 'min' => 1, 342 'max' => 12, 343 ); 344 345 // Weeks per year. 346 if ( isset( $_year ) ) { 347 /* 348 * If we have a specific year, use it to calculate number of weeks. 349 * Note: the number of weeks in a year is the date in which Dec 28 appears. 350 */ 351 $week_count = gmdate( 'W', mktime( 0, 0, 0, 12, 28, $_year ) ); 352 353 } else { 354 // Otherwise set the week-count to a maximum of 53. 355 $week_count = 53; 356 } 357 358 $min_max_checks['week'] = array( 359 'min' => 1, 360 'max' => $week_count, 361 ); 362 363 // Days per month. 364 $min_max_checks['day'] = array( 365 'min' => 1, 366 'max' => 31, 367 ); 368 369 // Hours per day. 370 $min_max_checks['hour'] = array( 371 'min' => 0, 372 'max' => 23, 373 ); 374 375 // Minutes per hour. 376 $min_max_checks['minute'] = array( 377 'min' => 0, 378 'max' => 59, 379 ); 380 381 // Seconds per minute. 382 $min_max_checks['second'] = array( 383 'min' => 0, 384 'max' => 59, 385 ); 386 387 // Concatenate and throw a notice for each invalid value. 388 foreach ( $min_max_checks as $key => $check ) { 389 if ( ! array_key_exists( $key, $date_query ) ) { 390 continue; 391 } 392 393 // Throw a notice for each failing value. 394 foreach ( (array) $date_query[ $key ] as $_value ) { 395 $is_between = $_value >= $check['min'] && $_value <= $check['max']; 396 397 if ( ! is_numeric( $_value ) || ! $is_between ) { 398 $error = sprintf( 399 /* translators: Date query invalid date message. 1: Invalid value, 2: Type of value, 3: Minimum valid value, 4: Maximum valid value. */ 400 __( 'Invalid value %1$s for %2$s. Expected value should be between %3$s and %4$s.' ), 401 '<code>' . esc_html( $_value ) . '</code>', 402 '<code>' . esc_html( $key ) . '</code>', 403 '<code>' . esc_html( $check['min'] ) . '</code>', 404 '<code>' . esc_html( $check['max'] ) . '</code>' 405 ); 406 407 _doing_it_wrong( __CLASS__, $error, '4.1.0' ); 408 409 $valid = false; 410 } 411 } 412 } 413 414 // If we already have invalid date messages, don't bother running through checkdate(). 415 if ( ! $valid ) { 416 return $valid; 417 } 418 419 $day_month_year_error_msg = ''; 420 421 $day_exists = array_key_exists( 'day', $date_query ) && is_numeric( $date_query['day'] ); 422 $month_exists = array_key_exists( 'month', $date_query ) && is_numeric( $date_query['month'] ); 423 $year_exists = array_key_exists( 'year', $date_query ) && is_numeric( $date_query['year'] ); 424 425 if ( $day_exists && $month_exists && $year_exists ) { 426 // 1. Checking day, month, year combination. 427 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'] ) ) ) { 428 $day_month_year_error_msg = sprintf( 429 /* translators: 1: Year, 2: Month, 3: Day of month. */ 430 __( 'The following values do not describe a valid date: year %1$s, month %2$s, day %3$s.' ), 431 '<code>' . esc_html( $date_query['year'] ) . '</code>', 432 '<code>' . esc_html( $date_query['month'] ) . '</code>', 433 '<code>' . esc_html( $date_query['day'] ) . '</code>' 434 ); 435 436 $valid = false; 437 } 438 } elseif ( $day_exists && $month_exists ) { 439 /* 440 * 2. checking day, month combination 441 * We use 2012 because, as a leap year, it's the most permissive. 442 */ 443 if ( ! wp_checkdate( $date_query['month'], $date_query['day'], 2012, sprintf( '2012-%s-%s', $date_query['month'], $date_query['day'] ) ) ) { 444 $day_month_year_error_msg = sprintf( 445 /* translators: 1: Month, 2: Day of month. */ 446 __( 'The following values do not describe a valid date: month %1$s, day %2$s.' ), 447 '<code>' . esc_html( $date_query['month'] ) . '</code>', 448 '<code>' . esc_html( $date_query['day'] ) . '</code>' 449 ); 450 451 $valid = false; 452 } 453 } 454 455 if ( ! empty( $day_month_year_error_msg ) ) { 456 _doing_it_wrong( __CLASS__, $day_month_year_error_msg, '4.1.0' ); 457 } 458 459 return $valid; 460 } 461 462 /** 463 * Validates a column name parameter. 464 * 465 * Column names without a table prefix (like 'post_date') are checked against a list of 466 * allowed and known tables, and then, if found, have a table prefix (such as 'wp_posts.') 467 * prepended. Prefixed column names (such as 'wp_posts.post_date') bypass this allowed 468 * check, and are only sanitized to remove illegal characters. 469 * 470 * @since 3.7.0 471 * 472 * @global wpdb $wpdb WordPress database abstraction object. 473 * 474 * @param string $column The user-supplied column name. 475 * @return string A validated column name value. 476 */ 477 public function validate_column( $column ) { 478 global $wpdb; 479 480 $valid_columns = array( 481 'post_date', // Part of $wpdb->posts. 482 'post_date_gmt', // Part of $wpdb->posts. 483 'post_modified', // Part of $wpdb->posts. 484 'post_modified_gmt', // Part of $wpdb->posts. 485 'comment_date', // Part of $wpdb->comments. 486 'comment_date_gmt', // Part of $wpdb->comments. 487 'user_registered', // Part of $wpdb->users. 488 ); 489 490 if ( is_multisite() ) { 491 $valid_columns = array_merge( 492 $valid_columns, 493 array( 494 'registered', // Part of $wpdb->blogs. 495 'last_updated', // Part of $wpdb->blogs. 496 ) 497 ); 498 } 499 500 // Attempt to detect a table prefix. 501 if ( ! str_contains( $column, '.' ) ) { 502 /** 503 * Filters the list of valid date query columns. 504 * 505 * @since 3.7.0 506 * @since 4.1.0 Added 'user_registered' to the default recognized columns. 507 * @since 4.6.0 Added 'registered' and 'last_updated' to the default recognized columns. 508 * 509 * @param string[] $valid_columns An array of valid date query columns. Defaults 510 * are 'post_date', 'post_date_gmt', 'post_modified', 511 * 'post_modified_gmt', 'comment_date', 'comment_date_gmt', 512 * 'user_registered', 'registered', 'last_updated'. 513 */ 514 if ( ! in_array( $column, apply_filters( 'date_query_valid_columns', $valid_columns ), true ) ) { 515 $column = 'post_date'; 516 } 517 518 $known_columns = array( 519 $wpdb->posts => array( 520 'post_date', 521 'post_date_gmt', 522 'post_modified', 523 'post_modified_gmt', 524 ), 525 $wpdb->comments => array( 526 'comment_date', 527 'comment_date_gmt', 528 ), 529 $wpdb->users => array( 530 'user_registered', 531 ), 532 ); 533 534 if ( is_multisite() ) { 535 $known_columns[ $wpdb->blogs ] = array( 536 'registered', 537 'last_updated', 538 ); 539 } 540 541 // If it's a known column name, add the appropriate table prefix. 542 foreach ( $known_columns as $table_name => $table_columns ) { 543 if ( in_array( $column, $table_columns, true ) ) { 544 $column = $table_name . '.' . $column; 545 break; 546 } 547 } 548 } 549 550 // Remove unsafe characters. 551 return preg_replace( '/[^a-zA-Z0-9_$\.]/', '', $column ); 552 } 553 554 /** 555 * Generates WHERE clause to be appended to a main query. 556 * 557 * @since 3.7.0 558 * 559 * @return string MySQL WHERE clause. 560 */ 561 public function get_sql() { 562 $sql = $this->get_sql_clauses(); 563 564 $where = $sql['where']; 565 566 /** 567 * Filters the date query WHERE clause. 568 * 569 * @since 3.7.0 570 * 571 * @param string $where WHERE clause of the date query. 572 * @param WP_Date_Query $query The WP_Date_Query instance. 573 */ 574 return apply_filters( 'get_date_sql', $where, $this ); 575 } 576 577 /** 578 * Generates SQL clauses to be appended to a main query. 579 * 580 * Called by the public WP_Date_Query::get_sql(), this method is abstracted 581 * out to maintain parity with the other Query classes. 582 * 583 * @since 4.1.0 584 * 585 * @return string[] { 586 * Array containing JOIN and WHERE SQL clauses to append to the main query. 587 * 588 * @type string $join SQL fragment to append to the main JOIN clause. 589 * @type string $where SQL fragment to append to the main WHERE clause. 590 * } 591 */ 592 protected function get_sql_clauses() { 593 $sql = $this->get_sql_for_query( $this->queries ); 594 595 if ( ! empty( $sql['where'] ) ) { 596 $sql['where'] = ' AND ' . $sql['where']; 597 } 598 599 return $sql; 600 } 601 602 /** 603 * Generates SQL clauses for a single query array. 604 * 605 * If nested subqueries are found, this method recurses the tree to 606 * produce the properly nested SQL. 607 * 608 * @since 4.1.0 609 * 610 * @param array $query Query to parse. 611 * @param int $depth Optional. Number of tree levels deep we currently are. 612 * Used to calculate indentation. Default 0. 613 * @return array { 614 * Array containing JOIN and WHERE SQL clauses to append to a single query array. 615 * 616 * @type string $join SQL fragment to append to the main JOIN clause. 617 * @type string $where SQL fragment to append to the main WHERE clause. 618 * } 619 */ 620 protected function get_sql_for_query( $query, $depth = 0 ) { 621 $sql_chunks = array( 622 'join' => array(), 623 'where' => array(), 624 ); 625 626 $sql = array( 627 'join' => '', 628 'where' => '', 629 ); 630 631 $indent = ''; 632 for ( $i = 0; $i < $depth; $i++ ) { 633 $indent .= ' '; 634 } 635 636 foreach ( $query as $key => $clause ) { 637 if ( 'relation' === $key ) { 638 $relation = $query['relation']; 639 } elseif ( is_array( $clause ) ) { 640 641 // This is a first-order clause. 642 if ( $this->is_first_order_clause( $clause ) ) { 643 $clause_sql = $this->get_sql_for_clause( $clause, $query ); 644 645 $where_count = count( $clause_sql['where'] ); 646 if ( ! $where_count ) { 647 $sql_chunks['where'][] = ''; 648 } elseif ( 1 === $where_count ) { 649 $sql_chunks['where'][] = $clause_sql['where'][0]; 650 } else { 651 $sql_chunks['where'][] = '( ' . implode( ' AND ', $clause_sql['where'] ) . ' )'; 652 } 653 654 $sql_chunks['join'] = array_merge( $sql_chunks['join'], $clause_sql['join'] ); 655 // This is a subquery, so we recurse. 656 } else { 657 $clause_sql = $this->get_sql_for_query( $clause, $depth + 1 ); 658 659 $sql_chunks['where'][] = $clause_sql['where']; 660 $sql_chunks['join'][] = $clause_sql['join']; 661 } 662 } 663 } 664 665 // Filter to remove empties. 666 $sql_chunks['join'] = array_filter( $sql_chunks['join'] ); 667 $sql_chunks['where'] = array_filter( $sql_chunks['where'] ); 668 669 if ( empty( $relation ) ) { 670 $relation = 'AND'; 671 } 672 673 // Filter duplicate JOIN clauses and combine into a single string. 674 if ( ! empty( $sql_chunks['join'] ) ) { 675 $sql['join'] = implode( ' ', array_unique( $sql_chunks['join'] ) ); 676 } 677 678 // Generate a single WHERE clause with proper brackets and indentation. 679 if ( ! empty( $sql_chunks['where'] ) ) { 680 $sql['where'] = '( ' . "\n " . $indent . implode( ' ' . "\n " . $indent . $relation . ' ' . "\n " . $indent, $sql_chunks['where'] ) . "\n" . $indent . ')'; 681 } 682 683 return $sql; 684 } 685 686 /** 687 * Turns a single date clause into pieces for a WHERE clause. 688 * 689 * A wrapper for get_sql_for_clause(), included here for backward 690 * compatibility while retaining the naming convention across Query classes. 691 * 692 * @since 3.7.0 693 * 694 * @param array $query Date query arguments. 695 * @return array { 696 * Array containing JOIN and WHERE SQL clauses to append to the main query. 697 * 698 * @type string[] $join Array of SQL fragments to append to the main JOIN clause. 699 * @type string[] $where Array of SQL fragments to append to the main WHERE clause. 700 * } 701 */ 702 protected function get_sql_for_subquery( $query ) { 703 return $this->get_sql_for_clause( $query, '' ); 704 } 705 706 /** 707 * Turns a first-order date query into SQL for a WHERE clause. 708 * 709 * @since 4.1.0 710 * 711 * @global wpdb $wpdb WordPress database abstraction object. 712 * 713 * @param array $query Date query clause. 714 * @param array $parent_query Parent query of the current date query. 715 * @return array { 716 * Array containing JOIN and WHERE SQL clauses to append to the main query. 717 * 718 * @type string[] $join Array of SQL fragments to append to the main JOIN clause. 719 * @type string[] $where Array of SQL fragments to append to the main WHERE clause. 720 * } 721 */ 722 protected function get_sql_for_clause( $query, $parent_query ) { 723 global $wpdb; 724 725 // The sub-parts of a $where part. 726 $where_parts = array(); 727 728 $column = ( ! empty( $query['column'] ) ) ? esc_sql( $query['column'] ) : $this->column; 729 730 $column = $this->validate_column( $column ); 731 732 $compare = $this->get_compare( $query ); 733 734 $inclusive = ! empty( $query['inclusive'] ); 735 736 // Assign greater- and less-than values. 737 $lt = '<'; 738 $gt = '>'; 739 740 if ( $inclusive ) { 741 $lt .= '='; 742 $gt .= '='; 743 } 744 745 // Range queries. 746 if ( ! empty( $query['after'] ) ) { 747 $where_parts[] = $wpdb->prepare( "$column $gt %s", $this->build_mysql_datetime( $query['after'], ! $inclusive ) ); 748 } 749 if ( ! empty( $query['before'] ) ) { 750 $where_parts[] = $wpdb->prepare( "$column $lt %s", $this->build_mysql_datetime( $query['before'], $inclusive ) ); 751 } 752 // Specific value queries. 753 754 $date_units = array( 755 'YEAR' => array( 'year' ), 756 'MONTH' => array( 'month', 'monthnum' ), 757 '_wp_mysql_week' => array( 'week', 'w' ), 758 'DAYOFYEAR' => array( 'dayofyear' ), 759 'DAYOFMONTH' => array( 'day' ), 760 'DAYOFWEEK' => array( 'dayofweek' ), 761 'WEEKDAY' => array( 'dayofweek_iso' ), 762 ); 763 764 // Check of the possible date units and add them to the query. 765 foreach ( $date_units as $sql_part => $query_parts ) { 766 foreach ( $query_parts as $query_part ) { 767 if ( isset( $query[ $query_part ] ) ) { 768 $value = $this->build_value( $compare, $query[ $query_part ] ); 769 if ( $value ) { 770 switch ( $sql_part ) { 771 case '_wp_mysql_week': 772 $where_parts[] = _wp_mysql_week( $column ) . " $compare $value"; 773 break; 774 case 'WEEKDAY': 775 $where_parts[] = "$sql_part( $column ) + 1 $compare $value"; 776 break; 777 default: 778 $where_parts[] = "$sql_part( $column ) $compare $value"; 779 } 780 781 break; 782 } 783 } 784 } 785 } 786 787 if ( isset( $query['hour'] ) || isset( $query['minute'] ) || isset( $query['second'] ) ) { 788 // Avoid notices. 789 foreach ( array( 'hour', 'minute', 'second' ) as $unit ) { 790 if ( ! isset( $query[ $unit ] ) ) { 791 $query[ $unit ] = null; 792 } 793 } 794 795 $time_query = $this->build_time_query( $column, $compare, $query['hour'], $query['minute'], $query['second'] ); 796 if ( $time_query ) { 797 $where_parts[] = $time_query; 798 } 799 } 800 801 /* 802 * Return an array of 'join' and 'where' for compatibility 803 * with other query classes. 804 */ 805 return array( 806 'where' => $where_parts, 807 'join' => array(), 808 ); 809 } 810 811 /** 812 * Builds and validates a value string based on the comparison operator. 813 * 814 * @since 3.7.0 815 * 816 * @param string $compare The compare operator to use. 817 * @param string|array $value The value. 818 * @return string|false|int The value to be used in SQL or false on error. 819 */ 820 public function build_value( $compare, $value ) { 821 if ( ! isset( $value ) ) { 822 return false; 823 } 824 825 switch ( $compare ) { 826 case 'IN': 827 case 'NOT IN': 828 $value = (array) $value; 829 830 // Remove non-numeric values. 831 $value = array_filter( $value, 'is_numeric' ); 832 833 if ( empty( $value ) ) { 834 return false; 835 } 836 837 return '(' . implode( ',', array_map( 'intval', $value ) ) . ')'; 838 839 case 'BETWEEN': 840 case 'NOT BETWEEN': 841 if ( ! is_array( $value ) || 2 !== count( $value ) ) { 842 $value = array( $value, $value ); 843 } else { 844 $value = array_values( $value ); 845 } 846 847 // If either value is non-numeric, bail. 848 foreach ( $value as $v ) { 849 if ( ! is_numeric( $v ) ) { 850 return false; 851 } 852 } 853 854 $value = array_map( 'intval', $value ); 855 856 return $value[0] . ' AND ' . $value[1]; 857 858 default: 859 if ( ! is_numeric( $value ) ) { 860 return false; 861 } 862 863 return (int) $value; 864 } 865 } 866 867 /** 868 * Builds a MySQL format date/time based on some query parameters. 869 * 870 * You can pass an array of values (year, month, etc.) with missing parameter values being defaulted to 871 * either the maximum or minimum values (controlled by the $default_to parameter). Alternatively you can 872 * pass a string that will be passed to date_create(). 873 * 874 * @since 3.7.0 875 * 876 * @param string|array $datetime An array of parameters or a strtotime() string. 877 * @param bool $default_to_max Whether to round up incomplete dates. Supported by values 878 * of $datetime that are arrays, or string values that are a 879 * subset of MySQL date format ('Y', 'Y-m', 'Y-m-d', 'Y-m-d H:i'). 880 * Default: false. 881 * @return string|false A MySQL format date/time or false on failure. 882 */ 883 public function build_mysql_datetime( $datetime, $default_to_max = false ) { 884 if ( ! is_array( $datetime ) ) { 885 886 /* 887 * Try to parse some common date formats, so we can detect 888 * the level of precision and support the 'inclusive' parameter. 889 */ 890 if ( preg_match( '/^(\d{4})$/', $datetime, $matches ) ) { 891 // Y 892 $datetime = array( 893 'year' => (int) $matches[1], 894 ); 895 896 } elseif ( preg_match( '/^(\d{4})\-(\d{2})$/', $datetime, $matches ) ) { 897 // Y-m 898 $datetime = array( 899 'year' => (int) $matches[1], 900 'month' => (int) $matches[2], 901 ); 902 903 } elseif ( preg_match( '/^(\d{4})\-(\d{2})\-(\d{2})$/', $datetime, $matches ) ) { 904 // Y-m-d 905 $datetime = array( 906 'year' => (int) $matches[1], 907 'month' => (int) $matches[2], 908 'day' => (int) $matches[3], 909 ); 910 911 } elseif ( preg_match( '/^(\d{4})\-(\d{2})\-(\d{2}) (\d{2}):(\d{2})$/', $datetime, $matches ) ) { 912 // Y-m-d H:i 913 $datetime = array( 914 'year' => (int) $matches[1], 915 'month' => (int) $matches[2], 916 'day' => (int) $matches[3], 917 'hour' => (int) $matches[4], 918 'minute' => (int) $matches[5], 919 ); 920 } 921 922 // If no match is found, we don't support default_to_max. 923 if ( ! is_array( $datetime ) ) { 924 $wp_timezone = wp_timezone(); 925 926 // Assume local timezone if not provided. 927 $dt = date_create( $datetime, $wp_timezone ); 928 929 if ( false === $dt ) { 930 return gmdate( 'Y-m-d H:i:s', false ); 931 } 932 933 return $dt->setTimezone( $wp_timezone )->format( 'Y-m-d H:i:s' ); 934 } 935 } 936 937 $datetime = array_map( 'absint', $datetime ); 938 939 if ( ! isset( $datetime['year'] ) ) { 940 $datetime['year'] = current_time( 'Y' ); 941 } 942 943 if ( ! isset( $datetime['month'] ) ) { 944 $datetime['month'] = ( $default_to_max ) ? 12 : 1; 945 } 946 947 if ( ! isset( $datetime['day'] ) ) { 948 $datetime['day'] = ( $default_to_max ) ? (int) gmdate( 't', mktime( 0, 0, 0, $datetime['month'], 1, $datetime['year'] ) ) : 1; 949 } 950 951 if ( ! isset( $datetime['hour'] ) ) { 952 $datetime['hour'] = ( $default_to_max ) ? 23 : 0; 953 } 954 955 if ( ! isset( $datetime['minute'] ) ) { 956 $datetime['minute'] = ( $default_to_max ) ? 59 : 0; 957 } 958 959 if ( ! isset( $datetime['second'] ) ) { 960 $datetime['second'] = ( $default_to_max ) ? 59 : 0; 961 } 962 963 return sprintf( '%04d-%02d-%02d %02d:%02d:%02d', $datetime['year'], $datetime['month'], $datetime['day'], $datetime['hour'], $datetime['minute'], $datetime['second'] ); 964 } 965 966 /** 967 * Builds a query string for comparing time values (hour, minute, second). 968 * 969 * If just hour, minute, or second is set than a normal comparison will be done. 970 * However if multiple values are passed, a pseudo-decimal time will be created 971 * in order to be able to accurately compare against. 972 * 973 * @since 3.7.0 974 * 975 * @global wpdb $wpdb WordPress database abstraction object. 976 * 977 * @param string $column The column to query against. Needs to be pre-validated! 978 * @param string $compare The comparison operator. Needs to be pre-validated! 979 * @param int|null $hour Optional. An hour value (0-23). 980 * @param int|null $minute Optional. A minute value (0-59). 981 * @param int|null $second Optional. A second value (0-59). 982 * @return string|false A query part or false on failure. 983 */ 984 public function build_time_query( $column, $compare, $hour = null, $minute = null, $second = null ) { 985 global $wpdb; 986 987 // Have to have at least one. 988 if ( ! isset( $hour ) && ! isset( $minute ) && ! isset( $second ) ) { 989 return false; 990 } 991 992 // Complex combined queries aren't supported for multi-value queries. 993 if ( in_array( $compare, array( 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ), true ) ) { 994 $return = array(); 995 996 $value = $this->build_value( $compare, $hour ); 997 if ( false !== $value ) { 998 $return[] = "HOUR( $column ) $compare $value"; 999 } 1000 1001 $value = $this->build_value( $compare, $minute ); 1002 if ( false !== $value ) { 1003 $return[] = "MINUTE( $column ) $compare $value"; 1004 } 1005 1006 $value = $this->build_value( $compare, $second ); 1007 if ( false !== $value ) { 1008 $return[] = "SECOND( $column ) $compare $value"; 1009 } 1010 1011 return implode( ' AND ', $return ); 1012 } 1013 1014 // Cases where just one unit is set. 1015 if ( isset( $hour ) && ! isset( $minute ) && ! isset( $second ) ) { 1016 $value = $this->build_value( $compare, $hour ); 1017 if ( false !== $value ) { 1018 return "HOUR( $column ) $compare $value"; 1019 } 1020 } elseif ( ! isset( $hour ) && isset( $minute ) && ! isset( $second ) ) { 1021 $value = $this->build_value( $compare, $minute ); 1022 if ( false !== $value ) { 1023 return "MINUTE( $column ) $compare $value"; 1024 } 1025 } elseif ( ! isset( $hour ) && ! isset( $minute ) && isset( $second ) ) { 1026 $value = $this->build_value( $compare, $second ); 1027 if ( false !== $value ) { 1028 return "SECOND( $column ) $compare $value"; 1029 } 1030 } 1031 1032 // Single units were already handled. Since hour & second isn't allowed, minute must to be set. 1033 if ( ! isset( $minute ) ) { 1034 return false; 1035 } 1036 1037 $format = ''; 1038 $time = ''; 1039 1040 // Hour. 1041 if ( null !== $hour ) { 1042 $format .= '%H.'; 1043 $time .= sprintf( '%02d', $hour ) . '.'; 1044 } else { 1045 $format .= '0.'; 1046 $time .= '0.'; 1047 } 1048 1049 // Minute. 1050 $format .= '%i'; 1051 $time .= sprintf( '%02d', $minute ); 1052 1053 if ( isset( $second ) ) { 1054 $format .= '%s'; 1055 $time .= sprintf( '%02d', $second ); 1056 } 1057 1058 return $wpdb->prepare( "DATE_FORMAT( $column, %s ) $compare %f", $format, $time ); 1059 } 1060 1061 /** 1062 * Sanitizes a 'relation' operator. 1063 * 1064 * @since 6.0.3 1065 * 1066 * @param string $relation Raw relation key from the query argument. 1067 * @return string Sanitized relation. Either 'AND' or 'OR'. 1068 */ 1069 public function sanitize_relation( $relation ) { 1070 if ( 'OR' === strtoupper( $relation ) ) { 1071 return 'OR'; 1072 } else { 1073 return 'AND'; 1074 } 1075 } 1076 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated : Wed Apr 15 08:20:10 2026 | Cross-referenced by PHPXref |