<!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>[31663] trunk: Improve 'orderby' syntax for `WP_User_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/31663">31663</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/31663","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>2015-03-07 16:05:11 +0000 (Sat, 07 Mar 2015)</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'>Improve 'orderby' syntax for `WP_User_Query`.
This changeset ports a number of 'orderby' features from `WP_Query` and
`WP_Comment_Query`:
* Allow multiple 'orderby' values to be passed as a space-separated list.
* Allow multiple 'orderby' values to be passed as a flat array.
* Allow multi-dimensional 'orderby', with orderby fields as array keys and ASC/DESC as the corresponding values.
See <a href="https://core.trac.wordpress.org/ticket/31265">#31265</a>.</pre>
<h3>Modified Paths</h3>
<ul>
<li><a href="#trunksrcwpincludesuserphp">trunk/src/wp-includes/user.php</a></li>
<li><a href="#trunktestsphpunittestsuserqueryphp">trunk/tests/phpunit/tests/user/query.php</a></li>
</ul>
</div>
<div id="patch">
<h3>Diff</h3>
<a id="trunksrcwpincludesuserphp"></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/user.php</h4>
<pre class="diff"><span>
<span class="info" style="display: block; padding: 0 10px; color: #888">--- trunk/src/wp-includes/user.php 2015-03-07 15:44:28 UTC (rev 31662)
+++ trunk/src/wp-includes/user.php 2015-03-07 16:05:11 UTC (rev 31663)
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -500,7 +500,8 @@
</span><span class="cx" style="display: block; padding: 0 10px"> * Prepare the query variables.
</span><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="cx" style="display: block; padding: 0 10px"> * @since 3.1.0
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- * @since 4.2.0 Added 'meta_value_num' support for `$orderby` parameter.
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * @since 4.2.0 Added 'meta_value_num' support for `$orderby` parameter. Added multi-dimensional array syntax
+ * for `$orderby` parameter.
</ins><span class="cx" style="display: block; padding: 0 10px"> * @access public
</span><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="cx" style="display: block; padding: 0 10px"> * @param string|array $query {
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -521,13 +522,18 @@
</span><span class="cx" style="display: block; padding: 0 10px"> * column to search in based on search string. Default empty.
</span><span class="cx" style="display: block; padding: 0 10px"> * @type array $search_columns Array of column names to be searched. Accepts 'ID', 'login',
</span><span class="cx" style="display: block; padding: 0 10px"> * 'nicename', 'email', 'url'. Default empty array.
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- * @type string $orderby Field to sort the retrieved users by. Accepts 'ID', 'display_name',
- * 'login', 'nicename', 'email', 'url', 'registered', 'post_count',
- * 'meta_value' or 'meta_value_num'. To use 'meta_value' or
- * 'meta_value_num', `$meta_key` must be also be defined.
- * Default 'user_login'.
- * @type string $order Designates ascending or descending order of users. Accepts 'ASC',
- * 'DESC'. Default 'ASC'.
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * @type string|array $orderby Field to sort the retrieved users by. May be a single value,
+ * an array of values, or a multi-dimensional array with fields as keys
+ * and orders ('ASC' or 'DESC') as values. Accepted values are'ID',
+ * 'display_name' (or 'name'), 'user_login' (or 'login'),
+ * 'user_nicename' (or 'nicename'), 'user_email' (or 'email'),
+ * 'user_url' (or 'url'), 'user_registered' (or 'registered'),
+ * 'post_count', 'meta_value', or 'meta_value_num'. To use 'meta_value'
+ * or 'meta_value_num', `$meta_key` must be also be defined.
+ * Default 'user_login'.
+ * @type string $order Designates ascending or descending order of users. Order values
+ * passed as part of an `$orderby` array take precedence over this
+ * parameter. Accepts 'ASC', 'DESC'. Default 'ASC'.
</ins><span class="cx" style="display: block; padding: 0 10px"> * @type int $offset Number of users to offset in retrieved results. Can be used in
</span><span class="cx" style="display: block; padding: 0 10px"> * conjunction with pagination. Default 0.
</span><span class="cx" style="display: block; padding: 0 10px"> * @type int $number Number of users to limit the query for. Can be used in conjunction
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -611,48 +617,50 @@
</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"> // sorting
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- if ( isset( $qv['orderby'] ) ) {
- if ( in_array( $qv['orderby'], array('nicename', 'email', 'url', 'registered') ) ) {
- $orderby = 'user_' . $qv['orderby'];
- } elseif ( in_array( $qv['orderby'], array('user_nicename', 'user_email', 'user_url', 'user_registered') ) ) {
- $orderby = $qv['orderby'];
- } elseif ( 'name' == $qv['orderby'] || 'display_name' == $qv['orderby'] ) {
- $orderby = 'display_name';
- } elseif ( 'post_count' == $qv['orderby'] ) {
- // todo: avoid the JOIN
- $where = get_posts_by_author_sql('post');
- $this->query_from .= " LEFT OUTER JOIN (
- SELECT post_author, COUNT(*) as post_count
- FROM $wpdb->posts
- $where
- GROUP BY post_author
- ) p ON ({$wpdb->users}.ID = p.post_author)
- ";
- $orderby = 'post_count';
- } elseif ( 'ID' == $qv['orderby'] || 'id' == $qv['orderby'] ) {
- $orderby = 'ID';
- } elseif ( 'meta_value' == $qv['orderby'] ) {
- $orderby = "$wpdb->usermeta.meta_value";
- } elseif ( 'meta_value_num' == $qv['orderby'] ) {
- $orderby = "$wpdb->usermeta.meta_value+0";
- } elseif ( 'include' === $qv['orderby'] && ! empty( $include ) ) {
- // Sanitized earlier.
- $include_sql = implode( ',', $include );
- $orderby = "FIELD( $wpdb->users.ID, $include_sql )";
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ $qv['order'] = isset( $qv['order'] ) ? strtoupper( $qv['order'] ) : '';
+ $order = $this->parse_order( $qv['order'] );
+
+ if ( empty( $qv['orderby'] ) ) {
+ // Default order is by 'user_login'.
+ $ordersby = array( 'user_login' => $order );
+ } else if ( is_array( $qv['orderby'] ) ) {
+ $ordersby = $qv['orderby'];
+ } else {
+ // 'orderby' values may be a comma- or space-separated list.
+ $ordersby = preg_split( '/[,\s]+/', $qv['orderby'] );
+ }
+
+ $orderby_array = array();
+ foreach ( $ordersby as $_key => $_value ) {
+ if ( ! $_value ) {
+ continue;
+ }
+
+ if ( is_int( $_key ) ) {
+ // Integer key means this is a flat array of 'orderby' fields.
+ $_orderby = $_value;
+ $_order = $order;
</ins><span class="cx" style="display: block; padding: 0 10px"> } else {
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- $orderby = 'user_login';
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ // Non-integer key means this the key is the field and the value is ASC/DESC.
+ $_orderby = $_key;
+ $_order = $_value;
</ins><span class="cx" style="display: block; padding: 0 10px"> }
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+
+ $parsed = $this->parse_orderby( $_orderby );
+
+ if ( ! $parsed ) {
+ continue;
+ }
+
+ $orderby_array[] = $parsed . ' ' . $this->parse_order( $_order );
</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">- if ( empty( $orderby ) )
- $orderby = 'user_login';
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ // If no valid clauses were found, order by user_login.
+ if ( empty( $orderby_array ) ) {
+ $orderby_array[] = "user_login $order";
+ }
</ins><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- $qv['order'] = isset( $qv['order'] ) ? strtoupper( $qv['order'] ) : '';
- if ( 'ASC' == $qv['order'] )
- $order = 'ASC';
- else
- $order = 'DESC';
- $this->query_orderby = "ORDER BY $orderby $order";
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ $this->query_orderby = 'ORDER BY ' . implode( ', ', $orderby_array );
</ins><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> // limit
</span><span class="cx" style="display: block; padding: 0 10px"> if ( isset( $qv['number'] ) && $qv['number'] ) {
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -925,6 +933,74 @@
</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><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * Parse and sanitize 'orderby' keys passed to the user query.
+ *
+ * @since 4.2.0
+ * @access protected
+ *
+ * @global wpdb $wpdb WordPress database abstraction object.
+ *
+ * @param string $orderby Alias for the field to order by.
+ * @return string|bool Value to used in the ORDER clause, if `$orderby` is valid. False otherwise.
+ */
+ protected function parse_orderby( $orderby ) {
+ global $wpdb;
+
+ $_orderby = '';
+ if ( in_array( $orderby, array( 'login', 'nicename', 'email', 'url', 'registered' ) ) ) {
+ $_orderby = 'user_' . $orderby;
+ } elseif ( in_array( $orderby, array( 'user_login', 'user_nicename', 'user_email', 'user_url', 'user_registered' ) ) ) {
+ $_orderby = $orderby;
+ } elseif ( 'name' == $orderby || 'display_name' == $orderby ) {
+ $_orderby = 'display_name';
+ } elseif ( 'post_count' == $orderby ) {
+ // todo: avoid the JOIN
+ $where = get_posts_by_author_sql( 'post' );
+ $this->query_from .= " LEFT OUTER JOIN (
+ SELECT post_author, COUNT(*) as post_count
+ FROM $wpdb->posts
+ $where
+ GROUP BY post_author
+ ) p ON ({$wpdb->users}.ID = p.post_author)
+ ";
+ $_orderby = 'post_count';
+ } elseif ( 'ID' == $orderby || 'id' == $orderby ) {
+ $_orderby = 'ID';
+ } elseif ( 'meta_value' == $orderby ) {
+ $_orderby = "$wpdb->usermeta.meta_value";
+ } elseif ( 'meta_value_num' == $orderby ) {
+ $_orderby = "$wpdb->usermeta.meta_value+0";
+ } elseif ( 'include' === $orderby && ! empty( $this->query_vars['include'] ) ) {
+ $include = wp_parse_id_list( $this->query_vars['include'] );
+ $include_sql = implode( ',', $include );
+ $_orderby = "FIELD( $wpdb->users.ID, $include_sql )";
+ }
+
+ return $_orderby;
+ }
+
+ /**
+ * Parse an 'order' query variable and cast it to ASC or DESC as necessary.
+ *
+ * @since 4.2.0
+ * @access protected
+ *
+ * @param string $order The 'order' query variable.
+ * @return string The sanitized 'order' query variable.
+ */
+ protected function parse_order( $order ) {
+ if ( ! is_string( $order ) || empty( $order ) ) {
+ return 'DESC';
+ }
+
+ if ( 'ASC' === strtoupper( $order ) ) {
+ return 'ASC';
+ } else {
+ return 'DESC';
+ }
+ }
+
+ /**
</ins><span class="cx" style="display: block; padding: 0 10px"> * Make private properties readable for backwards compatibility.
</span><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="cx" style="display: block; padding: 0 10px"> * @since 4.0.0
</span></span></pre></div>
<a id="trunktestsphpunittestsuserqueryphp"></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/user/query.php</h4>
<pre class="diff"><span>
<span class="info" style="display: block; padding: 0 10px; color: #888">--- trunk/tests/phpunit/tests/user/query.php 2015-03-07 15:44:28 UTC (rev 31662)
+++ trunk/tests/phpunit/tests/user/query.php 2015-03-07 16:05:11 UTC (rev 31663)
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -231,6 +231,93 @@
</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><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * @ticket 31265
+ */
+ public function test_orderby_space_separated() {
+ global $wpdb;
+
+ $q = new WP_User_Query( array(
+ 'orderby' => 'login nicename',
+ 'order' => 'ASC',
+ ) );
+
+ $this->assertContains( "ORDER BY user_login ASC, user_nicename ASC", $q->query_orderby );
+ }
+
+ /**
+ * @ticket 31265
+ */
+ public function test_orderby_flat_array() {
+ global $wpdb;
+
+ $q = new WP_User_Query( array(
+ 'orderby' => array( 'login', 'nicename' ),
+ ) );
+
+ $this->assertContains( "ORDER BY user_login ASC, user_nicename ASC", $q->query_orderby );
+ }
+
+ /**
+ * @ticket 31265
+ */
+ public function test_orderby_array_contains_invalid_item() {
+ global $wpdb;
+
+ $q = new WP_User_Query( array(
+ 'orderby' => array( 'login', 'foo', 'nicename' ),
+ ) );
+
+ $this->assertContains( "ORDER BY user_login ASC, user_nicename ASC", $q->query_orderby );
+ }
+
+ /**
+ * @ticket 31265
+ */
+ public function test_orderby_array_contains_all_invalid_items() {
+ global $wpdb;
+
+ $q = new WP_User_Query( array(
+ 'orderby' => array( 'foo', 'bar', 'baz' ),
+ ) );
+
+ $this->assertContains( "ORDER BY user_login", $q->query_orderby );
+ }
+
+ /**
+ * @ticket 31265
+ */
+ public function test_orderby_array() {
+ global $wpdb;
+
+ $q = new WP_User_Query( array(
+ 'orderby' => array(
+ 'login' => 'DESC',
+ 'nicename' => 'ASC',
+ 'email' => 'DESC',
+ ),
+ ) );
+
+ $this->assertContains( "ORDER BY user_login DESC, user_nicename ASC, user_email DESC", $q->query_orderby );
+ }
+
+ /**
+ * @ticket 31265
+ */
+ public function test_orderby_array_should_discard_invalid_columns() {
+ global $wpdb;
+
+ $q = new WP_User_Query( array(
+ 'orderby' => array(
+ 'login' => 'DESC',
+ 'foo' => 'ASC',
+ 'email' => 'ASC',
+ ),
+ ) );
+
+ $this->assertContains( "ORDER BY user_login DESC, user_email ASC", $q->query_orderby );
+ }
+
+ /**
</ins><span class="cx" style="display: block; padding: 0 10px"> * @ticket 21119
</span><span class="cx" style="display: block; padding: 0 10px"> */
</span><span class="cx" style="display: block; padding: 0 10px"> function test_prepare_query() {
</span></span></pre>
</div>
</div>
</body>
</html>