WP_Date_Query

Class for generating SQL clauses that filter a primary query according to date.

Defined (1)

The class is defined in the following location(s).

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