WordPress.org

Make WordPress Core

Changeset 16413


Ignore:
Timestamp:
11/17/2010 01:56:01 AM (9 years ago)
Author:
scribu
Message:

Optimize get_tax_sql(). See #12891

Location:
trunk/wp-includes
Files:
2 edited

Legend:

Unmodified
Added
Removed
  • trunk/wp-includes/query.php

    r16404 r16413  
    19341934        $q['tax_query'] = $this->parse_tax_query( $q );
    19351935        if ( !empty( $q['tax_query'] ) ) {
     1936            $clauses = call_user_func_array( 'get_tax_sql', array( $q['tax_query'], $wpdb->posts, 'ID', &$this) );
     1937
     1938            $join .= $clauses['join'];
     1939            $where .= $clauses['where'];
     1940
    19361941            if ( empty($post_type) ) {
    19371942                $post_type = 'any';
     
    19401945                $post_status_join = true;
    19411946            }
    1942 
    1943             $where .= get_tax_sql( $q['tax_query'], "$wpdb->posts.ID" );
    19441947
    19451948            // Back-compat
  • trunk/wp-includes/taxonomy.php

    r16403 r16413  
    461461 * @uses wp_parse_args() Creates an array from string $args.
    462462 *
    463  * @param mixed $terms Term id/slug/name or array of such to match against
     463 * @param int|array $term_ids Term id or array of term ids of terms that will be used
    464464 * @param string|array $taxonomies String of taxonomy name or Array of string values of taxonomy names
    465  * @param array|string $args
    466  *   'include_children' bool Whether to include term children (hierarchical taxonomies only)
    467  *   'field' string Which term field is being used. Can be 'term_id', 'slug' or 'name'
    468  *   'operator' string Can be 'IN' and 'NOT IN'
    469  *   'do_query' bool Whether to execute the query or return the SQL string
    470  *
    471  * @return WP_Error If the taxonomy does not exist
    472  * @return array The list of found object_ids
    473  * @return string The SQL string, if do_query is set to false
    474  */
    475 function get_objects_in_term( $terms, $taxonomies, $args = array() ) {
     465 * @param array|string $args Change the order of the object_ids, either ASC or DESC
     466 * @return WP_Error|array If the taxonomy does not exist, then WP_Error will be returned. On success
     467 *  the array can be empty meaning that there are no $object_ids found or it will return the $object_ids found.
     468 */
     469function get_objects_in_term( $term_ids, $taxonomies, $args = array() ) {
    476470    global $wpdb;
    477471
    478     extract( wp_parse_args( $args, array(
    479         'include_children' => false,
    480         'field' => 'term_id',
    481         'operator' => 'IN',
    482         'do_query' => true,
    483     ) ), EXTR_SKIP );
    484 
    485     $taxonomies = (array) $taxonomies;
    486 
    487     foreach ( $taxonomies as $taxonomy ) {
     472    if ( ! is_array( $term_ids ) )
     473        $term_ids = array( $term_ids );
     474
     475    if ( ! is_array( $taxonomies ) )
     476        $taxonomies = array( $taxonomies );
     477
     478    foreach ( (array) $taxonomies as $taxonomy ) {
    488479        if ( ! taxonomy_exists( $taxonomy ) )
    489             return new WP_Error( 'invalid_taxonomy', sprintf( __( 'Invalid Taxonomy: %s' ), $taxonomy ) );
    490     }
    491 
    492     if ( !in_array( $field, array( 'term_id', 'slug', 'name' ) ) )
    493         $field = 'term_id';
    494 
    495     if ( !in_array( $operator, array( 'IN', 'NOT IN' ) ) )
    496         $operator = 'IN';
    497 
    498     $terms = array_unique( (array) $terms );
    499 
    500     if ( is_taxonomy_hierarchical( $taxonomy ) && $include_children ) {
    501         $children = array();
    502         foreach ( $terms as $term ) {
    503             if ( 'term_id' != $field ) {
    504                 if ( $term = get_term_by( $field, $term, $taxonomy ) )
    505                     $term = $term->term_id;
    506                 else
    507                     continue;
    508             }
    509             $children = array_merge( $children, get_term_children( $term, $taxonomy ) );
    510             $children[] = $term;
    511         }
    512         $terms = $children;
    513         $field = 'term_id';
    514     }
    515 
    516     if ( empty( $terms ) )
    517         return $do_query ? array() : '';
     480            return new WP_Error( 'invalid_taxonomy', __( 'Invalid Taxonomy' ) );
     481    }
     482
     483    $defaults = array( 'order' => 'ASC' );
     484    $args = wp_parse_args( $args, $defaults );
     485    extract( $args, EXTR_SKIP );
     486
     487    $order = ( 'desc' == strtolower( $order ) ) ? 'DESC' : 'ASC';
     488
     489    $term_ids = array_map('intval', $term_ids );
    518490
    519491    $taxonomies = "'" . implode( "', '", $taxonomies ) . "'";
    520 
    521     switch ( $field ) {
    522         case 'term_id':
    523             $terms = array_map( 'intval', $terms );
    524 
    525             $terms = implode( ',', $terms );
    526             $sql = "
    527                 SELECT object_id
    528                 FROM $wpdb->term_relationships
    529                 INNER JOIN $wpdb->term_taxonomy USING (term_taxonomy_id)
    530                 WHERE taxonomy IN ($taxonomies)
    531                 AND term_id $operator ($terms)
    532             ";
    533         break;
    534 
    535         case 'slug':
    536         case 'name':
    537             foreach ( $terms as $i => $term ) {
    538                 $terms[$i] = sanitize_title_for_query( $term );
    539             }
    540             $terms = array_filter($terms);
    541 
    542             $terms = "'" . implode( "','", $terms ) . "'";
    543             $sql = "
    544                 SELECT object_id
    545                 FROM $wpdb->term_relationships
    546                 INNER JOIN $wpdb->term_taxonomy USING (term_taxonomy_id)
    547                 INNER JOIN $wpdb->terms USING (term_id)
    548                 WHERE taxonomy IN ($taxonomies)
    549                 AND $field $operator ($terms)
    550             ";
    551         break;
    552     }
    553 
    554     return $do_query ? $wpdb->get_col( $sql ) : $sql;
     492    $term_ids = "'" . implode( "', '", $term_ids ) . "'";
     493
     494    $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");
     495
     496    if ( ! $object_ids )
     497        return array();
     498
     499    return $object_ids;
    555500}
    556501
     
    572517 *      Default: true
    573518 *
    574  * @param string $object_id_column
     519 * @param string $primary_table
     520 * @param string $primary_id_column
    575521 * @return string
    576522 */
    577 function get_tax_sql( $tax_query, $object_id_column ) {
     523function get_tax_sql( $tax_query, $primary_table, $primary_id_column ) {
    578524    global $wpdb;
    579525
    580     $sql = array();
     526    $join = '';
     527    $where = '';
     528    $i = 0;
    581529    foreach ( $tax_query as $query ) {
    582         if ( !isset( $query['include_children'] ) )
    583             $query['include_children'] = true;
    584 
    585         $query['do_query'] = false;
    586 
    587         $sql_single = get_objects_in_term( $query['terms'], $query['taxonomy'], $query );
    588 
    589         if ( empty( $sql_single ) || is_wp_error( $sql_single ) )
    590             return ' AND 0 = 1';
    591 
    592         $sql[] = $sql_single;
    593     }
    594 
    595     if ( 1 == count( $sql ) ) {
    596         $ids = $wpdb->get_col( $sql[0] );
    597     } else {
    598         $r = "SELECT object_id FROM $wpdb->term_relationships WHERE 1=1";
    599         foreach ( $sql as $query )
    600             $r .= " AND object_id IN ($query)";
    601 
    602         $ids = $wpdb->get_col( $r );
    603     }
    604 
    605     if ( !empty( $ids ) )
    606         return " AND $object_id_column IN(" . implode( ', ', $ids ) . ")";
    607     else
    608         return ' AND 0 = 1';
    609 }
    610 
     530        extract( wp_parse_args( $query, array(
     531            'taxonomy' => array(),
     532            'terms' => array(),
     533            'include_children' => true,
     534            'field' => 'term_id',
     535            'operator' => 'IN',
     536        ) ) );
     537
     538        $taxonomies = (array) $taxonomy;
     539
     540        foreach ( $taxonomies as $taxonomy ) {
     541            if ( ! taxonomy_exists( $taxonomy ) )
     542                return ' AND 0 = 1';
     543        }
     544
     545        if ( !in_array( $operator, array( 'IN', 'NOT IN' ) ) )
     546            $operator = 'IN';
     547
     548        $taxonomies = "'" . implode( "', '", $taxonomies ) . "'";
     549
     550        $terms = array_unique( (array) $terms );
     551
     552        if ( empty( $terms ) )
     553            continue;
     554
     555        if ( is_taxonomy_hierarchical( $taxonomy ) && $include_children ) {
     556            _transform_terms( $terms, $taxonomies, $field, 'term_id' );
     557
     558            if ( empty( $terms ) )
     559                continue;
     560
     561            $children = array();
     562            foreach ( $terms as $term ) {
     563                $children = array_merge( $children, get_term_children( $term, $taxonomy ) );
     564                $children[] = $term;
     565            }
     566            $terms = $children;
     567
     568            _transform_terms( $terms, $taxonomies, 'term_id', 'term_taxonomy_id' );
     569        }
     570        else {
     571            _transform_terms( $terms, $taxonomies, $field, 'term_taxonomy_id' );
     572        }
     573
     574        if ( empty( $terms ) )
     575            continue;
     576
     577        $terms = implode( ',', $terms );
     578
     579        if ( 'IN' == $operator ) {
     580            $alias = $i ? 'tt' . $i : $wpdb->term_relationships;
     581
     582            $join .= " INNER JOIN $wpdb->term_relationships";
     583            $join .= $i ? " AS $alias" : '';
     584            $join .= " ON ($primary_table.$primary_id_column = $alias.object_id)";
     585
     586            $where .= " AND $alias.term_taxonomy_id $operator ($terms)";
     587
     588            $i++;
     589        }
     590        else {
     591            // NOT IN is very slow for some reason
     592            $where .= " AND $primary_table.$primary_id_column IN (
     593                SELECT object_id
     594                FROM $wpdb->term_relationships
     595                WHERE term_taxonomy_id $operator ($terms)
     596            )";         
     597        }
     598    }
     599
     600    return compact( 'join', 'where' );
     601}
     602
     603function _transform_terms( &$terms, $taxonomies, $field, $resulting_field ) {
     604    global $wpdb;
     605
     606    if ( $field == $resulting_field )
     607        return;
     608
     609    $resulting_field = esc_sql( $resulting_field );
     610
     611    switch ( $field ) {
     612        case 'slug':
     613        case 'name':
     614            $terms = "'" . implode( "','", array_map( 'sanitize_title_for_query', $terms ) ) . "'";
     615            $terms = $wpdb->get_col( "
     616                SELECT $resulting_field
     617                FROM $wpdb->term_taxonomy
     618                INNER JOIN $wpdb->terms USING (term_id)
     619                WHERE taxonomy IN ($taxonomies)
     620                AND $field IN ($terms)
     621            " );
     622            break;
     623
     624        default:
     625            $terms = implode( ',', array_map( 'intval', $terms ) );
     626            $terms = $wpdb->get_col( "
     627                SELECT $resulting_field
     628                FROM $wpdb->term_taxonomy
     629                WHERE taxonomy IN ($taxonomies)
     630                AND term_id IN ($terms)
     631            " );
     632    }
     633}
    611634
    612635/**
Note: See TracChangeset for help on using the changeset viewer.