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