/**
 * 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');
 }
Example #6
0
 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;
 }
Example #7
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');
 }
Example #9
0
 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;
 }