<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head><meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>[29923] trunk: Introduce nested query support to WP_Date_Query.</title>
</head>
<body>
<style type="text/css"><!--
#msg dl.meta { border: 1px #006 solid; background: #369; padding: 6px; color: #fff; }
#msg dl.meta dt { float: left; width: 6em; font-weight: bold; }
#msg dt:after { content:':';}
#msg dl, #msg dt, #msg ul, #msg li, #header, #footer, #logmsg { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; }
#msg dl a { font-weight: bold}
#msg dl a:link { color:#fc3; }
#msg dl a:active { color:#ff0; }
#msg dl a:visited { color:#cc6; }
h3 { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; font-weight: bold; }
#msg pre { overflow: auto; background: #ffc; border: 1px #fa0 solid; padding: 6px; }
#logmsg { background: #ffc; border: 1px #fa0 solid; padding: 1em 1em 0 1em; }
#logmsg p, #logmsg pre, #logmsg blockquote { margin: 0 0 1em 0; }
#logmsg p, #logmsg li, #logmsg dt, #logmsg dd { line-height: 14pt; }
#logmsg h1, #logmsg h2, #logmsg h3, #logmsg h4, #logmsg h5, #logmsg h6 { margin: .5em 0; }
#logmsg h1:first-child, #logmsg h2:first-child, #logmsg h3:first-child, #logmsg h4:first-child, #logmsg h5:first-child, #logmsg h6:first-child { margin-top: 0; }
#logmsg ul, #logmsg ol { padding: 0; list-style-position: inside; margin: 0 0 0 1em; }
#logmsg ul { text-indent: -1em; padding-left: 1em; }#logmsg ol { text-indent: -1.5em; padding-left: 1.5em; }
#logmsg > ul, #logmsg > ol { margin: 0 0 1em 0; }
#logmsg pre { background: #eee; padding: 1em; }
#logmsg blockquote { border: 1px solid #fa0; border-left-width: 10px; padding: 1em 1em 0 1em; background: white;}
#logmsg dl { margin: 0; }
#logmsg dt { font-weight: bold; }
#logmsg dd { margin: 0; padding: 0 0 0.5em 0; }
#logmsg dd:before { content:'\00bb';}
#logmsg table { border-spacing: 0px; border-collapse: collapse; border-top: 4px solid #fa0; border-bottom: 1px solid #fa0; background: #fff; }
#logmsg table th { text-align: left; font-weight: normal; padding: 0.2em 0.5em; border-top: 1px dotted #fa0; }
#logmsg table td { text-align: right; border-top: 1px dotted #fa0; padding: 0.2em 0.5em; }
#logmsg table thead th { text-align: center; border-bottom: 1px solid #fa0; }
#logmsg table th.Corner { text-align: left; }
#logmsg hr { border: none 0; border-top: 2px dashed #fa0; height: 1px; }
#header, #footer { color: #fff; background: #636; border: 1px #300 solid; padding: 6px; }
#patch { width: 100%; }
#patch h4 {font-family: verdana,arial,helvetica,sans-serif;font-size:10pt;padding:8px;background:#369;color:#fff;margin:0;}
#patch .propset h4, #patch .binary h4 {margin:0;}
#patch pre {padding:0;line-height:1.2em;margin:0;}
#patch .diff {width:100%;background:#eee;padding: 0 0 10px 0;overflow:auto;}
#patch .propset .diff, #patch .binary .diff {padding:10px 0;}
#patch span {display:block;padding:0 10px;}
#patch .modfile, #patch .addfile, #patch .delfile, #patch .propset, #patch .binary, #patch .copfile {border:1px solid #ccc;margin:10px 0;}
#patch ins {background:#dfd;text-decoration:none;display:block;padding:0 10px;}
#patch del {background:#fdd;text-decoration:none;display:block;padding:0 10px;}
#patch .lines, .info {color:#888;background:#fff;}
--></style>
<div id="msg">
<dl class="meta" style="font-size: 105%">
<dt style="float: left; width: 6em; font-weight: bold">Revision</dt> <dd><a style="font-weight: bold" href="https://core.trac.wordpress.org/changeset/29923">29923</a><script type="application/ld+json">{"@context":"http://schema.org","@type":"EmailMessage","description":"Review this Commit","action":{"@type":"ViewAction","url":"https://core.trac.wordpress.org/changeset/29923","name":"Review Commit"}}</script></dd>
<dt style="float: left; width: 6em; font-weight: bold">Author</dt> <dd>boonebgorges</dd>
<dt style="float: left; width: 6em; font-weight: bold">Date</dt> <dd>2014-10-16 19:33:24 +0000 (Thu, 16 Oct 2014)</dd>
</dl>
<pre style='padding-left: 1em; margin: 2em 0; border-left: 2px solid #ccc; line-height: 1.25; font-size: 105%; font-family: sans-serif'>Introduce nested query support to WP_Date_Query.
This enhancement makes it possible to filter post, comment, and other queries
by date in ways that are arbitrarily complex, using mixed AND and OR relations.
Includes unit tests for the new syntax. In a few places, the existing unit
tests were slightly too strict (such as when checking the exact syntax of a SQL
string); these existing tests have been narrowed.
Props boonebgorges.
Fixes <a href="https://core.trac.wordpress.org/ticket/29822">#29822</a>.</pre>
<h3>Modified Paths</h3>
<ul>
<li><a href="#trunksrcwpincludesdatephp">trunk/src/wp-includes/date.php</a></li>
<li><a href="#trunktestsphpunittestsdatequeryphp">trunk/tests/phpunit/tests/date/query.php</a></li>
<li><a href="#trunktestsphpunittestsquerydateQueryphp">trunk/tests/phpunit/tests/query/dateQuery.php</a></li>
</ul>
</div>
<div id="patch">
<h3>Diff</h3>
<a id="trunksrcwpincludesdatephp"></a>
<div class="modfile"><h4 style="background-color: #eee; color: inherit; margin: 1em 0; padding: 1.3em; font-size: 115%">Modified: trunk/src/wp-includes/date.php</h4>
<pre class="diff"><span>
<span class="info" style="display: block; padding: 0 10px; color: #888">--- trunk/src/wp-includes/date.php 2014-10-16 19:31:09 UTC (rev 29922)
+++ trunk/src/wp-includes/date.php 2014-10-16 19:33:24 UTC (rev 29923)
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -1,8 +1,10 @@
</span><span class="cx" style="display: block; padding: 0 10px"> <?php
</span><span class="cx" style="display: block; padding: 0 10px"> /**
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- * WP_Date_Query will generate a MySQL WHERE clause for the specified date-based parameters.
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * Class for generating SQL clauses that filter a primary query according to date.
</ins><span class="cx" style="display: block; padding: 0 10px"> *
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- * Initialize the class by passing an array of arrays of parameters.
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * `WP_Date_Query` is a helper that allows primary query classes, such as {@see WP_Query},
+ * to filter their results by date columns, by generating `WHERE` subclauses to be attached
+ * to the primary SQL query string.
</ins><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="cx" style="display: block; padding: 0 10px"> * @link http://codex.wordpress.org/Function_Reference/WP_Query Codex page.
</span><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -10,8 +12,10 @@
</span><span class="cx" style="display: block; padding: 0 10px"> */
</span><span class="cx" style="display: block; padding: 0 10px"> class WP_Date_Query {
</span><span class="cx" style="display: block; padding: 0 10px"> /**
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- * List of date queries.
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * Array of date queries.
</ins><span class="cx" style="display: block; padding: 0 10px"> *
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * See {@see WP_Date_Query::__construct()} for information on date query arguments.
+ *
</ins><span class="cx" style="display: block; padding: 0 10px"> * @since 3.7.0
</span><span class="cx" style="display: block; padding: 0 10px"> * @access public
</span><span class="cx" style="display: block; padding: 0 10px"> * @var array
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -19,7 +23,7 @@
</span><span class="cx" style="display: block; padding: 0 10px"> public $queries = array();
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> /**
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- * The relation between the queries. Can be either 'AND' or 'OR' and can be changed via the query arguments.
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * The default relation between top-level queries. Can be either 'AND' or 'OR'.
</ins><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="cx" style="display: block; padding: 0 10px"> * @since 3.7.0
</span><span class="cx" style="display: block; padding: 0 10px"> * @access public
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -46,13 +50,23 @@
</span><span class="cx" style="display: block; padding: 0 10px"> public $compare = '=';
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> /**
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * Supported time-related parameter keys.
+ *
+ * @since 4.1.0
+ * @access public
+ * @var array
+ */
+ public $time_keys = array( 'after', 'before', 'year', 'month', 'monthnum', 'week', 'w', 'dayofyear', 'day', 'dayofweek', 'hour', 'minute', 'second' );
+
+ /**
</ins><span class="cx" style="display: block; padding: 0 10px"> * Constructor.
</span><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="cx" style="display: block; padding: 0 10px"> * @since 3.7.0
</span><span class="cx" style="display: block; padding: 0 10px"> * @since 4.0.0 The $inclusive logic was updated to include all times within the date range.
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * @access public
</ins><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="cx" style="display: block; padding: 0 10px"> * @param array $date_query {
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- * One or more associative arrays of date query parameters.
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * Array of date query clauses.
</ins><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="cx" style="display: block; padding: 0 10px"> * @type array {
</span><span class="cx" style="display: block; padding: 0 10px"> * @type string $column Optional. The column to query against. If undefined, inherits the value of
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -60,11 +74,12 @@
</span><span class="cx" style="display: block; padding: 0 10px"> * 'post_date_gmt', 'post_modified','post_modified_gmt', 'comment_date',
</span><span class="cx" style="display: block; padding: 0 10px"> * 'comment_date_gmt'.
</span><span class="cx" style="display: block; padding: 0 10px"> * @type string $compare Optional. The comparison operator.
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- * Default '='. Accepts '=', '!=', '>', '>=', '<', '<=', 'IN', 'NOT IN',
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * Accepts '=', '!=', '>', '>=', '<', '<=', 'IN', 'NOT IN'. Default '='.
</ins><span class="cx" style="display: block; padding: 0 10px"> * 'BETWEEN', 'NOT BETWEEN'.
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- * @type string $relation Optional. The boolean relationship between the date queryies.
- * Default 'OR'. Accepts 'OR', 'AND'.
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * @type string $relation Optional. The boolean relationship between the date queries.
+ * Accepts 'OR', 'AND'. Default 'OR'.
</ins><span class="cx" style="display: block; padding: 0 10px"> * @type array {
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ Optional. An array of first-order clause parameters, or another fully-formed date query.
</ins><span class="cx" style="display: block; padding: 0 10px"> * @type string|array $before Optional. Date to retrieve posts before. Accepts strtotime()-compatible
</span><span class="cx" style="display: block; padding: 0 10px"> * string, or array of 'year', 'month', 'day' values. {
</span><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -109,44 +124,118 @@
</span><span class="cx" style="display: block; padding: 0 10px"> * 'comment_date', 'comment_date_gmt'.
</span><span class="cx" style="display: block; padding: 0 10px"> */
</span><span class="cx" style="display: block; padding: 0 10px"> public function __construct( $date_query, $default_column = 'post_date' ) {
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- if ( empty( $date_query ) || ! is_array( $date_query ) )
- return;
</del><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- if ( isset( $date_query['relation'] ) && strtoupper( $date_query['relation'] ) == 'OR' )
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ if ( isset( $date_query['relation'] ) && 'OR' === strtoupper( $date_query['relation'] ) ) {
</ins><span class="cx" style="display: block; padding: 0 10px"> $this->relation = 'OR';
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- else
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ } else {
</ins><span class="cx" style="display: block; padding: 0 10px"> $this->relation = 'AND';
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ }
</ins><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- if ( ! empty( $date_query['column'] ) )
- $this->column = esc_sql( $date_query['column'] );
- else
- $this->column = esc_sql( $default_column );
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ if ( ! is_array( $date_query ) ) {
+ return;
+ }
</ins><span class="cx" style="display: block; padding: 0 10px">
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ // Support for passing time-based keys in the top level of the $date_query array.
+ if ( ! isset( $date_query[0] ) && ! empty( $date_query ) ) {
+ $date_query = array( $date_query );
+ }
+
+ if ( empty( $date_query ) ) {
+ return;
+ }
+
+ if ( ! empty( $date_query['column'] ) ) {
+ $date_query['column'] = esc_sql( $date_query['column'] );
+ } else {
+ $date_query['column'] = esc_sql( $default_column );
+ }
+
</ins><span class="cx" style="display: block; padding: 0 10px"> $this->column = $this->validate_column( $this->column );
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> $this->compare = $this->get_compare( $date_query );
</span><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- // If an array of arrays wasn't passed, fix it
- if ( ! isset( $date_query[0] ) )
- $date_query = array( $date_query );
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ $this->queries = $this->sanitize_query( $date_query );
</ins><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- $this->queries = array();
- foreach ( $date_query as $key => $query ) {
- if ( ! is_array( $query ) )
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ return;
+ }
+
+ /**
+ * Recursive-friendly query sanitizer.
+ *
+ * Ensures that each query-level clause has a 'relation' key, and that
+ * each first-order clause contains all the necessary keys from
+ * $defaults.
+ *
+ * @since 4.1.0
+ * @access public
+ *
+ * @param array $query A tax_query query clause.
+ * @return array Sanitized queries.
+ */
+ public function sanitize_query( $queries, $parent_query = null ) {
+ $cleaned_query = array();
+
+ $defaults = array(
+ 'column' => 'post_date',
+ 'compare' => '=',
+ 'relation' => 'AND',
+ );
+
+ // Numeric keys should always have array values.
+ foreach ( $queries as $qkey => $qvalue ) {
+ if ( is_numeric( $qkey ) && ! is_array( $qvalue ) ) {
+ unset( $queries[ $qkey ] );
+ }
+ }
+
+ // Each query should have a value for each default key. Inherit from the parent when possible.
+ foreach ( $defaults as $dkey => $dvalue ) {
+ if ( isset( $queries[ $dkey ] ) ) {
</ins><span class="cx" style="display: block; padding: 0 10px"> continue;
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ }
</ins><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- $this->queries[$key] = $query;
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ if ( isset( $parent_query[ $dkey ] ) ) {
+ $queries[ $dkey ] = $parent_query[ $dkey ];
+ } else {
+ $queries[ $dkey ] = $dvalue;
+ }
</ins><span class="cx" style="display: block; padding: 0 10px"> }
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+
+ foreach ( $queries as $key => $q ) {
+ if ( ! is_array( $q ) || in_array( $key, $this->time_keys, true ) ) {
+ // This is a first-order query. Trust the values and sanitize when building SQL.
+ $cleaned_query[ $key ] = $q;
+ } else {
+ // Any array without a time key is another query, so we recurse.
+ $cleaned_query[] = $this->sanitize_query( $q, $queries );
+ }
+ }
+
+ return $cleaned_query;
</ins><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> /**
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * Determine whether this is a first-order clause.
+ *
+ * Checks to see if the current clause has any time-related keys.
+ * If so, it's first-order.
+ *
+ * @param array $query Query clause.
+ * @return bool True if this is a first-order clause.
+ */
+ protected function is_first_order_clause( $query ) {
+ $time_keys = array_intersect( $this->time_keys, array_keys( $query ) );
+ return ! empty( $time_keys );
+ }
+
+ /**
</ins><span class="cx" style="display: block; padding: 0 10px"> * Determines and validates what comparison operator to use.
</span><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="cx" style="display: block; padding: 0 10px"> * @since 3.7.0
</span><span class="cx" style="display: block; padding: 0 10px"> * @access public
</span><span class="cx" style="display: block; padding: 0 10px"> *
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- * @param array $query A date query or a date subquery
- * @return string The comparison operator
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * @param array $query A date query or a date subquery.
+ * @return string The comparison operator.
</ins><span class="cx" style="display: block; padding: 0 10px"> */
</span><span class="cx" style="display: block; padding: 0 10px"> public function get_compare( $query ) {
</span><span class="cx" style="display: block; padding: 0 10px"> if ( ! empty( $query['compare'] ) && in_array( $query['compare'], array( '=', '!=', '>', '>=', '<', '<=', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ) ) )
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -184,31 +273,18 @@
</span><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> /**
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- * Turns an array of date query parameters into a MySQL string.
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * Generate WHERE clause to be appended to a main query.
</ins><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="cx" style="display: block; padding: 0 10px"> * @since 3.7.0
</span><span class="cx" style="display: block; padding: 0 10px"> * @access public
</span><span class="cx" style="display: block; padding: 0 10px"> *
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- * @return string MySQL WHERE parameters
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * @return string MySQL WHERE clause.
</ins><span class="cx" style="display: block; padding: 0 10px"> */
</span><span class="cx" style="display: block; padding: 0 10px"> public function get_sql() {
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- // The parts of the final query
- $where = array();
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ $sql = $this->get_sql_clauses();
</ins><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- foreach ( $this->queries as $key => $query ) {
- $where_parts = $this->get_sql_for_subquery( $query );
- if ( $where_parts ) {
- // Combine the parts of this subquery into a single string
- $where[ $key ] = '( ' . implode( ' AND ', $where_parts ) . ' )';
- }
- }
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ $where = $sql['where'];
</ins><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- // Combine the subquery strings into a single string
- if ( $where )
- $where = ' AND ( ' . implode( " {$this->relation} ", $where ) . ' )';
- else
- $where = '';
-
</del><span class="cx" style="display: block; padding: 0 10px"> /**
</span><span class="cx" style="display: block; padding: 0 10px"> * Filter the date query WHERE clause.
</span><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -221,15 +297,156 @@
</span><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> /**
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- * Turns a single date subquery into pieces for a WHERE clause.
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * Generate SQL clauses to be appended to a main query.
</ins><span class="cx" style="display: block; padding: 0 10px"> *
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- * @since 3.7.0
- * return array
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * Called by the public {@see WP_Date_Query::get_sql()}, this method
+ * is abstracted out to maintain parity with the other Query classes.
+ *
+ * @since 4.1.0
+ * @access protected
+ *
+ * @return array {
+ * Array containing JOIN and WHERE SQL clauses to append to the main query.
+ *
+ * @type string $join SQL fragment to append to the main JOIN clause.
+ * @type string $where SQL fragment to append to the main WHERE clause.
+ * }
</ins><span class="cx" style="display: block; padding: 0 10px"> */
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ protected function get_sql_clauses() {
+ $sql = $this->get_sql_for_query( $this->queries );
+
+ if ( ! empty( $sql['where'] ) ) {
+ $sql['where'] = ' AND ' . $sql['where'];
+ }
+
+ return $sql;
+ }
+
+ /**
+ * Generate SQL clauses for a single query array.
+ *
+ * If nested subqueries are found, this method recurses the tree to
+ * produce the properly nested SQL.
+ *
+ * @since 4.1.0
+ * @access protected
+ *
+ * @param array $query Query to parse.
+ * @param int $depth Optional. Number of tree levels deep we currently are.
+ * Used to calculate indentation.
+ * @return array {
+ * Array containing JOIN and WHERE SQL clauses to append to a single query array.
+ *
+ * @type string $join SQL fragment to append to the main JOIN clause.
+ * @type string $where SQL fragment to append to the main WHERE clause.
+ * }
+ */
+ protected function get_sql_for_query( $query, $depth = 0 ) {
+ $sql_chunks = array(
+ 'join' => array(),
+ 'where' => array(),
+ );
+
+ $sql = array(
+ 'join' => '',
+ 'where' => '',
+ );
+
+ $indent = '';
+ for ( $i = 0; $i < $depth; $i++ ) {
+ $indent .= " ";
+ }
+
+ foreach ( $query as $key => $clause ) {
+ if ( 'relation' === $key ) {
+ $relation = $query['relation'];
+ } else if ( is_array( $clause ) ) {
+
+ // This is a first-order clause.
+ if ( $this->is_first_order_clause( $clause ) ) {
+ $clause_sql = $this->get_sql_for_clause( $clause, $query );
+
+ $where_count = count( $clause_sql['where'] );
+ if ( ! $where_count ) {
+ $sql_chunks['where'][] = '';
+ } else if ( 1 === $where_count ) {
+ $sql_chunks['where'][] = $clause_sql['where'][0];
+ } else {
+ $sql_chunks['where'][] = '( ' . implode( ' AND ', $clause_sql['where'] ) . ' )';
+ }
+
+ $sql_chunks['join'] = array_merge( $sql_chunks['join'], $clause_sql['join'] );
+ // This is a subquery, so we recurse.
+ } else {
+ $clause_sql = $this->get_sql_for_query( $clause, $depth + 1 );
+
+ $sql_chunks['where'][] = $clause_sql['where'];
+ $sql_chunks['join'][] = $clause_sql['join'];
+ }
+ }
+ }
+
+ // Filter to remove empties.
+ $sql_chunks['join'] = array_filter( $sql_chunks['join'] );
+ $sql_chunks['where'] = array_filter( $sql_chunks['where'] );
+
+ if ( empty( $relation ) ) {
+ $relation = 'AND';
+ }
+
+ // Filter duplicate JOIN clauses and combine into a single string.
+ if ( ! empty( $sql_chunks['join'] ) ) {
+ $sql['join'] = implode( ' ', array_unique( $sql_chunks['join'] ) );
+ }
+
+ // Generate a single WHERE clause with proper brackets and indentation.
+ if ( ! empty( $sql_chunks['where'] ) ) {
+ $sql['where'] = '( ' . "\n " . $indent . implode( ' ' . "\n " . $indent . $relation . ' ' . "\n " . $indent, $sql_chunks['where'] ) . "\n" . $indent . ')';
+ }
+
+ return $sql;
+ }
+
+ /**
+ * Turns a single date clause into pieces for a WHERE clause.
+ *
+ * A wrapper for get_sql_for_clause(), included here for backward
+ * compatibility while retaining the naming convention across Query classes.
+ *
+ * @since 3.7.0
+ * @access protected
+ *
+ * @param array $query Date query arguments.
+ * @return array {
+ * Array containing JOIN and WHERE SQL clauses to append to the main query.
+ *
+ * @type string $join SQL fragment to append to the main JOIN clause.
+ * @type string $where SQL fragment to append to the main WHERE clause.
+ * }
+ */
</ins><span class="cx" style="display: block; padding: 0 10px"> protected function get_sql_for_subquery( $query ) {
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ return $this->get_sql_for_clause( $query, '' );
+ }
+
+ /**
+ * Turns a first-order date query into SQL for a WHERE clause.
+ *
+ * @since 4.1.0
+ * @access protected
+ *
+ * @param array $query Date query clause.
+ * @param array $parent_query Parent query of the current date query.
+ * @return array {
+ * Array containing JOIN and WHERE SQL clauses to append to the main query.
+ *
+ * @type string $join SQL fragment to append to the main JOIN clause.
+ * @type string $where SQL fragment to append to the main WHERE clause.
+ * }
+ */
+ protected function get_sql_for_clause( $query, $parent_query ) {
</ins><span class="cx" style="display: block; padding: 0 10px"> global $wpdb;
</span><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- // The sub-parts of a $where part
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ // The sub-parts of a $where part.
</ins><span class="cx" style="display: block; padding: 0 10px"> $where_parts = array();
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> $column = ( ! empty( $query['column'] ) ) ? esc_sql( $query['column'] ) : $this->column;
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -249,14 +466,14 @@
</span><span class="cx" style="display: block; padding: 0 10px"> $gt .= '=';
</span><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- // Range queries
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ // Range queries.
</ins><span class="cx" style="display: block; padding: 0 10px"> if ( ! empty( $query['after'] ) )
</span><span class="cx" style="display: block; padding: 0 10px"> $where_parts[] = $wpdb->prepare( "$column $gt %s", $this->build_mysql_datetime( $query['after'], ! $inclusive ) );
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> if ( ! empty( $query['before'] ) )
</span><span class="cx" style="display: block; padding: 0 10px"> $where_parts[] = $wpdb->prepare( "$column $lt %s", $this->build_mysql_datetime( $query['before'], $inclusive ) );
</span><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- // Specific value queries
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ // Specific value queries.
</ins><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> if ( isset( $query['year'] ) && $value = $this->build_value( $compare, $query['year'] ) )
</span><span class="cx" style="display: block; padding: 0 10px"> $where_parts[] = "YEAR( $column ) $compare $value";
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -281,10 +498,10 @@
</span><span class="cx" style="display: block; padding: 0 10px"> $where_parts[] = "DAYOFWEEK( $column ) $compare $value";
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> if ( isset( $query['hour'] ) || isset( $query['minute'] ) || isset( $query['second'] ) ) {
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- // Avoid notices
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ // Avoid notices.
</ins><span class="cx" style="display: block; padding: 0 10px"> foreach ( array( 'hour', 'minute', 'second' ) as $unit ) {
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- if ( ! isset( $query[$unit] ) ) {
- $query[$unit] = null;
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ if ( ! isset( $query[ $unit ] ) ) {
+ $query[ $unit ] = null;
</ins><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -293,7 +510,14 @@
</span><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- return $where_parts;
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ /*
+ * Return an array of 'join' and 'where' for compatibility
+ * with other query classes.
+ */
+ return array(
+ 'where' => $where_parts,
+ 'join' => array(),
+ );
</ins><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> /**
</span></span></pre></div>
<a id="trunktestsphpunittestsdatequeryphp"></a>
<div class="modfile"><h4 style="background-color: #eee; color: inherit; margin: 1em 0; padding: 1.3em; font-size: 115%">Modified: trunk/tests/phpunit/tests/date/query.php</h4>
<pre class="diff"><span>
<span class="info" style="display: block; padding: 0 10px; color: #888">--- trunk/tests/phpunit/tests/date/query.php 2014-10-16 19:31:09 UTC (rev 29922)
+++ trunk/tests/phpunit/tests/date/query.php 2014-10-16 19:33:24 UTC (rev 29923)
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -55,7 +55,13 @@
</span><span class="cx" style="display: block; padding: 0 10px"> 'year' => 2008,
</span><span class="cx" style="display: block; padding: 0 10px"> 'month' => 6,
</span><span class="cx" style="display: block; padding: 0 10px"> ),
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ 'column' => 'post_date',
+ 'compare' => '=',
+ 'relation' => 'AND',
</ins><span class="cx" style="display: block; padding: 0 10px"> ),
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ 'column' => 'post_date',
+ 'compare' => '=',
+ 'relation' => 'AND',
</ins><span class="cx" style="display: block; padding: 0 10px"> );
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> $this->assertSame( $expected, $q->queries );
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -73,17 +79,22 @@
</span><span class="cx" style="display: block; padding: 0 10px"> ),
</span><span class="cx" style="display: block; padding: 0 10px"> ) );
</span><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- // Note: WP_Date_Query does not reset indexes
</del><span class="cx" style="display: block; padding: 0 10px"> $expected = array(
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- 2 => array(
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ array(
</ins><span class="cx" style="display: block; padding: 0 10px"> 'before' => array(
</span><span class="cx" style="display: block; padding: 0 10px"> 'year' => 2008,
</span><span class="cx" style="display: block; padding: 0 10px"> 'month' => 6,
</span><span class="cx" style="display: block; padding: 0 10px"> ),
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ 'column' => 'post_date',
+ 'compare' => '=',
+ 'relation' => 'AND',
</ins><span class="cx" style="display: block; padding: 0 10px"> ),
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ 'column' => 'post_date',
+ 'compare' => '=',
+ 'relation' => 'AND',
</ins><span class="cx" style="display: block; padding: 0 10px"> );
</span><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- $this->assertSame( $expected, $q->queries );
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ $this->assertEquals( $expected, $q->queries );
</ins><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> public function test_get_compare_empty() {
</span></span></pre></div>
<a id="trunktestsphpunittestsquerydateQueryphp"></a>
<div class="modfile"><h4 style="background-color: #eee; color: inherit; margin: 1em 0; padding: 1.3em; font-size: 115%">Modified: trunk/tests/phpunit/tests/query/dateQuery.php</h4>
<pre class="diff"><span>
<span class="info" style="display: block; padding: 0 10px; color: #888">--- trunk/tests/phpunit/tests/query/dateQuery.php 2014-10-16 19:31:09 UTC (rev 29922)
+++ trunk/tests/phpunit/tests/query/dateQuery.php 2014-10-16 19:33:24 UTC (rev 29923)
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -629,9 +629,8 @@
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> $this->assertEquals( $expected_dates, wp_list_pluck( $posts, 'post_date' ) );
</span><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- $this->assertContains( "AND ( ( MONTH( post_date ) = 5 ) ) AND", $this->q->request );
-
- $this->assertNotContains( "AND ( ( MONTH( post_date ) = 5 AND MONTH( post_date ) = 9 ) ) AND", $this->q->request );
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ $this->assertContains( "MONTH( post_date ) = 5", $this->q->request );
+ $this->assertNotContains( "MONTH( post_date ) = 9", $this->q->request );
</ins><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> public function test_date_params_week_w_duplicate() {
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -653,11 +652,160 @@
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> $this->assertEquals( $expected_dates, wp_list_pluck( $posts, 'post_date' ) );
</span><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- $this->assertContains( "AND ( ( WEEK( post_date, 1 ) = 21 ) ) AND", $this->q->request );
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ $this->assertContains( "WEEK( post_date, 1 ) = 21", $this->q->request );
+ $this->assertNotContains( "WEEK( post_date, 1 ) = 22", $this->q->request );
+ }
</ins><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- $this->assertNotContains( "AND ( ( WEEK( post_date, 1 ) = 21 AND WEEK( post_date, 1 ) = 22 ) ) AND", $this->q->request );
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ /**
+ * @ticket 29822
+ */
+ public function test_date_query_one_nested_query() {
+ $this->create_posts();
+
+ $posts = $this->_get_query_result( array(
+ 'date_query' => array(
+ 'relation' => 'OR',
+ array(
+ 'relation' => 'AND',
+ array(
+ 'year' => 2004,
+ ),
+ array(
+ 'month' => 1,
+ ),
+ ),
+ array(
+ 'year' => 1984,
+ ),
+ ),
+ ) );
+
+ $expected_dates = array(
+ '1984-07-28 19:28:56',
+ '2004-01-03 08:54:10',
+ );
+
+ $this->assertEquals( $expected_dates, wp_list_pluck( $posts, 'post_date' ) );
</ins><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px">
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ /**
+ * @ticket 29822
+ */
+ public function test_date_query_one_nested_query_multiple_columns_relation_and() {
+ $p1 = $this->factory->post->create( array(
+ 'post_date' => '2012-03-05 15:30:55',
+ ) );
+ $this->update_post_modified( $p1, '2014-11-03 14:43:00' );
+
+ $p2 = $this->factory->post->create( array(
+ 'post_date' => '2012-05-05 15:30:55',
+ ) );
+ $this->update_post_modified( $p2, '2014-10-03 14:43:00' );
+
+ $p3 = $this->factory->post->create( array(
+ 'post_date' => '2013-05-05 15:30:55',
+ ) );
+ $this->update_post_modified( $p3, '2014-10-03 14:43:00' );
+
+ $p4 = $this->factory->post->create( array(
+ 'post_date' => '2012-02-05 15:30:55',
+ ) );
+ $this->update_post_modified( $p4, '2012-12-03 14:43:00' );
+
+ $q = new WP_Query( array(
+ 'date_query' => array(
+ 'relation' => 'AND',
+ array(
+ 'column' => 'post_date',
+ array(
+ 'year' => 2012,
+ ),
+ ),
+ array(
+ 'column' => 'post_modified',
+ array(
+ 'year' => 2014,
+ ),
+ ),
+ ),
+ 'fields' => 'ids',
+ 'update_post_meta_cache' => false,
+ 'update_post_term_cache' => false,
+ 'post_status' => 'publish',
+ ) );
+
+ $expected = array( $p1, $p2, );
+
+ $this->assertEqualSets( $expected, $q->posts );
+ }
+
+ /**
+ * @ticket 29822
+ */
+ public function test_date_query_nested_query_multiple_columns_mixed_relations() {
+ $p1 = $this->factory->post->create( array(
+ 'post_date' => '2012-03-05 15:30:55',
+ ) );
+ $this->update_post_modified( $p1, '2014-11-03 14:43:00' );
+
+ $p2 = $this->factory->post->create( array(
+ 'post_date' => '2012-05-05 15:30:55',
+ ) );
+ $this->update_post_modified( $p2, '2014-10-03 14:43:00' );
+
+ $p3 = $this->factory->post->create( array(
+ 'post_date' => '2013-05-05 15:30:55',
+ ) );
+ $this->update_post_modified( $p3, '2014-10-03 14:43:00' );
+
+ $p4 = $this->factory->post->create( array(
+ 'post_date' => '2012-02-05 15:30:55',
+ ) );
+ $this->update_post_modified( $p4, '2012-12-03 14:43:00' );
+
+ $p5 = $this->factory->post->create( array(
+ 'post_date' => '2014-02-05 15:30:55',
+ ) );
+ $this->update_post_modified( $p5, '2013-12-03 14:43:00' );
+
+ $q = new WP_Query( array(
+ 'date_query' => array(
+ 'relation' => 'OR',
+ array(
+ 'relation' => 'AND',
+ array(
+ 'column' => 'post_date',
+ array(
+ 'day' => 05,
+ ),
+ ),
+ array(
+ 'column' => 'post_date',
+ array(
+ 'before' => array(
+ 'year' => 2012,
+ 'month' => 4,
+ ),
+ ),
+ ),
+ ),
+ array(
+ 'column' => 'post_modified',
+ array(
+ 'month' => 12,
+ ),
+ ),
+ ),
+ 'fields' => 'ids',
+ 'update_post_meta_cache' => false,
+ 'update_post_term_cache' => false,
+ 'post_status' => 'publish',
+ ) );
+
+ $expected = array( $p1, $p4, $p5, );
+ $this->assertEqualSets( $expected, $q->posts );
+ }
+
</ins><span class="cx" style="display: block; padding: 0 10px"> /** Helpers **********************************************************/
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> protected function create_posts() {
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -692,4 +840,28 @@
</span><span class="cx" style="display: block; padding: 0 10px"> $this->factory->post->create( array( 'post_date' => $post_date ) );
</span><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px"> }
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+
+ /**
+ * There's no way to change post_modified through the API.
+ */
+ protected function update_post_modified( $post_id, $date ) {
+ global $wpdb;
+ return $wpdb->update(
+ $wpdb->posts,
+ array(
+ 'post_modified' => $date,
+ 'post_modified_gmt' => $date,
+ ),
+ array(
+ 'ID' => $post_id,
+ ),
+ array(
+ '%s',
+ '%s',
+ ),
+ array(
+ '%d',
+ )
+ );
+ }
</ins><span class="cx" style="display: block; padding: 0 10px"> }
</span></span></pre>
</div>
</div>
</body>
</html>