/** * Given a taxonomy query, generates SQL to be appended to a main query. * * @since 3.1.0 * * @see WP_Tax_Query * * @param array $tax_query A compact tax query * @param string $primary_table * @param string $primary_id_column * @return array */ function get_tax_sql($tax_query, $primary_table, $primary_id_column) { $tax_query_obj = new WP_Tax_Query($tax_query); return $tax_query_obj->get_sql($primary_table, $primary_id_column); }
/** * Get a SQL clause representing member_type include/exclusion. * * @since 2.4.0 * * @param string|array $member_types Array or comma-separated list of member types. * @param string $operator 'IN' or 'NOT IN'. * @return string */ protected function get_sql_clause_for_member_types($member_types, $operator) { global $wpdb; // Sanitize. if ('NOT IN' !== $operator) { $operator = 'IN'; } // Parse and sanitize types. if (!is_array($member_types)) { $member_types = preg_split('/[,\\s+]/', $member_types); } $types = array(); foreach ($member_types as $mt) { if (bp_get_member_type_object($mt)) { $types[] = $mt; } } $tax_query = new WP_Tax_Query(array(array('taxonomy' => 'bp_member_type', 'field' => 'name', 'operator' => $operator, 'terms' => $types))); // Switch to the root blog, where member type taxonomies live. $switched = false; if (!bp_is_root_blog()) { switch_to_blog(bp_get_root_blog_id()); $switched = true; } $sql_clauses = $tax_query->get_sql('u', $this->uid_name); if ($switched) { restore_current_blog(); } $clause = ''; // The no_results clauses are the same between IN and NOT IN. if (false !== strpos($sql_clauses['where'], '0 = 1')) { $clause = $this->no_results['where']; // The tax_query clause generated for NOT IN can be used almost as-is. We just trim the leading 'AND'. } elseif ('NOT IN' === $operator) { $clause = preg_replace('/^\\s*AND\\s*/', '', $sql_clauses['where']); // IN clauses must be converted to a subquery. } elseif (preg_match('/' . $wpdb->term_relationships . '\\.term_taxonomy_id IN \\([0-9, ]+\\)/', $sql_clauses['where'], $matches)) { $clause = "u.{$this->uid_name} IN ( SELECT object_id FROM {$wpdb->term_relationships} WHERE {$matches[0]} )"; } return $clause; }
/** * Count products after other filters have occured by adjusting the main query. * @param int $rating * @return int */ protected function get_filtered_product_count($rating) { global $wpdb; $tax_query = WC_Query::get_main_tax_query(); $meta_query = WC_Query::get_main_meta_query(); // Unset current rating filter. foreach ($tax_query as $key => $query) { if (!empty($query['rating_filter'])) { unset($tax_query[$key]); break; } } // Set new rating filter. $product_visibility_terms = wc_get_product_visibility_term_ids(); $tax_query[] = array('taxonomy' => 'product_visibility', 'field' => 'term_taxonomy_id', 'terms' => $product_visibility_terms['rated-' . $rating], 'operator' => 'IN', 'rating_filter' => true); $meta_query = new WP_Meta_Query($meta_query); $tax_query = new WP_Tax_Query($tax_query); $meta_query_sql = $meta_query->get_sql('post', $wpdb->posts, 'ID'); $tax_query_sql = $tax_query->get_sql($wpdb->posts, 'ID'); $sql = "SELECT COUNT( DISTINCT {$wpdb->posts}.ID ) FROM {$wpdb->posts} "; $sql .= $tax_query_sql['join'] . $meta_query_sql['join']; $sql .= " WHERE {$wpdb->posts}.post_type = 'product' AND {$wpdb->posts}.post_status = 'publish' "; $sql .= $tax_query_sql['where'] . $meta_query_sql['where']; return absint($wpdb->get_var($sql)); }
/** * Prepare the query for user_ids. * * @since BuddyPress (1.7.0) */ public function prepare_user_ids_query() { global $wpdb, $bp; // Default query variables used here $type = ''; $per_page = 0; $page = 1; $user_id = 0; $include = false; $search_terms = false; $exclude = false; $meta_key = false; $meta_value = false; extract( $this->query_vars ); // Setup the main SQL query container $sql = array( 'select' => '', 'where' => array(), 'orderby' => '', 'order' => '', 'limit' => '' ); /** TYPE **************************************************************/ // Determines the sort order, which means it also determines where the // user IDs are drawn from (the SELECT and WHERE statements) switch ( $type ) { // 'online' query happens against the last_activity usermeta key // Filter 'bp_user_query_online_interval' to modify the // number of minutes used as an interval case 'online' : $this->uid_name = 'user_id'; $this->uid_table = $bp->members->table_name_last_activity; $sql['select'] = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u"; $sql['where'][] = $wpdb->prepare( "u.component = %s AND u.type = 'last_activity'", buddypress()->members->id ); $sql['where'][] = $wpdb->prepare( "u.date_recorded >= DATE_SUB( UTC_TIMESTAMP(), INTERVAL %d MINUTE )", apply_filters( 'bp_user_query_online_interval', 15 ) ); $sql['orderby'] = "ORDER BY u.date_recorded"; $sql['order'] = "DESC"; break; // 'active', 'newest', and 'random' queries // all happen against the last_activity usermeta key case 'active' : case 'newest' : case 'random' : $this->uid_name = 'user_id'; $this->uid_table = $bp->members->table_name_last_activity; $sql['select'] = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u"; $sql['where'][] = $wpdb->prepare( "u.component = %s AND u.type = 'last_activity'", buddypress()->members->id ); if ( 'newest' == $type ) { $sql['orderby'] = "ORDER BY u.user_id"; $sql['order'] = "DESC"; } elseif ( 'random' == $type ) { $sql['orderby'] = "ORDER BY rand()"; } else { $sql['orderby'] = "ORDER BY u.date_recorded"; $sql['order'] = "DESC"; } break; // 'popular' sorts by the 'total_friend_count' usermeta case 'popular' : $this->uid_name = 'user_id'; $this->uid_table = $wpdb->usermeta; $sql['select'] = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u"; $sql['where'][] = $wpdb->prepare( "u.meta_key = %s", bp_get_user_meta_key( 'total_friend_count' ) ); $sql['orderby'] = "ORDER BY CONVERT(u.meta_value, SIGNED)"; $sql['order'] = "DESC"; break; // 'alphabetical' sorts depend on the xprofile setup case 'alphabetical' : // We prefer to do alphabetical sorts against the display_name field // of wp_users, because the table is smaller and better indexed. We // can do so if xprofile sync is enabled, or if xprofile is inactive. // // @todo remove need for bp_is_active() check if ( ! bp_disable_profile_sync() || ! bp_is_active( 'xprofile' ) ) { $this->uid_name = 'ID'; $this->uid_table = $wpdb->users; $sql['select'] = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u"; $sql['orderby'] = "ORDER BY u.display_name"; $sql['order'] = "ASC"; // When profile sync is disabled, alphabetical sorts must happen against // the xprofile table } else { $this->uid_name = 'user_id'; $this->uid_table = $bp->profile->table_name_data; $sql['select'] = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u"; $sql['where'][] = $wpdb->prepare( "u.field_id = %d", bp_xprofile_fullname_field_id() ); $sql['orderby'] = "ORDER BY u.value"; $sql['order'] = "ASC"; } // Alphabetical queries ignore last_activity, while BP uses last_activity // to infer spam/deleted/non-activated users. To ensure that these users // are filtered out, we add an appropriate sub-query. $sql['where'][] = "u.{$this->uid_name} IN ( SELECT ID FROM {$wpdb->users} WHERE " . bp_core_get_status_sql( '' ) . " )"; break; // Any other 'type' falls through default : $this->uid_name = 'ID'; $this->uid_table = $wpdb->users; $sql['select'] = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u"; // In this case, we assume that a plugin is // handling order, so we leave those clauses // blank break; } /** WHERE *************************************************************/ // 'include' - User ids to include in the results $include = false !== $include ? wp_parse_id_list( $include ) : array(); $include_ids = $this->get_include_ids( $include ); if ( ! empty( $include_ids ) ) { $include_ids = implode( ',', wp_parse_id_list( $include_ids ) ); $sql['where'][] = "u.{$this->uid_name} IN ({$include_ids})"; } // 'exclude' - User ids to exclude from the results if ( false !== $exclude ) { $exclude_ids = implode( ',', wp_parse_id_list( $exclude ) ); $sql['where'][] = "u.{$this->uid_name} NOT IN ({$exclude_ids})"; } // 'user_id' - When a user id is passed, limit to the friends of the user // @todo remove need for bp_is_active() check if ( ! empty( $user_id ) && bp_is_active( 'friends' ) ) { $friend_ids = friends_get_friend_user_ids( $user_id ); $friend_ids = implode( ',', wp_parse_id_list( $friend_ids ) ); if ( ! empty( $friend_ids ) ) { $sql['where'][] = "u.{$this->uid_name} IN ({$friend_ids})"; // If the user has no friends, the query should always // return no users } else { $sql['where'][] = $this->no_results['where']; } } /** Search Terms ******************************************************/ // 'search_terms' searches user_login and user_nicename // xprofile field matches happen in bp_xprofile_bp_user_query_search() if ( false !== $search_terms ) { $search_terms = bp_esc_like( wp_kses_normalize_entities( $search_terms ) ); if ( $search_wildcard === 'left' ) { $search_terms_nospace = '%' . $search_terms; $search_terms_space = '%' . $search_terms . ' %'; } elseif ( $search_wildcard === 'right' ) { $search_terms_nospace = $search_terms . '%'; $search_terms_space = '% ' . $search_terms . '%'; } else { $search_terms_nospace = '%' . $search_terms . '%'; $search_terms_space = '%' . $search_terms . '%'; } $sql['where']['search'] = $wpdb->prepare( "u.{$this->uid_name} IN ( SELECT ID FROM {$wpdb->users} WHERE ( user_login LIKE %s OR user_login LIKE %s OR user_nicename LIKE %s OR user_nicename LIKE %s ) )", $search_terms_nospace, $search_terms_space, $search_terms_nospace, $search_terms_space ); } // Member type. if ( ! empty( $member_type ) ) { $member_types = array(); if ( ! is_array( $member_type ) ) { $member_type = preg_split( '/[,\s+]/', $member_type ); } foreach ( $member_type as $mt ) { if ( ! bp_get_member_type_object( $mt ) ) { continue; } $member_types[] = $mt; } if ( ! empty( $member_types ) ) { $member_type_tq = new WP_Tax_Query( array( array( 'taxonomy' => 'bp_member_type', 'field' => 'name', 'operator' => 'IN', 'terms' => $member_types, ), ) ); // Switch to the root blog, where member type taxonomies live. switch_to_blog( bp_get_root_blog_id() ); $member_type_sql_clauses = $member_type_tq->get_sql( 'u', $this->uid_name ); restore_current_blog(); // Grab the first term_relationships clause and convert to a subquery. if ( preg_match( '/' . $wpdb->term_relationships . '\.term_taxonomy_id IN \([0-9, ]+\)/', $member_type_sql_clauses['where'], $matches ) ) { $sql['where']['member_type'] = "u.{$this->uid_name} IN ( SELECT object_id FROM $wpdb->term_relationships WHERE {$matches[0]} )"; } } } // 'meta_key', 'meta_value' allow usermeta search // To avoid global joins, do a separate query if ( false !== $meta_key ) { $meta_sql = $wpdb->prepare( "SELECT user_id FROM {$wpdb->usermeta} WHERE meta_key = %s", $meta_key ); if ( false !== $meta_value ) { $meta_sql .= $wpdb->prepare( " AND meta_value = %s", $meta_value ); } $found_user_ids = $wpdb->get_col( $meta_sql ); if ( ! empty( $found_user_ids ) ) { $sql['where'][] = "u.{$this->uid_name} IN (" . implode( ',', wp_parse_id_list( $found_user_ids ) ) . ")"; } else { $sql['where'][] = '1 = 0'; } } // 'per_page', 'page' - handles LIMIT if ( !empty( $per_page ) && !empty( $page ) ) { $sql['limit'] = $wpdb->prepare( "LIMIT %d, %d", intval( ( $page - 1 ) * $per_page ), intval( $per_page ) ); } else { $sql['limit'] = ''; } // Allow custom filters $sql = apply_filters_ref_array( 'bp_user_query_uid_clauses', array( $sql, &$this ) ); // Assemble the query chunks $this->uid_clauses['select'] = $sql['select']; $this->uid_clauses['where'] = ! empty( $sql['where'] ) ? 'WHERE ' . implode( ' AND ', $sql['where'] ) : ''; $this->uid_clauses['orderby'] = $sql['orderby']; $this->uid_clauses['order'] = $sql['order']; $this->uid_clauses['limit'] = $sql['limit']; do_action_ref_array( 'bp_pre_user_query', array( &$this ) ); }
/** * @ticket 29738 */ public function test_get_sql_operator_and_empty_terms() { register_taxonomy('wptests_tax', 'post'); $tq = new WP_Tax_Query(array('relation' => 'OR', array('taxonomy' => 'wptests_tax', 'field' => 'term_id', 'operator' => 'AND', 'terms' => array()))); global $wpdb; $expected = array('join' => '', 'where' => ''); $this->assertSame($expected, $tq->get_sql($wpdb->posts, 'ID')); _unregister_taxonomy('wptests_tax'); }
public function load(array $options = array()) { $thisclass = get_class($this); $slug = isset($this->slug) ? $this->slug : sanitize_key($thisclass); $Storefront = ShoppStorefront(); $Shopping = ShoppShopping(); $Processing = new ShoppProduct(); $summary_table = ShoppDatabaseObject::tablename(ProductSummary::$table); $defaults = array('columns' => false, 'useindex' => false, 'joins' => array(), 'where' => array(), 'groupby' => false, 'orderby' => false, 'having' => array(), 'limit' => false, 'order' => false, 'page' => false, 'paged' => false, 'nostock' => null, 'pagination' => true, 'published' => true, 'ids' => false, 'adjacent' => false, 'product' => false, 'load' => array('coverimages'), 'inventory' => false, 'taxquery' => false, 'debug' => false); $loading = array_merge($defaults, $options); $loading = apply_filters("shopp_collection_load_options", $loading); $loading = apply_filters("shopp_{$slug}_collection_load_options", $loading); extract($loading); // Setup pagination $this->paged = false; $this->pagination = false === $paged ? shopp_setting('catalog_pagination') : $paged; $page = false === $page ? get_query_var('paged') : $page; $this->page = (int) $page > 0 || preg_match('/(0\\-9|[A-Z])/', $page) ? $page : 1; // Hard product limit per category to keep resources "reasonable" $hardlimit = apply_filters('shopp_category_products_hardlimit', 1000); // Enforce the where parameter as an array if (!is_array($where)) { return shopp_debug('The "where" parameter for ' . __METHOD__ . ' must be formatted as an array.'); } // Inventory filtering if (shopp_setting_enabled('inventory') && (is_null($nostock) && !shopp_setting_enabled('outofstock_catalog') || !is_null($nostock) && !Shopp::str_true($nostock))) { $where[] = "( s.inventory='off' OR (s.inventory='on' AND s.stock > 0) )"; } if (Shopp::str_true($published)) { $where[] = "p.post_status='publish'"; } // Multiple taxonomy queries if (is_array($taxquery)) { $tqdefaults = array('relation' => 'AND', 'include_children' => true); $taxquery = array_merge($tqdefaults, $taxquery); $TQ = new WP_Tax_Query($taxquery); $sql = $TQ->get_sql($Processing->_table, 'ID'); unset($TQ); $joins['taxquery'] = self::taxquery($sql['join']); $where[] = self::taxquery($sql['where']); } // Sort Order if (!$orderby) { $titlesort = "p.post_title ASC"; $defaultsort = empty($order) ? $titlesort : $order; // Define filterable built-in sort methods (you're welcome) $sortmethods = apply_filters('shopp_collection_sort_methods', array('bestselling' => "s.sold DESC,{$titlesort}", 'highprice' => "maxprice DESC,{$titlesort}", 'lowprice' => "minprice ASC,{$titlesort}", 'newest' => "p.post_date DESC,{$titlesort}", 'oldest' => "p.post_date ASC,{$titlesort}", 'random' => "RAND(" . crc32($Shopping->session) . ")", 'chaos' => "RAND(" . time() . ")", 'reverse' => "p.post_title DESC", 'title' => $titlesort, 'custom' => is_subclass_of($this, 'ProductTaxonomy') ? "tr.term_order ASC,{$titlesort}" : $defaultsort, 'recommended' => is_subclass_of($this, 'ProductTaxonomy') ? "tr.term_order ASC,{$titlesort}" : $defaultsort, 'default' => $defaultsort)); // Handle valid user browsing sort change requests if (isset($_REQUEST['sort']) && !empty($_REQUEST['sort']) && array_key_exists(strtolower($_REQUEST['sort']), $sortmethods)) { $Storefront->browsing['sortorder'] = strtolower($_REQUEST['sort']); } // Collect sort setting sources (Shopp admin setting, User browsing setting, programmer specified setting) $sortsettings = array(shopp_setting('default_product_order'), isset($Storefront->browsing['sortorder']) ? $Storefront->browsing['sortorder'] : false, !empty($order) ? $order : false); // Go through setting sources to determine most applicable setting $sorting = 'title'; foreach ($sortsettings as $setting) { if (!empty($setting) && isset($sortmethods[strtolower($setting)])) { $sorting = strtolower($setting); } } $orderby = $sortmethods[$sorting]; } if (empty($orderby)) { $orderby = 'p.post_title ASC'; } // Pagination if (empty($limit)) { if ($this->pagination > 0 && is_numeric($this->page) && Shopp::str_true($pagination)) { if (!$this->pagination || $this->pagination < 0) { $this->pagination = $hardlimit; } $start = $this->pagination * ($this->page - 1); $limit = "{$start},{$this->pagination}"; } else { $limit = $hardlimit; } $limited = false; // Flag that the result set does not have forced limits } else { $limited = true; } // The result set has forced limits // Core query components // Load core product data and product summary columns $cols = array('p.ID', 'p.post_title', 'p.post_name', 'p.post_excerpt', 'p.post_status', 'p.post_date', 'p.post_modified', 's.modified AS summed', 's.sold', 's.grossed', 's.maxprice', 's.minprice', 's.ranges', 's.taxed', 's.stock', 's.lowstock', 's.inventory', 's.featured', 's.variants', 's.addons', 's.sale'); if ($ids) { $cols = array('p.ID'); } $columns = "SQL_CALC_FOUND_ROWS " . join(',', $cols) . ($columns !== false ? ',' . $columns : ''); $table = "{$Processing->_table} AS p"; $where[] = "p.post_type='" . ShoppProduct::posttype() . "'"; $joins[$summary_table] = "LEFT OUTER JOIN {$summary_table} AS s ON s.product=p.ID"; $options = compact('columns', 'useindex', 'table', 'joins', 'where', 'groupby', 'having', 'limit', 'orderby'); // Alphabetic pagination if ('alpha' === $pagination || preg_match('/(0\\-9|[A-Z])/', $page)) { // Setup Roman alphabet navigation $alphanav = array_merge(array('0-9'), range('A', 'Z')); $this->alpha = array_combine($alphanav, array_fill(0, count($alphanav), 0)); // Setup alphabetized index query $a = $options; $a['columns'] = "count(DISTINCT p.ID) AS total,IF(LEFT(p.post_title,1) REGEXP '[0-9]',LEFT(p.post_title,1),LEFT(SOUNDEX(p.post_title),1)) AS letter"; $a['groupby'] = "letter"; $alphaquery = sDB::select($a); $cachehash = 'collection_alphanav_' . md5($alphaquery); $cached = Shopp::cache_get($cachehash, 'shopp_collection_alphanav'); if ($cached) { // Load from object cache, if available $this->alpha = $cached; $cached = false; } else { // Run query and cache results $expire = apply_filters('shopp_collection_cache_expire', 43200); $alpha = sDB::query($alphaquery, 'array', array($this, 'alphatable')); Shopp::cache_set($cachehash, $alpha, 'shopp_collection_alphanav', $expire); } $this->paged = true; if ($this->page == 1) { $this->page = '0-9'; } $alphafilter = $this->page == "0-9" ? "(LEFT(p.post_title,1) REGEXP '[0-9]') = 1" : "IF(LEFT(p.post_title,1) REGEXP '[0-9]',LEFT(p.post_title,1),LEFT(SOUNDEX(p.post_title),1))='{$this->page}'"; $options['where'][] = $alphafilter; } $query = sDB::select(apply_filters('shopp_collection_query', $options)); if ($debug) { echo $query . BR . BR; } // Load from cached results if available, or run the query and cache the results $cachehash = 'collection_' . md5($query); $cached = Shopp::cache_get($cachehash, 'shopp_collection'); if ($cached) { $this->products = $cached->products; $this->total = $cached->total; } else { $expire = apply_filters('shopp_collection_cache_expire', 43200); $cache = new stdClass(); if ($ids) { $cache->products = $this->products = sDB::query($query, 'array', 'col', 'ID'); } else { $cache->products = $this->products = sDB::query($query, 'array', array($Processing, 'loader')); } $cache->total = $this->total = sDB::found(); // If running a limited set, the reported total found should not exceed the limit (but can because of SQL_CALC_FOUND_ROWS) // Don't use the limit if it is offset if ($limited && false === strpos($limit, ',')) { $cache->total = $this->total = min($limit, $this->total); } Shopp::cache_set($cachehash, $cache, 'shopp_collection', $expire); } if (false === $this->products) { $this->products = array(); } if ($ids) { return $this->size() > 0; } // Finish up pagination construction if ($this->pagination > 0 && $this->total > $this->pagination) { $this->pages = ceil($this->total / $this->pagination); if ($this->pages > 1) { $this->paged = true; } } // Load all requested product meta from other data sources $Processing->load_data($load, $this->products); // If products are missing summary data, resum them if (isset($Processing->resum) && !empty($Processing->resum)) { $Processing->load_data(array('prices'), $Processing->resum); } unset($Processing); // Free memory $this->loaded = true; return $this->size() > 0; }
/** * Get filtered min price for current products. * @return int */ protected function get_filtered_price() { global $wpdb, $wp_the_query; $args = $wp_the_query->query_vars; $tax_query = isset($args['tax_query']) ? $args['tax_query'] : array(); $meta_query = isset($args['meta_query']) ? $args['meta_query'] : array(); if (!empty($args['taxonomy']) && !empty($args['term'])) { $tax_query[] = array('taxonomy' => $args['taxonomy'], 'terms' => array($args['term']), 'field' => 'slug'); } foreach ($meta_query as $key => $query) { if (!empty($query['price_filter']) || !empty($query['rating_filter'])) { unset($meta_query[$key]); } } $meta_query = new WP_Meta_Query($meta_query); $tax_query = new WP_Tax_Query($tax_query); $meta_query_sql = $meta_query->get_sql('post', $wpdb->posts, 'ID'); $tax_query_sql = $tax_query->get_sql($wpdb->posts, 'ID'); $sql = "SELECT min( CAST( price_meta.meta_value AS UNSIGNED ) ) as min_price, max( CAST( price_meta.meta_value AS UNSIGNED ) ) as max_price FROM {$wpdb->posts} "; $sql .= " LEFT JOIN {$wpdb->postmeta} as price_meta ON {$wpdb->posts}.ID = price_meta.post_id " . $tax_query_sql['join'] . $meta_query_sql['join']; $sql .= " \tWHERE {$wpdb->posts}.post_type = 'product'\n\t\t\t\t\tAND {$wpdb->posts}.post_status = 'publish'\n\t\t\t\t\tAND price_meta.meta_key IN ('" . implode("','", array_map('esc_sql', apply_filters('woocommerce_price_filter_meta_keys', array('_price')))) . "')\n\t\t\t\t\tAND price_meta.meta_value > '' "; $sql .= $tax_query_sql['where'] . $meta_query_sql['where']; return $wpdb->get_row($sql); }
/** * Count products within certain terms, taking the main WP query into consideration. * * @param array $term_ids * @param string $taxonomy * @param string $query_type * @return array */ protected function get_filtered_term_product_counts($term_ids, $taxonomy, $query_type) { global $wpdb; $tax_query = WC_Query::get_main_tax_query(); $meta_query = WC_Query::get_main_meta_query(); if ('or' === $query_type) { foreach ($tax_query as $key => $query) { if (is_array($query) && $taxonomy === $query['taxonomy']) { unset($tax_query[$key]); } } } $meta_query = new WP_Meta_Query($meta_query); $tax_query = new WP_Tax_Query($tax_query); $meta_query_sql = $meta_query->get_sql('post', $wpdb->posts, 'ID'); $tax_query_sql = $tax_query->get_sql($wpdb->posts, 'ID'); // Generate query $query = array(); $query['select'] = "SELECT COUNT( DISTINCT {$wpdb->posts}.ID ) as term_count, terms.term_id as term_count_id"; $query['from'] = "FROM {$wpdb->posts}"; $query['join'] = "\n\t\t\tINNER JOIN {$wpdb->term_relationships} AS term_relationships ON {$wpdb->posts}.ID = term_relationships.object_id\n\t\t\tINNER JOIN {$wpdb->term_taxonomy} AS term_taxonomy USING( term_taxonomy_id )\n\t\t\tINNER JOIN {$wpdb->terms} AS terms USING( term_id )\n\t\t\t" . $tax_query_sql['join'] . $meta_query_sql['join']; $query['where'] = "\n\t\t\tWHERE {$wpdb->posts}.post_type IN ( 'product' )\n\t\t\tAND {$wpdb->posts}.post_status = 'publish'\n\t\t\t" . $tax_query_sql['where'] . $meta_query_sql['where'] . "\n\t\t\tAND terms.term_id IN (" . implode(',', array_map('absint', $term_ids)) . ")\n\t\t"; if ($search = WC_Query::get_main_search_query_sql()) { $query['where'] .= ' AND ' . $search; } $query['group_by'] = "GROUP BY terms.term_id"; $query = apply_filters('woocommerce_get_filtered_term_product_counts_query', $query); $query = implode(' ', $query); $results = $wpdb->get_results($query); return wp_list_pluck($results, 'term_count', 'term_count_id'); }
public static function get_filtered_price($mode = 'yes') { global $wpdb, $wp_the_query; $args = $wp_the_query->query_vars; $tax_query = $mode == 'yes' && isset($args['tax_query']) ? $args['tax_query'] : array(); $meta_query = $mode == 'yes' && isset($args['meta_query']) ? $args['meta_query'] : array(); /* if ( ! empty( $args['taxonomy'] ) && ! empty( $args['term'] ) ) { $tax_query[] = array( 'taxonomy' => $args['taxonomy'], 'terms' => array( $args['term'] ), 'field' => 'slug', ); } foreach ( $meta_query as $key => $query ) { if ( ! empty( $query['price_filter'] ) || ! empty( $query['rating_filter'] ) ) { unset( $meta_query[ $key ] ); } } */ $meta_query = new WP_Meta_Query($meta_query); $tax_query = new WP_Tax_Query($tax_query); $meta_query_sql = $meta_query->get_sql('post', $wpdb->posts, 'ID'); $tax_query_sql = $tax_query->get_sql($wpdb->posts, 'ID'); $sql = "SELECT min( CAST( price_meta.meta_value AS UNSIGNED ) ) as min_price, max( CAST( price_meta.meta_value AS UNSIGNED ) ) as max_price FROM {$wpdb->posts} "; $sql .= " LEFT JOIN {$wpdb->postmeta} as price_meta ON {$wpdb->posts}.ID = price_meta.post_id " . $tax_query_sql['join'] . $meta_query_sql['join']; $sql .= " \tWHERE {$wpdb->posts}.post_type = 'product'\r\n\t\t\t\t\t\tAND {$wpdb->posts}.post_status = 'publish'\r\n\t\t\t\t\t\tAND price_meta.meta_key IN ('" . implode("','", array_map('esc_sql', apply_filters('prdctfltr_price_meta_keys', array('_price', '_sale_price', '_min_variation_price', '_min_variation_sale_price', '_max_variation_price', '_max_variation_sale_price')))) . "')\r\n\t\t\t\t\t\tAND price_meta.meta_value > '' "; $sql .= $tax_query_sql['where'] . $meta_query_sql['where']; $prices = $wpdb->get_row($sql); if ($prices->min_price <= 0 && $prices->min_price <= 0) { return self::get_filtered_price('no'); } else { return $prices; } }
/** * Execute the query * * @since 3.1.0 * * @param string|array $query_vars * @return int|array */ function query($query_vars) { global $wpdb; $defaults = array('author_email' => '', 'ID' => '', 'karma' => '', 'number' => '', 'offset' => '', 'orderby' => '', 'order' => 'DESC', 'parent' => '', 'post_ID' => '', 'post_id' => 0, 'post_author' => '', 'post_name' => '', 'post_parent' => '', 'post_status' => '', 'post_type' => '', 'tax_query' => '', 'status' => '', 'type' => '', 'user_id' => '', 'search' => '', 'count' => false); $this->query_vars = wp_parse_args($query_vars, $defaults); do_action_ref_array('pre_get_comments', array(&$this)); extract($this->query_vars, EXTR_SKIP); // $args can be whatever, only use the args defined in defaults to compute the key $key = md5(serialize(compact(array_keys($defaults)))); $last_changed = wp_cache_get('last_changed', 'comment'); if (!$last_changed) { $last_changed = time(); wp_cache_set('last_changed', $last_changed, 'comment'); } $cache_key = "get_comments:{$key}:{$last_changed}"; if ($cache = wp_cache_get($cache_key, 'comment')) { return $cache; } $post_id = absint($post_id); if ('hold' == $status) { $approved = "comment_approved = '0'"; } elseif ('approve' == $status) { $approved = "comment_approved = '1'"; } elseif ('spam' == $status) { $approved = "comment_approved = 'spam'"; } elseif ('trash' == $status) { $approved = "comment_approved = 'trash'"; } else { $approved = "( comment_approved = '0' OR comment_approved = '1' )"; } $order = 'ASC' == strtoupper($order) ? 'ASC' : 'DESC'; if (!empty($orderby)) { $ordersby = is_array($orderby) ? $orderby : preg_split('/[,\\s]/', $orderby); $ordersby = array_intersect($ordersby, array('comment_agent', 'comment_approved', 'comment_author', 'comment_author_email', 'comment_author_IP', 'comment_author_url', 'comment_content', 'comment_date', 'comment_date_gmt', 'comment_ID', 'comment_karma', 'comment_parent', 'comment_post_ID', 'comment_type', 'user_id')); $orderby = empty($ordersby) ? 'comment_date_gmt' : implode(', ', $ordersby); } else { $orderby = 'comment_date_gmt'; } $number = absint($number); $offset = absint($offset); if (!empty($number)) { if ($offset) { $limits = 'LIMIT ' . $offset . ',' . $number; } else { $limits = 'LIMIT ' . $number; } } else { $limits = ''; } if ($count) { $fields = 'COUNT(*)'; } else { $fields = '*'; } $join = ''; $where = $approved; if (!empty($post_id)) { $where .= $wpdb->prepare(' AND comment_post_ID = %d', $post_id); } if ('' !== $author_email) { $where .= $wpdb->prepare(' AND comment_author_email = %s', $author_email); } if ('' !== $karma) { $where .= $wpdb->prepare(' AND comment_karma = %d', $karma); } if ('comment' == $type) { $where .= " AND comment_type = ''"; } elseif ('pings' == $type) { $where .= ' AND comment_type IN ("pingback", "trackback")'; } elseif (!empty($type)) { $where .= $wpdb->prepare(' AND comment_type = %s', $type); } if ('' !== $parent) { $where .= $wpdb->prepare(' AND comment_parent = %d', $parent); } if ('' !== $user_id) { $where .= $wpdb->prepare(' AND user_id = %d', $user_id); } if ('' !== $search) { $where .= $this->get_search_sql($search, array('comment_author', 'comment_author_email', 'comment_author_url', 'comment_author_IP', 'comment_content')); } $post_fields = array_filter(compact(array('post_author', 'post_name', 'post_parent', 'post_status', 'post_type'))); if (!empty($post_fields)) { $join = "JOIN {$wpdb->posts} ON {$wpdb->posts}.ID = {$wpdb->comments}.comment_post_ID"; foreach ($post_fields as $field_name => $field_value) { $where .= $wpdb->prepare(" AND {$wpdb->posts}.{$field_name} = %s", $field_value); } } if (array_key_exists('tax_query', $query_vars) && !empty($query_vars['tax_query'])) { $pq = new WP_Tax_Query($query_vars['tax_query']); $compact = $pq->get_sql($wpdb->posts, 'ID'); if (strlen($join) == 0) { $join = "JOIN {$wpdb->posts} ON {$wpdb->posts}.ID = {$wpdb->comments}.comment_post_ID"; } $join .= $compact['join']; $where .= $compact['where']; } $pieces = array('fields', 'join', 'where', 'orderby', 'order', 'limits'); $clauses = apply_filters_ref_array('comments_clauses', array(compact($pieces), &$this)); foreach ($pieces as $piece) { ${$piece} = isset($clauses[$piece]) ? $clauses[$piece] : ''; } $query = "SELECT {$fields} FROM {$wpdb->comments} {$join} WHERE {$where} ORDER BY {$orderby} {$order} {$limits}"; if ($count) { return $wpdb->get_var($query); } $comments = $wpdb->get_results($query); $comments = apply_filters_ref_array('the_comments', array($comments, &$this)); wp_cache_add($cache_key, $comments, 'comment'); return $comments; }
/** * Count products within certain terms, taking the main WP query into consideration. * @param array $term_ids * @param string $taxonomy * @param string $query_type * @return array */ protected function get_filtered_term_product_counts($term_ids, $taxonomy, $query_type) { global $wpdb; $tax_query = WC_Query::get_main_tax_query(); $meta_query = WC_Query::get_main_meta_query(); if ('or' === $query_type) { foreach ($tax_query as $key => $query) { if ($taxonomy === $query['taxonomy']) { unset($tax_query[$key]); } } } $meta_query = new WP_Meta_Query($meta_query); $tax_query = new WP_Tax_Query($tax_query); $meta_query_sql = $meta_query->get_sql('post', $wpdb->posts, 'ID'); $tax_query_sql = $tax_query->get_sql($wpdb->posts, 'ID'); $sql = "\n\t\t\tSELECT COUNT( {$wpdb->posts}.ID ) as term_count, term_count_relationships.term_taxonomy_id as term_count_id FROM {$wpdb->posts}\n\t\t\tINNER JOIN {$wpdb->term_relationships} AS term_count_relationships ON ({$wpdb->posts}.ID = term_count_relationships.object_id)\n\t\t\t" . $tax_query_sql['join'] . $meta_query_sql['join'] . "\n\t\t\tWHERE {$wpdb->posts}.post_type = 'product' AND {$wpdb->posts}.post_status = 'publish'\n\t\t\t" . $tax_query_sql['where'] . $meta_query_sql['where'] . "\n\t\t\tAND term_count_relationships.term_taxonomy_id IN (" . implode(',', array_map('absint', $term_ids)) . ")\n\t\t\tGROUP BY term_count_relationships.term_taxonomy_id;\n\t\t"; $results = $wpdb->get_results($sql); return wp_list_pluck($results, 'term_count', 'term_count_id'); }
/** * Inject the tax_query SQL in SearchWP's WHERE * * @since 2.6 * * @param $sql * @param $engine * * @return string */ function tax_where($sql, $engine) { if ($engine != $this->engine || empty($this->tax_query) || !is_array($this->tax_query)) { return $sql; } global $wpdb; $tax_query = new WP_Tax_Query((array) $this->tax_query); $tq_sql = $tax_query->get_sql($wpdb->posts, 'ID'); return $sql . $tq_sql['where']; }
/** * Count products after other filters have occured by adjusting the main query. * @param int $rating * @return int */ protected function get_filtered_product_count($rating) { global $wpdb; $tax_query = WC_Query::get_main_tax_query(); $meta_query = WC_Query::get_main_meta_query(); // Unset current rating filter foreach ($meta_query as $key => $query) { if (!empty($query['rating_filter'])) { unset($meta_query[$key]); } } // Set new rating filter $meta_query[] = array('key' => '_wc_average_rating', 'value' => $rating, 'compare' => '>=', 'type' => 'DECIMAL', 'rating_filter' => true); $meta_query = new WP_Meta_Query($meta_query); $tax_query = new WP_Tax_Query($tax_query); $meta_query_sql = $meta_query->get_sql('post', $wpdb->posts, 'ID'); $tax_query_sql = $tax_query->get_sql($wpdb->posts, 'ID'); $sql = "SELECT COUNT( {$wpdb->posts}.ID ) FROM {$wpdb->posts} "; $sql .= $tax_query_sql['join'] . $meta_query_sql['join']; $sql .= " WHERE {$wpdb->posts}.post_type = 'product' AND {$wpdb->posts}.post_status = 'publish' "; $sql .= $tax_query_sql['where'] . $meta_query_sql['where']; return absint($wpdb->get_var($sql)); }
/** * Get SQL clause for group type(s). * * @since 2.6.0 * * @param string|array $group_types Group type(s). * @param string $operator 'IN' or 'NOT IN'. * @return string $clause SQL clause. */ protected static function get_sql_clause_for_group_types($group_types, $operator) { global $wpdb; // Sanitize operator. if ('NOT IN' !== $operator) { $operator = 'IN'; } // Parse and sanitize types. if (!is_array($group_types)) { $group_types = preg_split('/[,\\s+]/', $group_types); } $types = array(); foreach ($group_types as $gt) { if (bp_groups_get_group_type_object($gt)) { $types[] = $gt; } } $tax_query = new WP_Tax_Query(array(array('taxonomy' => 'bp_group_type', 'field' => 'name', 'operator' => $operator, 'terms' => $types))); $site_id = bp_get_taxonomy_term_site_id('bp_group_type'); $switched = false; if ($site_id !== get_current_blog_id()) { switch_to_blog($site_id); $switched = true; } $sql_clauses = $tax_query->get_sql('g', 'id'); $clause = ''; // The no_results clauses are the same between IN and NOT IN. if (false !== strpos($sql_clauses['where'], '0 = 1')) { $clause = self::strip_leading_and($sql_clauses['where']); // The tax_query clause generated for NOT IN can be used almost as-is. } elseif ('NOT IN' === $operator) { $clause = self::strip_leading_and($sql_clauses['where']); // IN clauses must be converted to a subquery. } elseif (preg_match('/' . $wpdb->term_relationships . '\\.term_taxonomy_id IN \\([0-9, ]+\\)/', $sql_clauses['where'], $matches)) { $clause = " g.id IN ( SELECT object_id FROM {$wpdb->term_relationships} WHERE {$matches[0]} )"; } if ($switched) { restore_current_blog(); } return $clause; }