<!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" /><style type="text/css"><!--
#msg dl { border: 1px #006 solid; background: #369; padding: 6px; color: #fff; }
#msg dt { float: left; width: 6em; font-weight: bold; }
#msg dt:after { content:':';}
#msg dl, #msg dt, #msg ul, #msg li, #header, #footer { 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 #fc0 solid; padding: 6px; }
#msg ul, pre { overflow: auto; }
#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>
<title>[16413] trunk/wp-includes: Optimize get_tax_sql().</title>
</head>
<body>
<div id="msg">
<dl>
<dt>Revision</dt> <dd><a href="http://trac.wordpress.org/changeset/16413">16413</a></dd>
<dt>Author</dt> <dd>scribu</dd>
<dt>Date</dt> <dd>2010-11-17 01:56:01 +0000 (Wed, 17 Nov 2010)</dd>
</dl>
<h3>Log Message</h3>
<pre>Optimize get_tax_sql(). See <a href="http://trac.wordpress.org/ticket/12891">#12891</a></pre>
<h3>Modified Paths</h3>
<ul>
<li><a href="#trunkwpincludesqueryphp">trunk/wp-includes/query.php</a></li>
<li><a href="#trunkwpincludestaxonomyphp">trunk/wp-includes/taxonomy.php</a></li>
</ul>
</div>
<div id="patch">
<h3>Diff</h3>
<a id="trunkwpincludesqueryphp"></a>
<div class="modfile"><h4>Modified: trunk/wp-includes/query.php (16412 => 16413)</h4>
<pre class="diff"><span>
<span class="info">--- trunk/wp-includes/query.php 2010-11-17 00:56:00 UTC (rev 16412)
+++ trunk/wp-includes/query.php 2010-11-17 01:56:01 UTC (rev 16413)
</span><span class="lines">@@ -1933,6 +1933,11 @@
</span><span class="cx"> // Taxonomies
</span><span class="cx"> $q['tax_query'] = $this->parse_tax_query( $q );
</span><span class="cx"> if ( !empty( $q['tax_query'] ) ) {
</span><ins>+ $clauses = call_user_func_array( 'get_tax_sql', array( $q['tax_query'], $wpdb->posts, 'ID', &$this) );
+
+ $join .= $clauses['join'];
+ $where .= $clauses['where'];
+
</ins><span class="cx"> if ( empty($post_type) ) {
</span><span class="cx"> $post_type = 'any';
</span><span class="cx"> $post_status_join = true;
</span><span class="lines">@@ -1940,8 +1945,6 @@
</span><span class="cx"> $post_status_join = true;
</span><span class="cx"> }
</span><span class="cx">
</span><del>- $where .= get_tax_sql( $q['tax_query'], "$wpdb->posts.ID" );
-
</del><span class="cx"> // Back-compat
</span><span class="cx"> $tax_query_in = wp_list_filter( $q['tax_query'], array( 'operator' => 'IN' ) );
</span><span class="cx"> if ( !empty( $tax_query_in ) ) {
</span></span></pre></div>
<a id="trunkwpincludestaxonomyphp"></a>
<div class="modfile"><h4>Modified: trunk/wp-includes/taxonomy.php (16412 => 16413)</h4>
<pre class="diff"><span>
<span class="info">--- trunk/wp-includes/taxonomy.php 2010-11-17 00:56:00 UTC (rev 16412)
+++ trunk/wp-includes/taxonomy.php 2010-11-17 01:56:01 UTC (rev 16413)
</span><span class="lines">@@ -460,98 +460,43 @@
</span><span class="cx"> * @uses $wpdb
</span><span class="cx"> * @uses wp_parse_args() Creates an array from string $args.
</span><span class="cx"> *
</span><del>- * @param mixed $terms Term id/slug/name or array of such to match against
</del><ins>+ * @param int|array $term_ids Term id or array of term ids of terms that will be used
</ins><span class="cx"> * @param string|array $taxonomies String of taxonomy name or Array of string values of taxonomy names
</span><del>- * @param array|string $args
- * 'include_children' bool Whether to include term children (hierarchical taxonomies only)
- * 'field' string Which term field is being used. Can be 'term_id', 'slug' or 'name'
- * 'operator' string Can be 'IN' and 'NOT IN'
- * 'do_query' bool Whether to execute the query or return the SQL string
- *
- * @return WP_Error If the taxonomy does not exist
- * @return array The list of found object_ids
- * @return string The SQL string, if do_query is set to false
</del><ins>+ * @param array|string $args Change the order of the object_ids, either ASC or DESC
+ * @return WP_Error|array If the taxonomy does not exist, then WP_Error will be returned. On success
+ * the array can be empty meaning that there are no $object_ids found or it will return the $object_ids found.
</ins><span class="cx"> */
</span><del>-function get_objects_in_term( $terms, $taxonomies, $args = array() ) {
</del><ins>+function get_objects_in_term( $term_ids, $taxonomies, $args = array() ) {
</ins><span class="cx"> global $wpdb;
</span><span class="cx">
</span><del>- extract( wp_parse_args( $args, array(
- 'include_children' => false,
- 'field' => 'term_id',
- 'operator' => 'IN',
- 'do_query' => true,
- ) ), EXTR_SKIP );
</del><ins>+ if ( ! is_array( $term_ids ) )
+ $term_ids = array( $term_ids );
</ins><span class="cx">
</span><del>- $taxonomies = (array) $taxonomies;
</del><ins>+ if ( ! is_array( $taxonomies ) )
+ $taxonomies = array( $taxonomies );
</ins><span class="cx">
</span><del>- foreach ( $taxonomies as $taxonomy ) {
</del><ins>+ foreach ( (array) $taxonomies as $taxonomy ) {
</ins><span class="cx"> if ( ! taxonomy_exists( $taxonomy ) )
</span><del>- return new WP_Error( 'invalid_taxonomy', sprintf( __( 'Invalid Taxonomy: %s' ), $taxonomy ) );
</del><ins>+ return new WP_Error( 'invalid_taxonomy', __( 'Invalid Taxonomy' ) );
</ins><span class="cx"> }
</span><span class="cx">
</span><del>- if ( !in_array( $field, array( 'term_id', 'slug', 'name' ) ) )
- $field = 'term_id';
</del><ins>+ $defaults = array( 'order' => 'ASC' );
+ $args = wp_parse_args( $args, $defaults );
+ extract( $args, EXTR_SKIP );
</ins><span class="cx">
</span><del>- if ( !in_array( $operator, array( 'IN', 'NOT IN' ) ) )
- $operator = 'IN';
</del><ins>+ $order = ( 'desc' == strtolower( $order ) ) ? 'DESC' : 'ASC';
</ins><span class="cx">
</span><del>- $terms = array_unique( (array) $terms );
</del><ins>+ $term_ids = array_map('intval', $term_ids );
</ins><span class="cx">
</span><del>- if ( is_taxonomy_hierarchical( $taxonomy ) && $include_children ) {
- $children = array();
- foreach ( $terms as $term ) {
- if ( 'term_id' != $field ) {
- if ( $term = get_term_by( $field, $term, $taxonomy ) )
- $term = $term->term_id;
- else
- continue;
- }
- $children = array_merge( $children, get_term_children( $term, $taxonomy ) );
- $children[] = $term;
- }
- $terms = $children;
- $field = 'term_id';
- }
-
- if ( empty( $terms ) )
- return $do_query ? array() : '';
-
</del><span class="cx"> $taxonomies = "'" . implode( "', '", $taxonomies ) . "'";
</span><ins>+ $term_ids = "'" . implode( "', '", $term_ids ) . "'";
</ins><span class="cx">
</span><del>- switch ( $field ) {
- case 'term_id':
- $terms = array_map( 'intval', $terms );
</del><ins>+ $object_ids = $wpdb->get_col("SELECT tr.object_id FROM $wpdb->term_relationships AS tr INNER JOIN $wpdb->term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ($taxonomies) AND tt.term_id IN ($term_ids) ORDER BY tr.object_id $order");
</ins><span class="cx">
</span><del>- $terms = implode( ',', $terms );
- $sql = "
- SELECT object_id
- FROM $wpdb->term_relationships
- INNER JOIN $wpdb->term_taxonomy USING (term_taxonomy_id)
- WHERE taxonomy IN ($taxonomies)
- AND term_id $operator ($terms)
- ";
- break;
</del><ins>+ if ( ! $object_ids )
+ return array();
</ins><span class="cx">
</span><del>- case 'slug':
- case 'name':
- foreach ( $terms as $i => $term ) {
- $terms[$i] = sanitize_title_for_query( $term );
- }
- $terms = array_filter($terms);
-
- $terms = "'" . implode( "','", $terms ) . "'";
- $sql = "
- SELECT object_id
- FROM $wpdb->term_relationships
- INNER JOIN $wpdb->term_taxonomy USING (term_taxonomy_id)
- INNER JOIN $wpdb->terms USING (term_id)
- WHERE taxonomy IN ($taxonomies)
- AND $field $operator ($terms)
- ";
- break;
- }
-
- return $do_query ? $wpdb->get_col( $sql ) : $sql;
</del><ins>+ return $object_ids;
</ins><span class="cx"> }
</span><span class="cx">
</span><span class="cx"> /*
</span><span class="lines">@@ -571,44 +516,122 @@
</span><span class="cx"> * - 'include_children' bool (optional) Whether to include child terms.
</span><span class="cx"> * Default: true
</span><span class="cx"> *
</span><del>- * @param string $object_id_column
</del><ins>+ * @param string $primary_table
+ * @param string $primary_id_column
</ins><span class="cx"> * @return string
</span><span class="cx"> */
</span><del>-function get_tax_sql( $tax_query, $object_id_column ) {
</del><ins>+function get_tax_sql( $tax_query, $primary_table, $primary_id_column ) {
</ins><span class="cx"> global $wpdb;
</span><span class="cx">
</span><del>- $sql = array();
</del><ins>+ $join = '';
+ $where = '';
+ $i = 0;
</ins><span class="cx"> foreach ( $tax_query as $query ) {
</span><del>- if ( !isset( $query['include_children'] ) )
- $query['include_children'] = true;
</del><ins>+ extract( wp_parse_args( $query, array(
+ 'taxonomy' => array(),
+ 'terms' => array(),
+ 'include_children' => true,
+ 'field' => 'term_id',
+ 'operator' => 'IN',
+ ) ) );
</ins><span class="cx">
</span><del>- $query['do_query'] = false;
</del><ins>+ $taxonomies = (array) $taxonomy;
</ins><span class="cx">
</span><del>- $sql_single = get_objects_in_term( $query['terms'], $query['taxonomy'], $query );
</del><ins>+ foreach ( $taxonomies as $taxonomy ) {
+ if ( ! taxonomy_exists( $taxonomy ) )
+ return ' AND 0 = 1';
+ }
</ins><span class="cx">
</span><del>- if ( empty( $sql_single ) || is_wp_error( $sql_single ) )
- return ' AND 0 = 1';
</del><ins>+ if ( !in_array( $operator, array( 'IN', 'NOT IN' ) ) )
+ $operator = 'IN';
</ins><span class="cx">
</span><del>- $sql[] = $sql_single;
- }
</del><ins>+ $taxonomies = "'" . implode( "', '", $taxonomies ) . "'";
</ins><span class="cx">
</span><del>- if ( 1 == count( $sql ) ) {
- $ids = $wpdb->get_col( $sql[0] );
- } else {
- $r = "SELECT object_id FROM $wpdb->term_relationships WHERE 1=1";
- foreach ( $sql as $query )
- $r .= " AND object_id IN ($query)";
</del><ins>+ $terms = array_unique( (array) $terms );
</ins><span class="cx">
</span><del>- $ids = $wpdb->get_col( $r );
</del><ins>+ if ( empty( $terms ) )
+ continue;
+
+ if ( is_taxonomy_hierarchical( $taxonomy ) && $include_children ) {
+ _transform_terms( $terms, $taxonomies, $field, 'term_id' );
+
+ if ( empty( $terms ) )
+ continue;
+
+ $children = array();
+ foreach ( $terms as $term ) {
+ $children = array_merge( $children, get_term_children( $term, $taxonomy ) );
+ $children[] = $term;
+ }
+ $terms = $children;
+
+ _transform_terms( $terms, $taxonomies, 'term_id', 'term_taxonomy_id' );
+ }
+ else {
+ _transform_terms( $terms, $taxonomies, $field, 'term_taxonomy_id' );
+ }
+
+ if ( empty( $terms ) )
+ continue;
+
+ $terms = implode( ',', $terms );
+
+ if ( 'IN' == $operator ) {
+ $alias = $i ? 'tt' . $i : $wpdb->term_relationships;
+
+ $join .= " INNER JOIN $wpdb->term_relationships";
+ $join .= $i ? " AS $alias" : '';
+ $join .= " ON ($primary_table.$primary_id_column = $alias.object_id)";
+
+ $where .= " AND $alias.term_taxonomy_id $operator ($terms)";
+
+ $i++;
+ }
+ else {
+ // NOT IN is very slow for some reason
+ $where .= " AND $primary_table.$primary_id_column IN (
+ SELECT object_id
+ FROM $wpdb->term_relationships
+ WHERE term_taxonomy_id $operator ($terms)
+ )";
+ }
</ins><span class="cx"> }
</span><span class="cx">
</span><del>- if ( !empty( $ids ) )
- return " AND $object_id_column IN(" . implode( ', ', $ids ) . ")";
- else
- return ' AND 0 = 1';
</del><ins>+ return compact( 'join', 'where' );
</ins><span class="cx"> }
</span><span class="cx">
</span><ins>+function _transform_terms( &$terms, $taxonomies, $field, $resulting_field ) {
+ global $wpdb;
</ins><span class="cx">
</span><ins>+ if ( $field == $resulting_field )
+ return;
+
+ $resulting_field = esc_sql( $resulting_field );
+
+ switch ( $field ) {
+ case 'slug':
+ case 'name':
+ $terms = "'" . implode( "','", array_map( 'sanitize_title_for_query', $terms ) ) . "'";
+ $terms = $wpdb->get_col( "
+ SELECT $resulting_field
+ FROM $wpdb->term_taxonomy
+ INNER JOIN $wpdb->terms USING (term_id)
+ WHERE taxonomy IN ($taxonomies)
+ AND $field IN ($terms)
+ " );
+ break;
+
+ default:
+ $terms = implode( ',', array_map( 'intval', $terms ) );
+ $terms = $wpdb->get_col( "
+ SELECT $resulting_field
+ FROM $wpdb->term_taxonomy
+ WHERE taxonomy IN ($taxonomies)
+ AND term_id IN ($terms)
+ " );
+ }
+}
+
</ins><span class="cx"> /**
</span><span class="cx"> * Get all Term data from database by Term ID.
</span><span class="cx"> *
</span></span></pre>
</div>
</div>
</body>
</html>