138 * @type int|array $second Optional. The second of the minute. Accepts numbers 0-60 or an |
138 * @type int|array $second Optional. The second of the minute. Accepts numbers 0-60 or an |
139 * array of valid numbers if `$compare` supports it. Default empty. |
139 * array of valid numbers if `$compare` supports it. Default empty. |
140 * } |
140 * } |
141 * } |
141 * } |
142 * } |
142 * } |
143 * @param array $default_column Optional. Default column to query against. Default 'post_date'. |
143 * @param string $default_column Optional. Default column to query against. Default 'post_date'. |
144 * Accepts 'post_date', 'post_date_gmt', 'post_modified', 'post_modified_gmt', |
144 * Accepts 'post_date', 'post_date_gmt', 'post_modified', 'post_modified_gmt', |
145 * 'comment_date', 'comment_date_gmt'. |
145 * 'comment_date', 'comment_date_gmt'. |
146 */ |
146 */ |
147 public function __construct( $date_query, $default_column = 'post_date' ) { |
147 public function __construct( $date_query, $default_column = 'post_date' ) { |
148 if ( isset( $date_query['relation'] ) && 'OR' === strtoupper( $date_query['relation'] ) ) { |
148 if ( isset( $date_query['relation'] ) && 'OR' === strtoupper( $date_query['relation'] ) ) { |
149 $this->relation = 'OR'; |
149 $this->relation = 'OR'; |
150 } else { |
150 } else { |
261 * |
261 * |
262 * @param array $query A date query or a date subquery. |
262 * @param array $query A date query or a date subquery. |
263 * @return string The comparison operator. |
263 * @return string The comparison operator. |
264 */ |
264 */ |
265 public function get_compare( $query ) { |
265 public function get_compare( $query ) { |
266 if ( ! empty( $query['compare'] ) && in_array( $query['compare'], array( '=', '!=', '>', '>=', '<', '<=', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ) ) ) |
266 if ( ! empty( $query['compare'] ) && in_array( $query['compare'], array( '=', '!=', '>', '>=', '<', '<=', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ) ) ) { |
267 return strtoupper( $query['compare'] ); |
267 return strtoupper( $query['compare'] ); |
|
268 } |
268 |
269 |
269 return $this->compare; |
270 return $this->compare; |
270 } |
271 } |
271 |
272 |
272 /** |
273 /** |
291 /* |
292 /* |
292 * Validate 'before' and 'after' up front, then let the |
293 * Validate 'before' and 'after' up front, then let the |
293 * validation routine continue to be sure that all invalid |
294 * validation routine continue to be sure that all invalid |
294 * values generate errors too. |
295 * values generate errors too. |
295 */ |
296 */ |
296 if ( array_key_exists( 'before', $date_query ) && is_array( $date_query['before'] ) ){ |
297 if ( array_key_exists( 'before', $date_query ) && is_array( $date_query['before'] ) ) { |
297 $valid = $this->validate_date_values( $date_query['before'] ); |
298 $valid = $this->validate_date_values( $date_query['before'] ); |
298 } |
299 } |
299 |
300 |
300 if ( array_key_exists( 'after', $date_query ) && is_array( $date_query['after'] ) ){ |
301 if ( array_key_exists( 'after', $date_query ) && is_array( $date_query['after'] ) ) { |
301 $valid = $this->validate_date_values( $date_query['after'] ); |
302 $valid = $this->validate_date_values( $date_query['after'] ); |
302 } |
303 } |
303 |
304 |
304 // Array containing all min-max checks. |
305 // Array containing all min-max checks. |
305 $min_max_checks = array(); |
306 $min_max_checks = array(); |
322 $max_days_of_year = 366; |
323 $max_days_of_year = 366; |
323 } |
324 } |
324 |
325 |
325 $min_max_checks['dayofyear'] = array( |
326 $min_max_checks['dayofyear'] = array( |
326 'min' => 1, |
327 'min' => 1, |
327 'max' => $max_days_of_year |
328 'max' => $max_days_of_year, |
328 ); |
329 ); |
329 |
330 |
330 // Days per week. |
331 // Days per week. |
331 $min_max_checks['dayofweek'] = array( |
332 $min_max_checks['dayofweek'] = array( |
332 'min' => 1, |
333 'min' => 1, |
333 'max' => 7 |
334 'max' => 7, |
334 ); |
335 ); |
335 |
336 |
336 // Days per week. |
337 // Days per week. |
337 $min_max_checks['dayofweek_iso'] = array( |
338 $min_max_checks['dayofweek_iso'] = array( |
338 'min' => 1, |
339 'min' => 1, |
339 'max' => 7 |
340 'max' => 7, |
340 ); |
341 ); |
341 |
342 |
342 // Months per year. |
343 // Months per year. |
343 $min_max_checks['month'] = array( |
344 $min_max_checks['month'] = array( |
344 'min' => 1, |
345 'min' => 1, |
345 'max' => 12 |
346 'max' => 12, |
346 ); |
347 ); |
347 |
348 |
348 // Weeks per year. |
349 // Weeks per year. |
349 if ( isset( $_year ) ) { |
350 if ( isset( $_year ) ) { |
350 /* |
351 /* |
358 $week_count = 53; |
359 $week_count = 53; |
359 } |
360 } |
360 |
361 |
361 $min_max_checks['week'] = array( |
362 $min_max_checks['week'] = array( |
362 'min' => 1, |
363 'min' => 1, |
363 'max' => $week_count |
364 'max' => $week_count, |
364 ); |
365 ); |
365 |
366 |
366 // Days per month. |
367 // Days per month. |
367 $min_max_checks['day'] = array( |
368 $min_max_checks['day'] = array( |
368 'min' => 1, |
369 'min' => 1, |
369 'max' => 31 |
370 'max' => 31, |
370 ); |
371 ); |
371 |
372 |
372 // Hours per day. |
373 // Hours per day. |
373 $min_max_checks['hour'] = array( |
374 $min_max_checks['hour'] = array( |
374 'min' => 0, |
375 'min' => 0, |
375 'max' => 23 |
376 'max' => 23, |
376 ); |
377 ); |
377 |
378 |
378 // Minutes per hour. |
379 // Minutes per hour. |
379 $min_max_checks['minute'] = array( |
380 $min_max_checks['minute'] = array( |
380 'min' => 0, |
381 'min' => 0, |
381 'max' => 59 |
382 'max' => 59, |
382 ); |
383 ); |
383 |
384 |
384 // Seconds per minute. |
385 // Seconds per minute. |
385 $min_max_checks['second'] = array( |
386 $min_max_checks['second'] = array( |
386 'min' => 0, |
387 'min' => 0, |
387 'max' => 59 |
388 'max' => 59, |
388 ); |
389 ); |
389 |
390 |
390 // Concatenate and throw a notice for each invalid value. |
391 // Concatenate and throw a notice for each invalid value. |
391 foreach ( $min_max_checks as $key => $check ) { |
392 foreach ( $min_max_checks as $key => $check ) { |
392 if ( ! array_key_exists( $key, $date_query ) ) { |
393 if ( ! array_key_exists( $key, $date_query ) ) { |
436 '<code>' . esc_html( $date_query['day'] ) . '</code>' |
437 '<code>' . esc_html( $date_query['day'] ) . '</code>' |
437 ); |
438 ); |
438 |
439 |
439 $valid = false; |
440 $valid = false; |
440 } |
441 } |
441 |
|
442 } elseif ( $day_exists && $month_exists ) { |
442 } elseif ( $day_exists && $month_exists ) { |
443 /* |
443 /* |
444 * 2. checking day, month combination |
444 * 2. checking day, month combination |
445 * We use 2012 because, as a leap year, it's the most permissive. |
445 * We use 2012 because, as a leap year, it's the most permissive. |
446 */ |
446 */ |
478 */ |
478 */ |
479 public function validate_column( $column ) { |
479 public function validate_column( $column ) { |
480 global $wpdb; |
480 global $wpdb; |
481 |
481 |
482 $valid_columns = array( |
482 $valid_columns = array( |
483 'post_date', 'post_date_gmt', 'post_modified', |
483 'post_date', |
484 'post_modified_gmt', 'comment_date', 'comment_date_gmt', |
484 'post_date_gmt', |
485 'user_registered', 'registered', 'last_updated', |
485 'post_modified', |
|
486 'post_modified_gmt', |
|
487 'comment_date', |
|
488 'comment_date_gmt', |
|
489 'user_registered', |
|
490 'registered', |
|
491 'last_updated', |
486 ); |
492 ); |
487 |
493 |
488 // Attempt to detect a table prefix. |
494 // Attempt to detect a table prefix. |
489 if ( false === strpos( $column, '.' ) ) { |
495 if ( false === strpos( $column, '.' ) ) { |
490 /** |
496 /** |
491 * Filters the list of valid date query columns. |
497 * Filters the list of valid date query columns. |
492 * |
498 * |
493 * @since 3.7.0 |
499 * @since 3.7.0 |
494 * @since 4.1.0 Added 'user_registered' to the default recognized columns. |
500 * @since 4.1.0 Added 'user_registered' to the default recognized columns. |
495 * |
501 * |
496 * @param array $valid_columns An array of valid date query columns. Defaults |
502 * @param string[] $valid_columns An array of valid date query columns. Defaults |
497 * are 'post_date', 'post_date_gmt', 'post_modified', |
503 * are 'post_date', 'post_date_gmt', 'post_modified', |
498 * 'post_modified_gmt', 'comment_date', 'comment_date_gmt', |
504 * 'post_modified_gmt', 'comment_date', 'comment_date_gmt', |
499 * 'user_registered' |
505 * 'user_registered' |
500 */ |
506 */ |
501 if ( ! in_array( $column, apply_filters( 'date_query_valid_columns', $valid_columns ) ) ) { |
507 if ( ! in_array( $column, apply_filters( 'date_query_valid_columns', $valid_columns ) ) ) { |
502 $column = 'post_date'; |
508 $column = 'post_date'; |
503 } |
509 } |
504 |
510 |
505 $known_columns = array( |
511 $known_columns = array( |
506 $wpdb->posts => array( |
512 $wpdb->posts => array( |
507 'post_date', |
513 'post_date', |
508 'post_date_gmt', |
514 'post_date_gmt', |
509 'post_modified', |
515 'post_modified', |
510 'post_modified_gmt', |
516 'post_modified_gmt', |
511 ), |
517 ), |
512 $wpdb->comments => array( |
518 $wpdb->comments => array( |
513 'comment_date', |
519 'comment_date', |
514 'comment_date_gmt', |
520 'comment_date_gmt', |
515 ), |
521 ), |
516 $wpdb->users => array( |
522 $wpdb->users => array( |
517 'user_registered', |
523 'user_registered', |
518 ), |
524 ), |
519 $wpdb->blogs => array( |
525 $wpdb->blogs => array( |
520 'registered', |
526 'registered', |
521 'last_updated', |
527 'last_updated', |
522 ), |
528 ), |
523 ); |
529 ); |
524 |
530 |
527 if ( in_array( $column, $table_columns ) ) { |
533 if ( in_array( $column, $table_columns ) ) { |
528 $column = $table_name . '.' . $column; |
534 $column = $table_name . '.' . $column; |
529 break; |
535 break; |
530 } |
536 } |
531 } |
537 } |
532 |
|
533 } |
538 } |
534 |
539 |
535 // Remove unsafe characters. |
540 // Remove unsafe characters. |
536 return preg_replace( '/[^a-zA-Z0-9_$\.]/', '', $column ); |
541 return preg_replace( '/[^a-zA-Z0-9_$\.]/', '', $column ); |
537 } |
542 } |
635 } else { |
640 } else { |
636 $sql_chunks['where'][] = '( ' . implode( ' AND ', $clause_sql['where'] ) . ' )'; |
641 $sql_chunks['where'][] = '( ' . implode( ' AND ', $clause_sql['where'] ) . ' )'; |
637 } |
642 } |
638 |
643 |
639 $sql_chunks['join'] = array_merge( $sql_chunks['join'], $clause_sql['join'] ); |
644 $sql_chunks['join'] = array_merge( $sql_chunks['join'], $clause_sql['join'] ); |
640 // This is a subquery, so we recurse. |
645 // This is a subquery, so we recurse. |
641 } else { |
646 } else { |
642 $clause_sql = $this->get_sql_for_query( $clause, $depth + 1 ); |
647 $clause_sql = $this->get_sql_for_query( $clause, $depth + 1 ); |
643 |
648 |
644 $sql_chunks['where'][] = $clause_sql['where']; |
649 $sql_chunks['where'][] = $clause_sql['where']; |
645 $sql_chunks['join'][] = $clause_sql['join']; |
650 $sql_chunks['join'][] = $clause_sql['join']; |
732 if ( ! empty( $query['before'] ) ) { |
737 if ( ! empty( $query['before'] ) ) { |
733 $where_parts[] = $wpdb->prepare( "$column $lt %s", $this->build_mysql_datetime( $query['before'], $inclusive ) ); |
738 $where_parts[] = $wpdb->prepare( "$column $lt %s", $this->build_mysql_datetime( $query['before'], $inclusive ) ); |
734 } |
739 } |
735 // Specific value queries. |
740 // Specific value queries. |
736 |
741 |
737 if ( isset( $query['year'] ) && $value = $this->build_value( $compare, $query['year'] ) ) |
742 if ( isset( $query['year'] ) && $value = $this->build_value( $compare, $query['year'] ) ) { |
738 $where_parts[] = "YEAR( $column ) $compare $value"; |
743 $where_parts[] = "YEAR( $column ) $compare $value"; |
|
744 } |
739 |
745 |
740 if ( isset( $query['month'] ) && $value = $this->build_value( $compare, $query['month'] ) ) { |
746 if ( isset( $query['month'] ) && $value = $this->build_value( $compare, $query['month'] ) ) { |
741 $where_parts[] = "MONTH( $column ) $compare $value"; |
747 $where_parts[] = "MONTH( $column ) $compare $value"; |
742 } elseif ( isset( $query['monthnum'] ) && $value = $this->build_value( $compare, $query['monthnum'] ) ) { |
748 } elseif ( isset( $query['monthnum'] ) && $value = $this->build_value( $compare, $query['monthnum'] ) ) { |
743 $where_parts[] = "MONTH( $column ) $compare $value"; |
749 $where_parts[] = "MONTH( $column ) $compare $value"; |
745 if ( isset( $query['week'] ) && false !== ( $value = $this->build_value( $compare, $query['week'] ) ) ) { |
751 if ( isset( $query['week'] ) && false !== ( $value = $this->build_value( $compare, $query['week'] ) ) ) { |
746 $where_parts[] = _wp_mysql_week( $column ) . " $compare $value"; |
752 $where_parts[] = _wp_mysql_week( $column ) . " $compare $value"; |
747 } elseif ( isset( $query['w'] ) && false !== ( $value = $this->build_value( $compare, $query['w'] ) ) ) { |
753 } elseif ( isset( $query['w'] ) && false !== ( $value = $this->build_value( $compare, $query['w'] ) ) ) { |
748 $where_parts[] = _wp_mysql_week( $column ) . " $compare $value"; |
754 $where_parts[] = _wp_mysql_week( $column ) . " $compare $value"; |
749 } |
755 } |
750 if ( isset( $query['dayofyear'] ) && $value = $this->build_value( $compare, $query['dayofyear'] ) ) |
756 if ( isset( $query['dayofyear'] ) && $value = $this->build_value( $compare, $query['dayofyear'] ) ) { |
751 $where_parts[] = "DAYOFYEAR( $column ) $compare $value"; |
757 $where_parts[] = "DAYOFYEAR( $column ) $compare $value"; |
752 |
758 } |
753 if ( isset( $query['day'] ) && $value = $this->build_value( $compare, $query['day'] ) ) |
759 |
|
760 if ( isset( $query['day'] ) && $value = $this->build_value( $compare, $query['day'] ) ) { |
754 $where_parts[] = "DAYOFMONTH( $column ) $compare $value"; |
761 $where_parts[] = "DAYOFMONTH( $column ) $compare $value"; |
755 |
762 } |
756 if ( isset( $query['dayofweek'] ) && $value = $this->build_value( $compare, $query['dayofweek'] ) ) |
763 |
|
764 if ( isset( $query['dayofweek'] ) && $value = $this->build_value( $compare, $query['dayofweek'] ) ) { |
757 $where_parts[] = "DAYOFWEEK( $column ) $compare $value"; |
765 $where_parts[] = "DAYOFWEEK( $column ) $compare $value"; |
758 |
766 } |
759 if ( isset( $query['dayofweek_iso'] ) && $value = $this->build_value( $compare, $query['dayofweek_iso'] ) ) |
767 |
|
768 if ( isset( $query['dayofweek_iso'] ) && $value = $this->build_value( $compare, $query['dayofweek_iso'] ) ) { |
760 $where_parts[] = "WEEKDAY( $column ) + 1 $compare $value"; |
769 $where_parts[] = "WEEKDAY( $column ) + 1 $compare $value"; |
|
770 } |
761 |
771 |
762 if ( isset( $query['hour'] ) || isset( $query['minute'] ) || isset( $query['second'] ) ) { |
772 if ( isset( $query['hour'] ) || isset( $query['minute'] ) || isset( $query['second'] ) ) { |
763 // Avoid notices. |
773 // Avoid notices. |
764 foreach ( array( 'hour', 'minute', 'second' ) as $unit ) { |
774 foreach ( array( 'hour', 'minute', 'second' ) as $unit ) { |
765 if ( ! isset( $query[ $unit ] ) ) { |
775 if ( ! isset( $query[ $unit ] ) ) { |
790 * @param string $compare The compare operator to use |
800 * @param string $compare The compare operator to use |
791 * @param string|array $value The value |
801 * @param string|array $value The value |
792 * @return string|false|int 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. |
793 */ |
803 */ |
794 public function build_value( $compare, $value ) { |
804 public function build_value( $compare, $value ) { |
795 if ( ! isset( $value ) ) |
805 if ( ! isset( $value ) ) { |
796 return false; |
806 return false; |
|
807 } |
797 |
808 |
798 switch ( $compare ) { |
809 switch ( $compare ) { |
799 case 'IN': |
810 case 'IN': |
800 case 'NOT IN': |
811 case 'NOT IN': |
801 $value = (array) $value; |
812 $value = (array) $value; |
901 } |
912 } |
902 } |
913 } |
903 |
914 |
904 $datetime = array_map( 'absint', $datetime ); |
915 $datetime = array_map( 'absint', $datetime ); |
905 |
916 |
906 if ( ! isset( $datetime['year'] ) ) |
917 if ( ! isset( $datetime['year'] ) ) { |
907 $datetime['year'] = gmdate( 'Y', $now ); |
918 $datetime['year'] = gmdate( 'Y', $now ); |
908 |
919 } |
909 if ( ! isset( $datetime['month'] ) ) |
920 |
|
921 if ( ! isset( $datetime['month'] ) ) { |
910 $datetime['month'] = ( $default_to_max ) ? 12 : 1; |
922 $datetime['month'] = ( $default_to_max ) ? 12 : 1; |
911 |
923 } |
912 if ( ! isset( $datetime['day'] ) ) |
924 |
|
925 if ( ! isset( $datetime['day'] ) ) { |
913 $datetime['day'] = ( $default_to_max ) ? (int) date( 't', mktime( 0, 0, 0, $datetime['month'], 1, $datetime['year'] ) ) : 1; |
926 $datetime['day'] = ( $default_to_max ) ? (int) date( 't', mktime( 0, 0, 0, $datetime['month'], 1, $datetime['year'] ) ) : 1; |
914 |
927 } |
915 if ( ! isset( $datetime['hour'] ) ) |
928 |
|
929 if ( ! isset( $datetime['hour'] ) ) { |
916 $datetime['hour'] = ( $default_to_max ) ? 23 : 0; |
930 $datetime['hour'] = ( $default_to_max ) ? 23 : 0; |
917 |
931 } |
918 if ( ! isset( $datetime['minute'] ) ) |
932 |
|
933 if ( ! isset( $datetime['minute'] ) ) { |
919 $datetime['minute'] = ( $default_to_max ) ? 59 : 0; |
934 $datetime['minute'] = ( $default_to_max ) ? 59 : 0; |
920 |
935 } |
921 if ( ! isset( $datetime['second'] ) ) |
936 |
|
937 if ( ! isset( $datetime['second'] ) ) { |
922 $datetime['second'] = ( $default_to_max ) ? 59 : 0; |
938 $datetime['second'] = ( $default_to_max ) ? 59 : 0; |
|
939 } |
923 |
940 |
924 return sprintf( '%04d-%02d-%02d %02d:%02d:%02d', $datetime['year'], $datetime['month'], $datetime['day'], $datetime['hour'], $datetime['minute'], $datetime['second'] ); |
941 return sprintf( '%04d-%02d-%02d %02d:%02d:%02d', $datetime['year'], $datetime['month'], $datetime['day'], $datetime['hour'], $datetime['minute'], $datetime['second'] ); |
925 } |
942 } |
926 |
943 |
927 /** |
944 /** |
942 */ |
959 */ |
943 public function build_time_query( $column, $compare, $hour = null, $minute = null, $second = null ) { |
960 public function build_time_query( $column, $compare, $hour = null, $minute = null, $second = null ) { |
944 global $wpdb; |
961 global $wpdb; |
945 |
962 |
946 // Have to have at least one |
963 // Have to have at least one |
947 if ( ! isset( $hour ) && ! isset( $minute ) && ! isset( $second ) ) |
964 if ( ! isset( $hour ) && ! isset( $minute ) && ! isset( $second ) ) { |
948 return false; |
965 return false; |
|
966 } |
949 |
967 |
950 // Complex combined queries aren't supported for multi-value queries |
968 // Complex combined queries aren't supported for multi-value queries |
951 if ( in_array( $compare, array( 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ) ) ) { |
969 if ( in_array( $compare, array( 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ) ) ) { |
952 $return = array(); |
970 $return = array(); |
953 |
971 |
954 if ( isset( $hour ) && false !== ( $value = $this->build_value( $compare, $hour ) ) ) |
972 if ( isset( $hour ) && false !== ( $value = $this->build_value( $compare, $hour ) ) ) { |
955 $return[] = "HOUR( $column ) $compare $value"; |
973 $return[] = "HOUR( $column ) $compare $value"; |
956 |
974 } |
957 if ( isset( $minute ) && false !== ( $value = $this->build_value( $compare, $minute ) ) ) |
975 |
|
976 if ( isset( $minute ) && false !== ( $value = $this->build_value( $compare, $minute ) ) ) { |
958 $return[] = "MINUTE( $column ) $compare $value"; |
977 $return[] = "MINUTE( $column ) $compare $value"; |
959 |
978 } |
960 if ( isset( $second ) && false !== ( $value = $this->build_value( $compare, $second ) ) ) |
979 |
|
980 if ( isset( $second ) && false !== ( $value = $this->build_value( $compare, $second ) ) ) { |
961 $return[] = "SECOND( $column ) $compare $value"; |
981 $return[] = "SECOND( $column ) $compare $value"; |
|
982 } |
962 |
983 |
963 return implode( ' AND ', $return ); |
984 return implode( ' AND ', $return ); |
964 } |
985 } |
965 |
986 |
966 // Cases where just one unit is set |
987 // Cases where just one unit is set |
971 } elseif ( ! isset( $hour ) && ! isset( $minute ) && isset( $second ) && false !== ( $value = $this->build_value( $compare, $second ) ) ) { |
992 } elseif ( ! isset( $hour ) && ! isset( $minute ) && isset( $second ) && false !== ( $value = $this->build_value( $compare, $second ) ) ) { |
972 return "SECOND( $column ) $compare $value"; |
993 return "SECOND( $column ) $compare $value"; |
973 } |
994 } |
974 |
995 |
975 // Single units were already handled. Since hour & second isn't allowed, minute must to be set. |
996 // Single units were already handled. Since hour & second isn't allowed, minute must to be set. |
976 if ( ! isset( $minute ) ) |
997 if ( ! isset( $minute ) ) { |
977 return false; |
998 return false; |
|
999 } |
978 |
1000 |
979 $format = $time = ''; |
1001 $format = $time = ''; |
980 |
1002 |
981 // Hour |
1003 // Hour |
982 if ( null !== $hour ) { |
1004 if ( null !== $hour ) { |