function get_sql() { global $wpdb; if (empty($this->queries)) { return array('join' => '', 'where' => ''); } $context_table = MainWP_WP_Stream_DB::$table_context; $main_table = MainWP_WP_Stream_DB::$table; $meta_id_column = 'meta_id'; $join = array(); $where = array(); $queries = $this->queries; $meta_query = new WP_Meta_Query(); foreach ($queries as $i => $query) { foreach ($query as $key => $args) { $type = $meta_query->get_cast_for_type(isset($args['type']) ? $args['type'] : ''); $value = isset($args['value']) ? $args['value'] : null; // Allow 'context' => array('val1', 'val2') as well if (is_null($value)) { $args = array('value' => $args); $value = $args['value']; } if (isset($args['compare'])) { $compare = strtoupper($args['compare']); } else { $compare = is_array($value) ? 'IN' : '='; } $operators = array('=', '!=', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN', 'REGEXP', 'NOT REGEXP', 'RLIKE'); if (!in_array($compare, $operators)) { $compare = '='; } if ('IN' === substr($compare, -2)) { if (!is_array($value)) { $value = preg_split('/[,\\s]+/', $value); } $compare_string = '(' . substr(str_repeat(',%s', count($value)), 1) . ')'; } elseif ('LIKE' === substr($compare, -4)) { $value = '%' . like_escape($value) . '%'; $compare_string = '%s'; } else { $compare_string = '%s'; } if (!empty($where[$i])) { $where[$i] .= ' AND '; } else { $where[$i] = ''; } $where[$i] = ' (' . $where[$i] . $wpdb->prepare("CAST({$context_table}.{$key} AS {$type}) {$compare} {$compare_string})", $value); } } $where = array_filter($where); if (empty($where)) { $where = ''; } else { $where = ' AND (' . implode("\n{$this->relation} ", $where) . ' )'; } $join = implode("\n", $join); return apply_filters_ref_array('get_context_sql', array(compact('join', 'where'), $this->queries)); }
/** * Parse and sanitize 'orderby' keys passed to the user query. * * @since 4.2.0 * @access protected * * @global wpdb $wpdb WordPress database abstraction object. * * @param string $orderby Alias for the field to order by. * @return string Value to used in the ORDER clause, if `$orderby` is valid. */ protected function parse_orderby($orderby) { global $wpdb; $meta_query_clauses = $this->meta_query->get_clauses(); $_orderby = ''; if (in_array($orderby, array('login', 'nicename', 'email', 'url', 'registered'))) { $_orderby = 'user_' . $orderby; } elseif (in_array($orderby, array('user_login', 'user_nicename', 'user_email', 'user_url', 'user_registered'))) { $_orderby = $orderby; } elseif ('name' == $orderby || 'display_name' == $orderby) { $_orderby = 'display_name'; } elseif ('post_count' == $orderby) { // todo: avoid the JOIN $where = get_posts_by_author_sql('post'); $this->query_from .= " LEFT OUTER JOIN (\n\t\t\t\tSELECT post_author, COUNT(*) as post_count\n\t\t\t\tFROM {$wpdb->posts}\n\t\t\t\t{$where}\n\t\t\t\tGROUP BY post_author\n\t\t\t) p ON ({$wpdb->users}.ID = p.post_author)\n\t\t\t"; $_orderby = 'post_count'; } elseif ('ID' == $orderby || 'id' == $orderby) { $_orderby = 'ID'; } elseif ('meta_value' == $orderby || $this->get('meta_key') == $orderby) { $_orderby = "{$wpdb->usermeta}.meta_value"; } elseif ('meta_value_num' == $orderby) { $_orderby = "{$wpdb->usermeta}.meta_value+0"; } elseif ('include' === $orderby && !empty($this->query_vars['include'])) { $include = wp_parse_id_list($this->query_vars['include']); $include_sql = implode(',', $include); $_orderby = "FIELD( {$wpdb->users}.ID, {$include_sql} )"; } elseif (isset($meta_query_clauses[$orderby])) { $meta_clause = $meta_query_clauses[$orderby]; $_orderby = sprintf("CAST(%s.meta_value AS %s)", esc_sql($meta_clause['alias']), esc_sql($meta_clause['cast'])); } return $_orderby; }
/** * @ticket 22096 */ function test_empty_value_sql() { global $wpdb; $query = new WP_Meta_Query(); $the_complex_query['meta_query'] = array( array( 'key' => 'my_first_key', 'value' => 'my_amazing_value' ), array( 'key' => 'my_second_key', 'compare' => 'NOT EXISTS' ), array( 'key' => 'my_third_key', 'value' => array( ), 'compare' => 'IN' ), ); $query->parse_query_vars( $the_complex_query ); $sql = $query->get_sql( 'post', $wpdb->posts, 'ID', $this ); // We should have 2 joins - one for my_first_key and one for my_second_key $this->assertEquals( 2, substr_count( $sql['join'], 'INNER JOIN' ) ); // The WHERE should check my_third_key against an unaliased table $this->assertEquals( 1, substr_count( $sql['where'], "$wpdb->postmeta.meta_key = 'my_third_key'" ) ); }
/** * Convert WP_Query query_var value into a human readable label * * @param array $query_vars WP_Query query vars. * @return string the label based on the given vars. */ public function wpToLabel($query_vars) { $meta_query = new WP_Meta_Query(); $meta_query->parse_query_vars($query_vars); $label = ''; if (count($meta_query->queries) > 0) { $expressionSet = new Lift_Expression_Set(strtolower($meta_query->relation)); foreach ($meta_query->queries as $subquery) { if ($subquery['key'] == $this->meta_key) { $label = (string) $subquery->value; } } } return $label; }
/** * terms_clauses * Clause updates for term queries. * * @param array $pieces The pieces of the sql query * @param array $taxonomies The taxonomies for the query * @param array $arguments The arguments for the query * * @return array $pieces * * @access public * @static * @since 1.0 */ public static function terms_clauses($pieces = array(), $taxonomies = array(), $arguments = array()) { if (!empty($arguments['meta_query'])) { $query = new WP_Meta_Query($arguments['meta_query']); $query->parse_query_vars($arguments); if (!empty($query->queries)) { $clauses = $query->get_sql('term', 'tt', 'term_id', $taxonomies); $pieces['join'] .= $clauses['join']; $pieces['where'] .= $clauses['where']; } } return $pieces; }
/** * Get the SQL for the 'meta_query' param in BP_Activity_Activity::get() * * We use WP_Meta_Query to do the heavy lifting of parsing the * meta_query array and creating the necessary SQL clauses. However, * since BP_Activity_Activity::get() builds its SQL differently than * WP_Query, we have to alter the return value (stripping the leading * AND keyword from the 'where' clause). * * @since 1.8.0 * * @param array $meta_query An array of meta_query filters. See the * documentation for {@link WP_Meta_Query} for details. * @return array $sql_array 'join' and 'where' clauses. */ protected static function get_meta_query_sql($meta_query = array()) { global $wpdb; $sql_array = array('join' => '', 'where' => ''); if (!empty($meta_query)) { $groups_meta_query = new WP_Meta_Query($meta_query); // WP_Meta_Query expects the table name at // $wpdb->group. $wpdb->groupmeta = buddypress()->groups->table_name_groupmeta; $meta_sql = $groups_meta_query->get_sql('group', 'g', 'id'); // BP_Groups_Group::get uses the comma syntax for table // joins, which means that we have to do some regex to // convert the INNER JOIN and move the ON clause to a // WHERE condition // // @todo It may be better in the long run to refactor // the more general query syntax to accord better with // BP/WP convention. preg_match_all('/JOIN (.+?) ON/', $meta_sql['join'], $matches_a); preg_match_all('/ON \\((.+?)\\)/', $meta_sql['join'], $matches_b); if (!empty($matches_a[1]) && !empty($matches_b[1])) { $sql_array['join'] = implode(',', $matches_a[1]) . ', '; $sql_array['where'] = $meta_sql['where'] . ' AND ' . implode(' AND ', $matches_b[1]); } } return $sql_array; }
function relevanssi_search($args) { global $wpdb, $relevanssi_variables; $relevanssi_table = $relevanssi_variables['relevanssi_table']; $filtered_args = apply_filters('relevanssi_search_filters', $args); extract($filtered_args); $hits = array(); $query_restrictions = ""; if (!isset($tax_query_relation)) { $tax_query_relation = "or"; } $tax_query_relation = strtolower($tax_query_relation); $term_tax_id = array(); $term_tax_ids = array(); $not_term_tax_ids = array(); $and_term_tax_ids = array(); if (is_array($tax_query)) { foreach ($tax_query as $row) { if ($row['field'] == 'slug') { $slug = $row['terms']; $numeric_slugs = array(); $slug_in = null; if (is_array($slug)) { $slugs = array(); $term_id = array(); foreach ($slug as $t_slug) { $term = get_term_by('slug', $t_slug, $row['taxonomy']); if (!$term && is_numeric($t_slug)) { $numeric_slugs[] = "'{$t_slug}'"; } else { $t_slug = sanitize_title($t_slug); $term_id[] = $term->term_id; $slugs[] = "'{$t_slug}'"; } } if (!empty($slugs)) { $slug_in = implode(',', $slugs); } } else { $term = get_term_by('slug', $slug, $row['taxonomy']); if (!$term && is_numeric($slug)) { $numeric_slugs[] = $slug; } else { $term_id = $term->term_id; $slug_in = "'{$slug}'"; } } if (!empty($slug_in)) { $row_taxonomy = sanitize_text_field($row['taxonomy']); $tt_q = "SELECT tt.term_taxonomy_id\n\t\t\t\t\t\t \tFROM {$wpdb->term_taxonomy} AS tt\n\t\t\t\t\t\t \tLEFT JOIN {$wpdb->terms} AS t ON (tt.term_id=t.term_id)\n\t\t\t\t\t\t \tWHERE tt.taxonomy = '{$row_taxonomy}' AND t.slug IN ({$slug_in})"; // Clean: $row_taxonomy is sanitized, each slug in $slug_in is sanitized $term_tax_id = $wpdb->get_col($tt_q); } if (!empty($numeric_slugs)) { $row['field'] = 'id'; } } if ($row['field'] == 'id' || $row['field'] == 'term_id') { $id = $row['terms']; $term_id = $id; if (is_array($id)) { $numeric_values = array(); foreach ($id as $t_id) { if (is_numeric($t_id)) { $numeric_values[] = $t_id; } } $id = implode(',', $numeric_values); } $row_taxonomy = sanitize_text_field($row['taxonomy']); $tt_q = "SELECT tt.term_taxonomy_id\n\t\t\t\t \tFROM {$wpdb->term_taxonomy} AS tt\n\t\t\t\t \tLEFT JOIN {$wpdb->terms} AS t ON (tt.term_id=t.term_id)\n\t\t\t\t \tWHERE tt.taxonomy = '{$row_taxonomy}' AND t.term_id IN ({$id})"; // Clean: $row_taxonomy is sanitized, $id is checked to be numeric $id_term_tax_id = $wpdb->get_col($tt_q); if (!empty($term_tax_id) && is_array($term_tax_id)) { $term_tax_id = array_unique(array_merge($term_tax_id, $id_term_tax_id)); } else { $term_tax_id = $id_term_tax_id; } } if (!isset($row['include_children']) || $row['include_children'] == true) { if (!is_array($term_id)) { $term_id = array($term_id); } foreach ($term_id as $t_id) { $kids = get_term_children($t_id, $row['taxonomy']); foreach ($kids as $kid) { $term = get_term_by('id', $kid, $row['taxonomy']); $term_tax_id[] = relevanssi_get_term_tax_id('id', $kid, $row['taxonomy']); } } } $term_tax_id = array_unique($term_tax_id); if (!empty($term_tax_id)) { $n = count($term_tax_id); $term_tax_id = implode(',', $term_tax_id); $tq_operator = 'IN'; if (isset($row['operator'])) { $tq_operator = strtoupper($row['operator']); } if ($tq_operator != 'IN' && $tq_operator != 'NOT IN' && $tq_operator != 'AND') { $tq_operator = 'IN'; } if ($tax_query_relation == 'and') { if ($tq_operator == 'AND') { $query_restrictions .= " AND relevanssi.doc IN (\n\t\t\t\t\t\t\tSELECT ID FROM {$wpdb->posts} WHERE 1=1 \n\t\t\t\t\t\t\tAND (\n\t\t\t\t\t\t\t\tSELECT COUNT(1) \n\t\t\t\t\t\t\t\tFROM {$wpdb->term_relationships} AS tr\n\t\t\t\t\t\t\t\tWHERE tr.term_taxonomy_id IN ({$term_tax_id}) \n\t\t\t\t\t\t\t\tAND tr.object_id = {$wpdb->posts}.ID ) = {$n}\n\t\t\t\t\t\t\t)"; // Clean: $term_tax_id and $n are Relevanssi-generated } else { $query_restrictions .= " AND relevanssi.doc {$tq_operator} (SELECT DISTINCT(tr.object_id) FROM {$wpdb->term_relationships} AS tr\n\t\t\t\t\t\tWHERE tr.term_taxonomy_id IN ({$term_tax_id}))"; // Clean: all variables are Relevanssi-generated } } else { if ($tq_operator == 'IN') { $term_tax_ids[] = $term_tax_id; } if ($tq_operator == 'NOT IN') { $not_term_tax_ids[] = $term_tax_id; } if ($tq_operator == 'AND') { $and_term_tax_ids[] = $term_tax_id; } } } else { global $wp_query; $wp_query->is_category = false; } } if ($tax_query_relation == 'or') { $term_tax_ids = array_unique($term_tax_ids); if (count($term_tax_ids) > 0) { $term_tax_ids = implode(',', $term_tax_ids); $query_restrictions .= " AND relevanssi.doc IN (SELECT DISTINCT(tr.object_id) FROM {$wpdb->term_relationships} AS tr\n\t\t\t \tWHERE tr.term_taxonomy_id IN ({$term_tax_ids}))"; // Clean: all variables are Relevanssi-generated } if (count($not_term_tax_ids) > 0) { $not_term_tax_ids = implode(',', $not_term_tax_ids); $query_restrictions .= " AND relevanssi.doc NOT IN (SELECT DISTINCT(tr.object_id) FROM {$wpdb->term_relationships} AS tr\n\t\t\t \tWHERE tr.term_taxonomy_id IN ({$not_term_tax_ids}))"; // Clean: all variables are Relevanssi-generated } if (count($and_term_tax_ids) > 0) { $and_term_tax_ids = implode(',', $and_term_tax_ids); $n = count(explode(',', $and_term_tax_ids)); $query_restrictions .= " AND relevanssi.doc IN (\n\t\t\t\t\tSELECT ID FROM {$wpdb->posts} WHERE 1=1 \n\t\t\t\t\tAND (\n\t\t\t\t\t\tSELECT COUNT(1) \n\t\t\t\t\t\tFROM {$wpdb->term_relationships} AS tr\n\t\t\t\t\t\tWHERE tr.term_taxonomy_id IN ({$and_term_tax_ids}) \n\t\t\t\t\t\tAND tr.object_id = {$wpdb->posts}.ID ) = {$n}\n\t\t\t\t\t)"; // Clean: all variables are Relevanssi-generated } } } if (is_array($post_query)) { if (!empty($post_query['in'])) { $valid_values = array(); foreach ($post_query['in'] as $post_in_id) { if (is_numeric($post_in_id)) { $valid_values[] = $post_in_id; } } $posts = implode(',', $valid_values); if (!empty($posts)) { $query_restrictions .= " AND relevanssi.doc IN ({$posts})"; } // Clean: $posts is checked to be integers } if (!empty($post_query['not in'])) { $valid_values = array(); foreach ($post_query['not in'] as $post_not_in_id) { if (is_numeric($post_not_in_id)) { $valid_values[] = $post_not_in_id; } } $posts = implode(',', $valid_values); if (!empty($posts)) { $query_restrictions .= " AND relevanssi.doc NOT IN ({$posts})"; } // Clean: $posts is checked to be integers } } if (is_array($parent_query)) { if (!empty($parent_query['parent in'])) { $valid_values = array(); foreach ($parent_query['parent in'] as $post_in_id) { if (is_numeric($post_in_id)) { $valid_values[] = $post_in_id; } } $posts = implode(',', $valid_values); if (!empty($posts)) { $query_restrictions .= " AND relevanssi.doc IN (SELECT ID FROM {$wpdb->posts} WHERE post_parent IN ({$posts}))"; } // Clean: $posts is checked to be integers } if (!empty($parent_query['parent not in'])) { $valid_values = array(); foreach ($parent_query['parent not in'] as $post_not_in_id) { if (is_numeric($post_not_in_id)) { $valid_values[] = $post_not_in_id; } } $posts = implode(',', $valid_values); if (!empty($posts)) { $query_restrictions .= " AND relevanssi.doc NOT IN (SELECT ID FROM {$wpdb->posts} WHERE post_parent IN ({$posts}))"; } // Clean: $posts is checked to be integers } } if (is_array($meta_query)) { $meta_query_restrictions = ""; $mq_vars = array('meta_query' => $meta_query); $mq = new WP_Meta_Query(); $mq->parse_query_vars($mq_vars); $meta_sql = $mq->get_sql('post', 'relevanssi', 'doc'); $meta_join = ""; $meta_where = ""; if ($meta_sql) { $meta_join = $meta_sql['join']; $meta_where = $meta_sql['where']; } $query_restrictions .= $meta_where; } if (!empty($date_query)) { if (is_object($date_query) && method_exists($date_query, 'get_sql')) { $sql = $date_query->get_sql(); // AND ( the query itself ) $query_restrictions .= " AND relevanssi.doc IN ( SELECT DISTINCT(ID) FROM {$wpdb->posts} WHERE 1 {$sql} )"; // Clean: $sql generated by $date_query->get_sql() query } } if (!$post_type && get_option('relevanssi_respect_exclude') == 'on') { if (function_exists('get_post_types')) { $pt_1 = get_post_types(array('exclude_from_search' => '0')); $pt_2 = get_post_types(array('exclude_from_search' => false)); $post_type = implode(',', array_merge($pt_1, $pt_2)); } } if ($post_type) { if ($post_type == -1) { $post_type = null; } // Facetious sets post_type to -1 if not selected if (!is_array($post_type)) { $post_types = esc_sql(explode(',', $post_type)); } else { $post_types = esc_sql($post_type); } $post_type = count($post_types) ? "'" . implode("', '", $post_types) . "'" : 'NULL'; } if ($post_status) { if (!is_array($post_status)) { $post_statuses = esc_sql(explode(',', $post_status)); } else { $post_statuses = esc_sql($post_status); } $post_status = count($post_statuses) ? "'" . implode("', '", $post_statuses) . "'" : 'NULL'; } //Added by OdditY: //Exclude Post_IDs (Pages) for non-admin search -> $postex = ''; if (!empty($expost)) { if ($expost != "") { $aexpids = explode(",", $expost); foreach ($aexpids as $exid) { $exid = esc_sql(trim($exid, ' -')); $postex .= " AND relevanssi.doc != '{$exid}'"; // Clean: escaped } } } // <- OdditY End if ($expost) { //added by OdditY $query_restrictions .= $postex; } $remove_stopwords = true; if (function_exists('wp_encode_emoji')) { $q = wp_encode_emoji($q); } $phrases = relevanssi_recognize_phrases($q); if (function_exists('relevanssi_recognize_negatives')) { $negative_terms = relevanssi_recognize_negatives($q); } else { $negative_terms = false; } if (function_exists('relevanssi_recognize_positives')) { $positive_terms = relevanssi_recognize_positives($q); } else { $positive_terms = false; } $terms = relevanssi_tokenize($q, $remove_stopwords); if (count($terms) < 1) { // Tokenizer killed all the search terms. return $hits; } $terms = array_keys($terms); // don't care about tf in query if ($negative_terms) { $terms = array_diff($terms, $negative_terms); if (count($terms) < 1) { return $hits; } } // Go get the count from the options table, but keep running the full query if it's not available $D = get_option('relevanssi_doc_count'); if (!$D || $D < 1) { $D = $wpdb->get_var("SELECT COUNT(DISTINCT(relevanssi.doc)) FROM {$relevanssi_table} AS relevanssi"); // Clean: no external inputs update_option('relevanssi_doc_count', $D); } $total_hits = 0; $title_matches = array(); $tag_matches = array(); $comment_matches = array(); $link_matches = array(); $body_matches = array(); $category_matches = array(); $taxonomy_matches = array(); $scores = array(); $term_hits = array(); $fuzzy = get_option('relevanssi_fuzzy'); if (function_exists('relevanssi_negatives_positives')) { $query_restrictions .= relevanssi_negatives_positives($negative_terms, $positive_terms, $relevanssi_table); // Clean: escaped in the function } if (!empty($author)) { $author_in = array(); $author_not_in = array(); foreach ($author as $id) { if (!is_numeric($id)) { continue; } if ($id > 0) { $author_in[] = $id; } else { $author_not_in[] = abs($id); } } if (count($author_in) > 0) { $authors = implode(',', $author_in); $query_restrictions .= " AND relevanssi.doc IN (SELECT DISTINCT(posts.ID) FROM {$wpdb->posts} AS posts\n\t\t\t WHERE posts.post_author IN ({$authors}))"; // Clean: $authors is always just numbers } if (count($author_not_in) > 0) { $authors = implode(',', $author_not_in); $query_restrictions .= " AND relevanssi.doc NOT IN (SELECT DISTINCT(posts.ID) FROM {$wpdb->posts} AS posts\n\t\t\t WHERE posts.post_author IN ({$authors}))"; // Clean: $authors is always just numbers } } if ($post_type) { // the -1 is there to get user profiles and category pages $query_restrictions .= " AND ((relevanssi.doc IN (SELECT DISTINCT(posts.ID) FROM {$wpdb->posts} AS posts\n\t\t\tWHERE posts.post_type IN ({$post_type}))) OR (doc = -1))"; // Clean: $post_type is escaped } if ($post_status) { // the -1 is there to get user profiles and category pages $query_restrictions .= " AND ((relevanssi.doc IN (SELECT DISTINCT(posts.ID) FROM {$wpdb->posts} AS posts\n\t\t\tWHERE posts.post_status IN ({$post_status}))) OR (doc = -1))"; // Clean: $post_status is escaped } if ($phrases) { $query_restrictions .= " {$phrases}"; // Clean: $phrases is escaped earlier } if (isset($_REQUEST['by_date'])) { $n = $_REQUEST['by_date']; $u = substr($n, -1, 1); switch ($u) { case 'h': $unit = "HOUR"; break; case 'd': $unit = "DAY"; break; case 'm': $unit = "MONTH"; break; case 'y': $unit = "YEAR"; break; case 'w': $unit = "WEEK"; break; default: $unit = "DAY"; } $n = preg_replace('/[hdmyw]/', '', $n); if (is_numeric($n)) { $query_restrictions .= " AND relevanssi.doc IN (SELECT DISTINCT(posts.ID) FROM {$wpdb->posts} AS posts\n\t\t\t\tWHERE posts.post_date > DATE_SUB(NOW(), INTERVAL {$n} {$unit}))"; // Clean: $n is always numeric, $unit is Relevanssi-generated } } $query_restrictions = apply_filters('relevanssi_where', $query_restrictions); // Charles St-Pierre $query_join = ""; if (!empty($meta_join)) { $query_join = $meta_join; } $query_join = apply_filters('relevanssi_join', $query_join); $no_matches = true; if ("always" == $fuzzy) { $o_term_cond = apply_filters('relevanssi_fuzzy_query', "(relevanssi.term LIKE '#term#%' OR relevanssi.term_reverse LIKE CONCAT(REVERSE('#term#'), '%')) "); } else { $o_term_cond = " relevanssi.term = '#term#' "; } $post_type_weights = get_option('relevanssi_post_type_weights'); if (function_exists('relevanssi_get_recency_bonus')) { list($recency_bonus, $recency_cutoff_date) = relevanssi_get_recency_bonus(); } else { $recency_bonus = false; $recency_cutoff_date = false; } $min_length = get_option('relevanssi_min_word_length'); $search_again = false; $title_boost = floatval(get_option('relevanssi_title_boost')); $link_boost = floatval(get_option('relevanssi_link_boost')); $comment_boost = floatval(get_option('relevanssi_comment_boost')); $include_these_posts = array(); do { foreach ($terms as $term) { $term = trim($term); // numeric search terms will start with a space if (strlen($term) < $min_length) { continue; } $term = esc_sql($term); if (strpos($o_term_cond, 'LIKE') !== false) { // only like_escape() if necessary, otherwise _ in search terms will not work if (method_exists($wpdb, 'esc_like')) { $term = $wpdb->esc_like($term); } else { // Compatibility for pre-4.0 WordPress $term = like_escape($term); } } $term_cond = str_replace('#term#', $term, $o_term_cond); !empty($post_type_weights['post_tag']) ? $tag = $post_type_weights['post_tag'] : ($tag = $relevanssi_variables['post_type_weight_defaults']['post_tag']); !empty($post_type_weights['category']) ? $cat = $post_type_weights['category'] : ($cat = $relevanssi_variables['post_type_weight_defaults']['category']); $query = "SELECT relevanssi.*, relevanssi.title * {$title_boost} + relevanssi.content + relevanssi.comment * {$comment_boost} + relevanssi.tag * {$tag} + relevanssi.link * {$link_boost} + relevanssi.author + relevanssi.category * {$cat} + relevanssi.excerpt + relevanssi.taxonomy + relevanssi.customfield + relevanssi.mysqlcolumn AS tf \n\t\t\t\t\t FROM {$relevanssi_table} AS relevanssi {$query_join} WHERE {$term_cond} {$query_restrictions}"; // Clean: $query_restrictions is escaped, $term_cond is escaped $query = apply_filters('relevanssi_query_filter', $query); $matches = $wpdb->get_results($query); if (count($matches) < 1) { continue; } else { $no_matches = false; if (count($include_these_posts) > 0) { $post_ids_to_add = implode(',', array_keys($include_these_posts)); $existing_ids = array(); foreach ($matches as $match) { $existing_ids[] = $match->doc; } $existing_ids = implode(',', $existing_ids); $query = "SELECT relevanssi.*, relevanssi.title * {$title_boost} + relevanssi.content + relevanssi.comment * {$comment_boost} + relevanssi.tag * {$tag} + relevanssi.link * {$link_boost} + relevanssi.author + relevanssi.category * {$cat} + relevanssi.excerpt + relevanssi.taxonomy + relevanssi.customfield + relevanssi.mysqlcolumn AS tf \n\t\t\t\t\t\t FROM {$relevanssi_table} AS relevanssi WHERE relevanssi.doc IN ({$post_ids_to_add}) AND relevanssi.doc NOT IN ({$existing_ids}) AND {$term_cond}"; // Clean: no unescaped user inputs $matches_to_add = $wpdb->get_results($query); $matches = array_merge($matches, $matches_to_add); } } relevanssi_populate_array($matches); global $relevanssi_post_types; $total_hits += count($matches); $query = "SELECT COUNT(DISTINCT(relevanssi.doc)) FROM {$relevanssi_table} AS relevanssi {$query_join} WHERE {$term_cond} {$query_restrictions}"; // Clean: $query_restrictions is escaped, $term_cond is escaped $query = apply_filters('relevanssi_df_query_filter', $query); $df = $wpdb->get_var($query); if ($df < 1 && "sometimes" == $fuzzy) { $query = "SELECT COUNT(DISTINCT(relevanssi.doc)) FROM {$relevanssi_table} AS relevanssi {$query_join}\n\t\t\t\t\tWHERE (relevanssi.term LIKE '{$term}%' OR relevanssi.term_reverse LIKE CONCAT(REVERSE('{$term}), %')) {$query_restrictions}"; // Clean: $query_restrictions is escaped, $term is escaped $query = apply_filters('relevanssi_df_query_filter', $query); $df = $wpdb->get_var($query); } $idf = log($D + 1 / (1 + $df)); $idf = $idf * $idf; foreach ($matches as $match) { if ('user' == $match->type) { $match->doc = 'u_' . $match->item; } else { if (!in_array($match->type, array('post', 'attachment'))) { $match->doc = '**' . $match->type . '**' . $match->item; } } if (isset($match->taxonomy_detail)) { $match->taxonomy_score = 0; $match->taxonomy_detail = unserialize($match->taxonomy_detail); if (is_array($match->taxonomy_detail)) { foreach ($match->taxonomy_detail as $tax => $count) { if ($tax == 'post_tag') { $match->tag = $count; } if (empty($post_type_weights[$tax])) { $match->taxonomy_score += $count * 1; } else { $match->taxonomy_score += $count * $post_type_weights[$tax]; } } } } $match->tf = $match->title * $title_boost + $match->content + $match->comment * $comment_boost + $match->link * $link_boost + $match->author + $match->excerpt + $match->taxonomy_score + $match->customfield + $match->mysqlcolumn; $term_hits[$match->doc][$term] = $match->title + $match->content + $match->comment + $match->tag + $match->link + $match->author + $match->category + $match->excerpt + $match->taxonomy + $match->customfield + $match->mysqlcolumn; $match->weight = $match->tf * $idf; if ($recency_bonus) { $post = relevanssi_get_post($match->doc); if (strtotime($post->post_date) > $recency_cutoff_date) { $match->weight = $match->weight * $recency_bonus['bonus']; } } isset($body_matches[$match->doc]) ? $body_matches[$match->doc] += $match->content : ($body_matches[$match->doc] = $match->content); isset($title_matches[$match->doc]) ? $title_matches[$match->doc] += $match->title : ($title_matches[$match->doc] = $match->title); isset($link_matches[$match->doc]) ? $link_matches[$match->doc] += $match->link : ($link_matches[$match->doc] = $match->link); isset($tag_matches[$match->doc]) ? $tag_matches[$match->doc] += $match->tag : ($tag_matches[$match->doc] = $match->tag); isset($category_matches[$match->doc]) ? $category_matches[$match->doc] += $match->category : ($category_matches[$match->doc] = $match->category); isset($taxonomy_matches[$match->doc]) ? $taxonomy_matches[$match->doc] += $match->taxonomy : ($taxonomy_matches[$match->doc] = $match->taxonomy); isset($comment_matches[$match->doc]) ? $comment_matches[$match->doc] += $match->comment : ($comment_matches[$match->doc] = $match->comment); isset($relevanssi_post_types[$match->doc]) ? $type = $relevanssi_post_types[$match->doc] : ($type = null); if (!empty($post_type_weights[$type])) { $match->weight = $match->weight * $post_type_weights[$type]; } $match = apply_filters('relevanssi_match', $match, $idf); if ($match->weight == 0) { continue; } // the filters killed the match $post_ok = true; $post_ok = apply_filters('relevanssi_post_ok', $post_ok, $match->doc); if ($post_ok) { $doc_terms[$match->doc][$term] = true; // count how many terms are matched to a doc isset($doc_weight[$match->doc]) ? $doc_weight[$match->doc] += $match->weight : ($doc_weight[$match->doc] = $match->weight); isset($scores[$match->doc]) ? $scores[$match->doc] += $match->weight : ($scores[$match->doc] = $match->weight); if (is_numeric($match->doc)) { // this is to weed out taxonomies and users (t_XXX, u_XXX) $include_these_posts[$match->doc] = true; } } } } if (!isset($doc_weight)) { $no_matches = true; } if ($no_matches) { if ($search_again) { // no hits even with fuzzy search! $search_again = false; } else { if ("sometimes" == $fuzzy) { $search_again = true; $o_term_cond = "(term LIKE '%#term#' OR term LIKE '#term#%') "; } } } else { $search_again = false; } } while ($search_again); $strip_stops = true; $temp_terms_without_stops = array_keys(relevanssi_tokenize(implode(' ', $terms), $strip_stops)); $terms_without_stops = array(); foreach ($temp_terms_without_stops as $temp_term) { if (strlen($temp_term) >= $min_length) { array_push($terms_without_stops, $temp_term); } } $total_terms = count($terms_without_stops); if (isset($doc_weight)) { $doc_weight = apply_filters('relevanssi_results', $doc_weight); } if (isset($doc_weight) && count($doc_weight) > 0) { arsort($doc_weight); $i = 0; foreach ($doc_weight as $doc => $weight) { if (count($doc_terms[$doc]) < $total_terms && $operator == "AND") { // AND operator in action: // doc didn't match all terms, so it's discarded continue; } if (!empty($fields)) { if ($fields == 'ids') { $hits[intval($i)] = $doc; } if ($fields == 'id=>parent') { $object = new StdClass(); $object->ID = $doc; $object->post_parent = wp_get_post_parent_id($doc); $hits[intval($i)] = $object; } } else { $hits[intval($i)] = relevanssi_get_post($doc); $hits[intval($i)]->relevance_score = round($weight, 2); } $i++; } } if (count($hits) < 1) { if ($operator == "AND" and get_option('relevanssi_disable_or_fallback') != 'on') { $or_args = $args; $or_args['operator'] = "OR"; $or_args['q'] = relevanssi_add_synonyms($q); $return = relevanssi_search($or_args); extract($return); } } global $wp; $default_order = get_option('relevanssi_default_orderby', 'relevance'); if (empty($orderby)) { $orderby = $default_order; } // the sorting function checks for non-existing keys, cannot whitelist here if (empty($order)) { $order = 'desc'; } $order = strtolower($order); $order_accepted_values = array('asc', 'desc'); if (!in_array($order, $order_accepted_values)) { $order = 'desc'; } $orderby = apply_filters('relevanssi_orderby', $orderby); $order = apply_filters('relevanssi_order', $order); if ($orderby != 'relevance') { relevanssi_object_sort($hits, $orderby, $order); } $return = array('hits' => $hits, 'body_matches' => $body_matches, 'title_matches' => $title_matches, 'tag_matches' => $tag_matches, 'category_matches' => $category_matches, 'taxonomy_matches' => $taxonomy_matches, 'comment_matches' => $comment_matches, 'scores' => $scores, 'term_hits' => $term_hits, 'query' => $q, 'link_matches' => $link_matches); return $return; }
/** * @ticket 23033 */ function test_get_cast_for_type() { $query = new WP_Meta_Query(); $this->assertEquals( 'BINARY', $query->get_cast_for_type( 'BINARY' ) ); $this->assertEquals( 'CHAR', $query->get_cast_for_type( 'CHAR' ) ); $this->assertEquals( 'DATE', $query->get_cast_for_type( 'DATE' ) ); $this->assertEquals( 'DATETIME', $query->get_cast_for_type( 'DATETIME' ) ); $this->assertEquals( 'SIGNED', $query->get_cast_for_type( 'SIGNED' ) ); $this->assertEquals( 'UNSIGNED', $query->get_cast_for_type( 'UNSIGNED' ) ); $this->assertEquals( 'TIME', $query->get_cast_for_type( 'TIME' ) ); $this->assertEquals( 'SIGNED', $query->get_cast_for_type( 'NUMERIC' ) ); $this->assertEquals( 'NUMERIC(10)', $query->get_cast_for_type( 'NUMERIC(10)' ) ); $this->assertEquals( 'CHAR', $query->get_cast_for_type( 'NUMERIC( 10)' ) ); $this->assertEquals( 'CHAR', $query->get_cast_for_type( 'NUMERIC( 10 )' ) ); $this->assertEquals( 'NUMERIC(10, 5)', $query->get_cast_for_type( 'NUMERIC(10, 5)' ) ); $this->assertEquals( 'CHAR', $query->get_cast_for_type( 'NUMERIC(10, 5)' ) ); $this->assertEquals( 'NUMERIC(10,5)', $query->get_cast_for_type( 'NUMERIC(10,5)' ) ); $this->assertEquals( 'CHAR', $query->get_cast_for_type( 'NUMERIC( 10, 5 )' ) ); $this->assertEquals( 'CHAR', $query->get_cast_for_type( 'NUMERIC(10, 5 )' ) ); $this->assertEquals( 'DECIMAL', $query->get_cast_for_type( 'DECIMAL' ) ); $this->assertEquals( 'DECIMAL(10)', $query->get_cast_for_type( 'DECIMAL(10)' ) ); $this->assertEquals( 'CHAR', $query->get_cast_for_type( 'DECIMAL( 10 )' ) ); $this->assertEquals( 'CHAR', $query->get_cast_for_type( 'DECIMAL( 10)' ) ); $this->assertEquals( 'CHAR', $query->get_cast_for_type( 'DECIMAL(10 )' ) ); $this->assertEquals( 'DECIMAL(10, 5)', $query->get_cast_for_type( 'DECIMAL(10, 5)' ) ); $this->assertEquals( 'DECIMAL(10,5)', $query->get_cast_for_type( 'DECIMAL(10,5)' ) ); $this->assertEquals( 'CHAR', $query->get_cast_for_type( 'DECIMAL(10, 5)' ) ); $this->assertEquals( 'CHAR', $query->get_cast_for_type( 'ANYTHING ELSE' ) ); }
/** * Inject the meta_query SQL in SearchWP's WHERE * * @since 2.6 * * @param $sql * @param $engine * * @return string */ function meta_where($sql, $engine) { if ($engine != $this->engine || empty($this->meta_query) || !is_array($this->meta_query)) { return $sql; } global $wpdb; $meta_query = new WP_Meta_Query($this->meta_query); $mq_sql = $meta_query->get_sql('post', $wpdb->posts, 'ID', null); return $sql . $mq_sql['where']; }
/** * Get the SQL for the 'meta_query' param in BP_Notifications_Notification::get(). * * We use WP_Meta_Query to do the heavy lifting of parsing the * meta_query array and creating the necessary SQL clauses. However, * since BP_Notifications_Notification::get() builds its SQL differently than * WP_Query, we have to alter the return value (stripping the leading * AND keyword from the 'where' clause). * * @since 2.3.0 * * @param array $meta_query An array of meta_query filters. See the * documentation for WP_Meta_Query for details. * @return array $sql_array 'join' and 'where' clauses. */ public static function get_meta_query_sql($meta_query = array()) { // Default array keys & empty values. $sql_array = array('join' => '', 'where' => ''); // Bail if no meta query. if (empty($meta_query)) { return $sql_array; } // WP_Meta_Query expects the table name at $wpdb->notificationmeta. $GLOBALS['wpdb']->notificationmeta = buddypress()->notifications->table_name_meta; $n_meta_query = new WP_Meta_Query($meta_query); $meta_sql = $n_meta_query->get_sql('notification', 'n', 'id'); // Strip the leading AND - it's handled in get(). $sql_array['where'] = preg_replace('/^\\sAND/', '', $meta_sql['where']); $sql_array['join'] = $meta_sql['join']; return $sql_array; }
/** * Initialize the class * @param string|array $args */ public function __construct($args = '') { global $wpdb; ap_wpdb_tables(); $this->per_page = 20; // Grab the current page number and set to 1 if no page number is set. $this->paged = isset($args['paged']) ? (int) $args['paged'] : 1; $this->offset = $this->per_page * ($this->paged - 1); $this->args = wp_parse_args($args, array('number' => $this->per_page, 'offset' => $this->offset, 'orderby' => 'date', 'order' => 'DESC', 'notification' => false)); // Process meta query arguments. if (isset($this->args['meta_query'])) { $meta_query = new WP_Meta_Query(); $meta_query->parse_query_vars($this->args['meta_query']); $this->meta_query_sql = $meta_query->get_sql('ap_activity', $wpdb->ap_activity, 'id', null); } $this->parse_query(); $this->total_activity_count = $wpdb->get_var(apply_filters('ap_found_activity_query', 'SELECT FOUND_ROWS()', $this)); }
/** * @group 32592 */ public function test_has_or_relation_should_return_true_for_nested_or() { $q = new WP_Meta_Query(array('relation' => 'AND', array('key' => 'foo', 'value' => 'bar'), array('relation' => 'OR', array('key' => 'foo1', 'value' => 'bar'), array('key' => 'foo2', 'value' => 'bar')))); $this->assertTrue($q->has_or_relation()); }
/** * 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'); }
/** * Filter `term_clauses` and add support for a `meta_query` argument * * @since 0.1.0 * * @param array $pieces Terms query SQL clauses. * @param array $taxonomies An array of taxonomies. * @param array $args An array of terms query arguments. * * @return Array of query pieces, maybe modifed */ public function terms_clauses($pieces = array(), $taxonomies = array(), $args = array()) { // Maybe do a meta query if (!empty($args['meta_query'])) { // Make doubly sure global database object is prepared $this->add_termmeta_to_db_object(); // Get the meta query parts $meta_query = new WP_Meta_Query($args['meta_query']); $meta_query->parse_query_vars($args); // Combine pieces & meta-query clauses if (!empty($meta_query->queries)) { /** * It's possible in a future version of WordPress that our * `term_id` usage might need to be swapped to `term_taxonomy_id`. */ $meta_clauses = $meta_query->get_sql('term', 'tt', 'term_id', $taxonomies); $pieces['join'] .= $meta_clauses['join']; $pieces['where'] .= $meta_clauses['where']; } } // Return possibly modified pieces array return $pieces; }
function test_empty_compare() { global $wpdb; $query = new WP_Meta_Query(array('relation' => 'OR', array('key' => 'exclude', 'compare' => ''), array('key' => 'exclude', 'compare' => '!=', 'value' => '1'))); $sql = $query->get_sql('post', $wpdb->posts, 'ID', $this); $this->assertContains("{$wpdb->postmeta}.meta_key = 'exclude'\nOR", $sql['where']); $this->assertNotContains("{$wpdb->postmeta}.post_id IS NULL", $sql['where']); }
/** * 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'); }
/** * Get the SQL for the 'meta_query' param in BP_Activity_Activity::get() * * We use WP_Meta_Query to do the heavy lifting of parsing the * meta_query array and creating the necessary SQL clauses. However, * since BP_Activity_Activity::get() builds its SQL differently than * WP_Query, we have to alter the return value (stripping the leading * AND keyword from the 'where' clause). * * @since BuddyPress (1.8) * * @param array $meta_query An array of meta_query filters. See the * documentation for WP_Meta_Query for details. * @return array $sql_array 'join' and 'where' clauses */ public static function get_meta_query_sql($meta_query = array()) { global $wpdb; $sql_array = array('join' => '', 'where' => ''); if (!empty($meta_query)) { $activity_meta_query = new WP_Meta_Query($meta_query); // WP_Meta_Query expects the table name at // $wpdb->activitymeta $wpdb->activitymeta = buddypress()->activity->table_name_meta; $meta_sql = $activity_meta_query->get_sql('activity', 'a', 'id'); // Strip the leading AND - BP handles it in get() $sql_array['where'] = preg_replace('/^\\sAND/', '', $meta_sql['where']); $sql_array['join'] = $meta_sql['join']; } return $sql_array; }
/** * 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)); }
function hm_add_term_meta_query_support($pieces, $taxonomies, $args) { if (empty($args['meta_query'])) { return $pieces; } $meta_query = new WP_Meta_Query($args['meta_query']); $sql = $meta_query->get_sql('term', 't', 'term_id'); if (!$sql) { return $pieces; } $pieces['join'] .= $sql['join']; $pieces['where'] .= $sql['where']; return $pieces; }
/** * Prepare the query variables. * * @since 3.1.0 * * @param string|array $args Optional. The query variables. */ function prepare_query($query = array()) { global $wpdb; if (empty($this->query_vars) || !empty($query)) { $this->query_limit = null; $this->query_vars = wp_parse_args($query, array('blog_id' => $GLOBALS['blog_id'], 'role' => '', 'meta_key' => '', 'meta_value' => '', 'meta_compare' => '', 'include' => array(), 'exclude' => array(), 'search' => '', 'search_columns' => array(), 'orderby' => 'login', 'order' => 'ASC', 'offset' => '', 'number' => '', 'count_total' => true, 'fields' => 'all', 'who' => '')); } $qv =& $this->query_vars; if (is_array($qv['fields'])) { $qv['fields'] = array_unique($qv['fields']); $this->query_fields = array(); foreach ($qv['fields'] as $field) { $field = 'ID' === $field ? 'ID' : sanitize_key($field); $this->query_fields[] = "{$wpdb->users}.{$field}"; } $this->query_fields = implode(',', $this->query_fields); } elseif ('all' == $qv['fields']) { $this->query_fields = "{$wpdb->users}.*"; } else { $this->query_fields = "{$wpdb->users}.ID"; } if (isset($qv['count_total']) && $qv['count_total']) { $this->query_fields = 'SQL_CALC_FOUND_ROWS ' . $this->query_fields; } $this->query_from = "FROM {$wpdb->users}"; $this->query_where = "WHERE 1=1"; // sorting if (isset($qv['orderby'])) { if (in_array($qv['orderby'], array('nicename', 'email', 'url', 'registered'))) { $orderby = 'user_' . $qv['orderby']; } elseif (in_array($qv['orderby'], array('user_nicename', 'user_email', 'user_url', 'user_registered'))) { $orderby = $qv['orderby']; } elseif ('name' == $qv['orderby'] || 'display_name' == $qv['orderby']) { $orderby = 'display_name'; } elseif ('post_count' == $qv['orderby']) { // todo: avoid the JOIN $where = get_posts_by_author_sql('post'); $this->query_from .= " LEFT OUTER JOIN (\r\n\t\t\t\t\tSELECT post_author, COUNT(*) as post_count\r\n\t\t\t\t\tFROM {$wpdb->posts}\r\n\t\t\t\t\t{$where}\r\n\t\t\t\t\tGROUP BY post_author\r\n\t\t\t\t) p ON ({$wpdb->users}.ID = p.post_author)\r\n\t\t\t\t"; $orderby = 'post_count'; } elseif ('ID' == $qv['orderby'] || 'id' == $qv['orderby']) { $orderby = 'ID'; } elseif ('meta_value' == $qv['orderby']) { $orderby = "{$wpdb->usermeta}.meta_value"; } else { $orderby = 'user_login'; } } if (empty($orderby)) { $orderby = 'user_login'; } $qv['order'] = isset($qv['order']) ? strtoupper($qv['order']) : ''; if ('ASC' == $qv['order']) { $order = 'ASC'; } else { $order = 'DESC'; } $this->query_orderby = "ORDER BY {$orderby} {$order}"; // limit if (isset($qv['number']) && $qv['number']) { if ($qv['offset']) { $this->query_limit = $wpdb->prepare("LIMIT %d, %d", $qv['offset'], $qv['number']); } else { $this->query_limit = $wpdb->prepare("LIMIT %d", $qv['number']); } } $search = ''; if (isset($qv['search'])) { $search = trim($qv['search']); } if ($search) { $leading_wild = ltrim($search, '*') != $search; $trailing_wild = rtrim($search, '*') != $search; if ($leading_wild && $trailing_wild) { $wild = 'both'; } elseif ($leading_wild) { $wild = 'leading'; } elseif ($trailing_wild) { $wild = 'trailing'; } else { $wild = false; } if ($wild) { $search = trim($search, '*'); } $search_columns = array(); if ($qv['search_columns']) { $search_columns = array_intersect($qv['search_columns'], array('ID', 'user_login', 'user_email', 'user_url', 'user_nicename')); } if (!$search_columns) { if (false !== strpos($search, '@')) { $search_columns = array('user_email'); } elseif (is_numeric($search)) { $search_columns = array('user_login', 'ID'); } elseif (preg_match('|^https?://|', $search) && !(is_multisite() && wp_is_large_network('users'))) { $search_columns = array('user_url'); } else { $search_columns = array('user_login', 'user_nicename'); } } /** * Filter the columns to search in a WP_User_Query search. * * The default columns depend on the search term, and include 'user_email', * 'user_login', 'ID', 'user_url', and 'user_nicename'. * * @since 3.6.0 * * @param array $search_columns Array of column names to be searched. * @param string $search Text being searched. * @param WP_User_Query $this The current WP_User_Query instance. */ $search_columns = apply_filters('user_search_columns', $search_columns, $search, $this); $this->query_where .= $this->get_search_sql($search, $search_columns, $wild); } $blog_id = 0; if (isset($qv['blog_id'])) { $blog_id = absint($qv['blog_id']); } if (isset($qv['who']) && 'authors' == $qv['who'] && $blog_id) { $qv['meta_key'] = $wpdb->get_blog_prefix($blog_id) . 'user_level'; $qv['meta_value'] = 0; $qv['meta_compare'] = '!='; $qv['blog_id'] = $blog_id = 0; // Prevent extra meta query } $role = ''; if (isset($qv['role'])) { $role = trim($qv['role']); } if ($blog_id && ($role || is_multisite())) { $cap_meta_query = array(); $cap_meta_query['key'] = $wpdb->get_blog_prefix($blog_id) . 'capabilities'; if ($role) { $cap_meta_query['value'] = '"' . $role . '"'; $cap_meta_query['compare'] = 'like'; } if (empty($qv['meta_query']) || !in_array($cap_meta_query, $qv['meta_query'], true)) { $qv['meta_query'][] = $cap_meta_query; } } $meta_query = new WP_Meta_Query(); $meta_query->parse_query_vars($qv); if (!empty($meta_query->queries)) { $clauses = $meta_query->get_sql('user', $wpdb->users, 'ID', $this); $this->query_from .= $clauses['join']; $this->query_where .= $clauses['where']; if ('OR' == $meta_query->relation) { $this->query_fields = 'DISTINCT ' . $this->query_fields; } } if (!empty($qv['include'])) { $ids = implode(',', wp_parse_id_list($qv['include'])); $this->query_where .= " AND {$wpdb->users}.ID IN ({$ids})"; } elseif (!empty($qv['exclude'])) { $ids = implode(',', wp_parse_id_list($qv['exclude'])); $this->query_where .= " AND {$wpdb->users}.ID NOT IN ({$ids})"; } /** * Fires after the WP_User_Query has been parsed, and before * the query is executed. * * The passed WP_User_Query object contains SQL parts formed * from parsing the given query. * * @since 3.1.0 * * @param WP_User_Query $this The current WP_User_Query instance, * passed by reference. */ do_action_ref_array('pre_user_query', array(&$this)); }
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; } }
/** * 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 the SQL for the 'meta_query' param in BP_Messages_Thread::get_current_threads_for_user(). * * We use WP_Meta_Query to do the heavy lifting of parsing the meta_query array * and creating the necessary SQL clauses. * * @since 2.2.0 * * @param array $meta_query An array of meta_query filters. See the * documentation for WP_Meta_Query for details. * * @return array $sql_array 'join' and 'where' clauses. */ public static function get_meta_query_sql($meta_query = array()) { global $wpdb; $sql_array = array('join' => '', 'where' => ''); if (!empty($meta_query)) { $meta_query = new WP_Meta_Query($meta_query); // WP_Meta_Query expects the table name at // $wpdb->messagemeta $wpdb->messagemeta = buddypress()->messages->table_name_meta; return $meta_query->get_sql('message', 'm', 'id'); } return $sql_array; }
public function query($args) { global $wpdb; $defaults = array('records_per_page' => get_option('posts_per_page'), 'paged' => 1, 'search' => null, 'type' => 'stream', 'object_id' => null, 'ip' => null, 'site_id' => is_multisite() ? get_current_site()->id : 1, 'blog_id' => is_network_admin() ? null : get_current_blog_id(), 'author' => null, 'author_role' => null, 'date' => null, 'date_from' => null, 'date_to' => null, 'visibility' => null, 'record_greater_than' => null, 'record__in' => array(), 'record__not_in' => array(), 'record_parent' => '', 'record_parent__in' => array(), 'record_parent__not_in' => array(), 'author__in' => array(), 'author__not_in' => array(), 'author_role__in' => array(), 'author_role__not_in' => array(), 'ip__in' => array(), 'ip__not_in' => array(), 'order' => 'desc', 'orderby' => 'ID', 'meta_query' => array(), 'context_query' => array(), 'fields' => '', 'ignore_context' => null, 'hide_excluded' => !empty(MainWP_WP_Stream_Settings::$options['exclude_hide_previous_records'])); $args = wp_parse_args($args, $defaults); $args = apply_filters('mainwp_wp_stream_query_args', $args); if (true === $args['hide_excluded']) { $args = self::add_excluded_record_args($args); } $join = ''; $where = ''; // Only join with context table for correct types of records if (!$args['ignore_context']) { $join = sprintf(' INNER JOIN %1$s ON ( %1$s.record_id = %2$s.ID )', $wpdb->mainwp_reportscontext, $wpdb->mainwp_reports); } /** * PARSE CORE FILTERS */ if ($args['object_id']) { $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.object_id = %d", $args['object_id']); } if ($args['type']) { $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.type = %s", $args['type']); } if ($args['ip']) { $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.ip = %s", mainwp_wp_stream_filter_var($args['ip'], FILTER_VALIDATE_IP)); } if (is_numeric($args['site_id'])) { $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.site_id = %d", $args['site_id']); } if (is_numeric($args['blog_id'])) { $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.blog_id = %d", $args['blog_id']); } if ($args['search']) { $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.summary LIKE %s", "%{$args['search']}%"); } if ($args['author'] || '0' === $args['author']) { $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.author = %d", (int) $args['author']); } if ($args['author_role']) { $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.author_role = %s", $args['author_role']); } if ($args['visibility']) { $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.visibility = %s", $args['visibility']); } /** * PARSE DATE FILTERS */ if ($args['date']) { $where .= $wpdb->prepare(" AND DATE({$wpdb->mainwp_reports}.created) = %s", $args['date']); } else { if ($args['date_from']) { $where .= $wpdb->prepare(" AND DATE({$wpdb->mainwp_reports}.created) >= %s", $args['date_from']); } if ($args['date_to']) { $where .= $wpdb->prepare(" AND DATE({$wpdb->mainwp_reports}.created) <= %s", $args['date_to']); } } /** * PARSE __IN PARAM FAMILY */ if ($args['record_greater_than']) { $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.ID > %d", (int) $args['record_greater_than']); } if ($args['record__in']) { $record__in = array_filter((array) $args['record__in'], 'is_numeric'); if (!empty($record__in)) { $record__in_format = '(' . join(',', array_fill(0, count($record__in), '%d')) . ')'; $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.ID IN {$record__in_format}", $record__in); } } if ($args['record__not_in']) { $record__not_in = array_filter((array) $args['record__not_in'], 'is_numeric'); if (!empty($record__not_in)) { $record__not_in_format = '(' . join(',', array_fill(0, count($record__not_in), '%d')) . ')'; $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.ID NOT IN {$record__not_in_format}", $record__not_in); } } if ($args['record_parent']) { $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.parent = %d", (int) $args['record_parent']); } if ($args['record_parent__in']) { $record_parent__in = array_filter((array) $args['record_parent__in'], 'is_numeric'); if (!empty($record_parent__in)) { $record_parent__in_format = '(' . join(',', array_fill(0, count($record_parent__in), '%d')) . ')'; $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.parent IN {$record_parent__in_format}", $record_parent__in); } } if ($args['record_parent__not_in']) { $record_parent__not_in = array_filter((array) $args['record_parent__not_in'], 'is_numeric'); if (!empty($record_parent__not_in)) { $record_parent__not_in_format = '(' . join(',', array_fill(0, count($record_parent__not_in), '%d')) . ')'; $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.parent NOT IN {$record_parent__not_in_format}", $record_parent__not_in); } } if ($args['author__in']) { $author__in = array_filter((array) $args['author__in'], 'is_numeric'); if (!empty($author__in)) { $author__in_format = '(' . join(',', array_fill(0, count($author__in), '%d')) . ')'; $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.author IN {$author__in_format}", $author__in); } } if ($args['author__not_in']) { $author__not_in = array_filter((array) $args['author__not_in'], 'is_numeric'); if (!empty($author__not_in)) { $author__not_in_format = '(' . join(',', array_fill(0, count($author__not_in), '%d')) . ')'; $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.author NOT IN {$author__not_in_format}", $author__not_in); } } if ($args['author_role__in']) { if (!empty($args['author_role__in'])) { $author_role__in = '(' . join(',', array_fill(0, count($args['author_role__in']), '%s')) . ')'; $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.author_role IN {$author_role__in}", $args['author_role__in']); } } if ($args['author_role__not_in']) { if (!empty($args['author_role__not_in'])) { $author_role__not_in = '(' . join(',', array_fill(0, count($args['author_role__not_in']), '%s')) . ')'; $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.author_role NOT IN {$author_role__not_in}", $args['author_role__not_in']); } } if ($args['ip__in']) { if (!empty($args['ip__in'])) { $ip__in = '(' . join(',', array_fill(0, count($args['ip__in']), '%s')) . ')'; $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.ip IN {$ip__in}", $args['ip__in']); } } if ($args['ip__not_in']) { if (!empty($args['ip__not_in'])) { $ip__not_in = '(' . join(',', array_fill(0, count($args['ip__not_in']), '%s')) . ')'; $where .= $wpdb->prepare(" AND {$wpdb->mainwp_reports}.ip NOT IN {$ip__not_in}", $args['ip__not_in']); } } /** * PARSE META QUERY PARAMS */ $meta_query = new WP_Meta_Query(); $meta_query->parse_query_vars($args); if (!empty($meta_query->queries)) { $mclauses = $meta_query->get_sql('mainwp-child-reports', $wpdb->mainwp_reports, 'ID'); $join .= str_replace('stream_id', 'record_id', $mclauses['join']); $where .= str_replace('stream_id', 'record_id', $mclauses['where']); } /** * PARSE CONTEXT PARAMS */ if (!$args['ignore_context']) { $context_query = new MainWP_WP_Stream_Context_Query($args); $cclauses = $context_query->get_sql(); $join .= $cclauses['join']; $where .= $cclauses['where']; } /** * PARSE PAGINATION PARAMS */ $page = intval($args['paged']); $perpage = intval($args['records_per_page']); if ($perpage >= 0) { $offset = ($page - 1) * $perpage; $limits = "LIMIT {$offset}, {$perpage}"; } else { $limits = ''; } /** * PARSE ORDER PARAMS */ $order = esc_sql($args['order']); $orderby = esc_sql($args['orderby']); $orderable = array('ID', 'site_id', 'blog_id', 'object_id', 'author', 'author_role', 'summary', 'visibility', 'parent', 'type', 'created'); if (in_array($orderby, $orderable)) { $orderby = $wpdb->mainwp_reports . '.' . $orderby; } elseif (in_array($orderby, array('connector', 'context', 'action'))) { $orderby = $wpdb->mainwp_reportscontext . '.' . $orderby; } elseif ('meta_value_num' === $orderby && !empty($args['meta_key'])) { $orderby = "CAST({$wpdb->mainwp_reportsmeta}.meta_value AS SIGNED)"; } elseif ('meta_value' === $orderby && !empty($args['meta_key'])) { $orderby = "{$wpdb->mainwp_reportsmeta}.meta_value"; } else { $orderby = "{$wpdb->mainwp_reports}.ID"; } $orderby = 'ORDER BY ' . $orderby . ' ' . $order; /** * PARSE FIELDS PARAMETER */ $fields = $args['fields']; $select = "{$wpdb->mainwp_reports}.*"; if (!$args['ignore_context']) { $select .= ", {$wpdb->mainwp_reportscontext}.context, {$wpdb->mainwp_reportscontext}.action, {$wpdb->mainwp_reportscontext}.connector"; } if ('ID' === $fields) { $select = "{$wpdb->mainwp_reports}.ID"; } elseif ('summary' === $fields) { $select = "{$wpdb->mainwp_reports}.summary, {$wpdb->mainwp_reports}.ID"; } /** * BUILD UP THE FINAL QUERY */ $sql = "SELECT SQL_CALC_FOUND_ROWS {$select}\n\t\tFROM {$wpdb->mainwp_reports}\n\t\t{$join}\n\t\tWHERE 1=1 {$where}\n\t\t{$orderby}\n\t\t{$limits}"; $sql = apply_filters('mainwp_wp_stream_query', $sql, $args); $results = $wpdb->get_results($sql); if ('with-meta' === $fields && is_array($results) && $results) { $ids = array_map('absint', wp_list_pluck($results, 'ID')); $sql_meta = sprintf("SELECT * FROM {$wpdb->mainwp_reportsmeta} WHERE record_id IN ( %s )", implode(',', $ids)); $meta = $wpdb->get_results($sql_meta); $ids_f = array_flip($ids); foreach ($meta as $meta_record) { $results[$ids_f[$meta_record->record_id]]->meta[$meta_record->meta_key][] = $meta_record->meta_value; } } return $results; }
/** * Prepare the query variables * * @since 3.1.0 * @access private */ function prepare_query() { global $wpdb; $qv =& $this->query_vars; if (is_array($qv['fields'])) { $qv['fields'] = array_unique($qv['fields']); $this->query_fields = array(); foreach ($qv['fields'] as $field) { $this->query_fields[] = $wpdb->users . '.' . esc_sql($field); } $this->query_fields = implode(',', $this->query_fields); } elseif ('all' == $qv['fields']) { $this->query_fields = "{$wpdb->users}.*"; } else { $this->query_fields = "{$wpdb->users}.ID"; } if ($this->query_vars['count_total']) { $this->query_fields = 'SQL_CALC_FOUND_ROWS ' . $this->query_fields; } $this->query_from = "FROM {$wpdb->users}"; $this->query_where = "WHERE 1=1"; // sorting if (in_array($qv['orderby'], array('nicename', 'email', 'url', 'registered'))) { $orderby = 'user_' . $qv['orderby']; } elseif (in_array($qv['orderby'], array('user_nicename', 'user_email', 'user_url', 'user_registered'))) { $orderby = $qv['orderby']; } elseif ('name' == $qv['orderby'] || 'display_name' == $qv['orderby']) { $orderby = 'display_name'; } elseif ('post_count' == $qv['orderby']) { // todo: avoid the JOIN $where = get_posts_by_author_sql('post'); $this->query_from .= " LEFT OUTER JOIN (\n\t\t\t\tSELECT post_author, COUNT(*) as post_count\n\t\t\t\tFROM {$wpdb->posts}\n\t\t\t\t{$where}\n\t\t\t\tGROUP BY post_author\n\t\t\t) p ON ({$wpdb->users}.ID = p.post_author)\n\t\t\t"; $orderby = 'post_count'; } elseif ('ID' == $qv['orderby'] || 'id' == $qv['orderby']) { $orderby = 'ID'; } else { $orderby = 'user_login'; } $qv['order'] = strtoupper($qv['order']); if ('ASC' == $qv['order']) { $order = 'ASC'; } else { $order = 'DESC'; } $this->query_orderby = "ORDER BY {$orderby} {$order}"; // limit if ($qv['number']) { if ($qv['offset']) { $this->query_limit = $wpdb->prepare("LIMIT %d, %d", $qv['offset'], $qv['number']); } else { $this->query_limit = $wpdb->prepare("LIMIT %d", $qv['number']); } } $search = trim($qv['search']); if ($search) { $leading_wild = ltrim($search, '*') != $search; $trailing_wild = rtrim($search, '*') != $search; if ($leading_wild && $trailing_wild) { $wild = 'both'; } elseif ($leading_wild) { $wild = 'leading'; } elseif ($trailing_wild) { $wild = 'trailing'; } else { $wild = false; } if ($wild) { $search = trim($search, '*'); } if (false !== strpos($search, '@')) { $search_columns = array('user_email'); } elseif (is_numeric($search)) { $search_columns = array('user_login', 'ID'); } elseif (preg_match('|^https?://|', $search)) { $search_columns = array('user_url'); } else { $search_columns = array('user_login', 'user_nicename'); } $this->query_where .= $this->get_search_sql($search, $search_columns, $wild); } $blog_id = absint($qv['blog_id']); if ('authors' == $qv['who'] && $blog_id) { $qv['meta_key'] = $wpdb->get_blog_prefix($blog_id) . 'user_level'; $qv['meta_value'] = 0; $qv['meta_compare'] = '!='; $qv['blog_id'] = $blog_id = 0; // Prevent extra meta query } $role = trim($qv['role']); if ($blog_id && ($role || is_multisite())) { $cap_meta_query = array(); $cap_meta_query['key'] = $wpdb->get_blog_prefix($blog_id) . 'capabilities'; if ($role) { $cap_meta_query['value'] = '"' . $role . '"'; $cap_meta_query['compare'] = 'like'; } $qv['meta_query'][] = $cap_meta_query; } $meta_query = new WP_Meta_Query(); $meta_query->parse_query_vars($qv); if (!empty($meta_query->queries)) { $clauses = $meta_query->get_sql('user', $wpdb->users, 'ID', $this); $this->query_from .= $clauses['join']; $this->query_where .= $clauses['where']; if ('OR' == $meta_query->relation) { $this->query_fields = 'DISTINCT ' . $this->query_fields; } } if (!empty($qv['include'])) { $ids = implode(',', wp_parse_id_list($qv['include'])); $this->query_where .= " AND {$wpdb->users}.ID IN ({$ids})"; } elseif (!empty($qv['exclude'])) { $ids = implode(',', wp_parse_id_list($qv['exclude'])); $this->query_where .= " AND {$wpdb->users}.ID NOT IN ({$ids})"; } do_action_ref_array('pre_user_query', array(&$this)); }
/** * 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); }
/** * Retrieves the terms associated with the given object(s), in the supplied taxonomies. * * @since 2.3.0 * @since 4.2.0 Added support for 'taxonomy', 'parent', and 'term_taxonomy_id' values of `$orderby`. * Introduced `$parent` argument. * @since 4.4.0 Introduced `$meta_query` and `$update_term_meta_cache` arguments. When `$fields` is 'all' or * 'all_with_object_id', an array of `WP_Term` objects will be returned. * * @global wpdb $wpdb WordPress database abstraction object. * * @param int|array $object_ids The ID(s) of the object(s) to retrieve. * @param string|array $taxonomies The taxonomies to retrieve terms from. * @param array|string $args { * Array of arguments. * @type string $orderby Field by which results should be sorted. Accepts 'name', 'count', 'slug', * 'term_group', 'term_order', 'taxonomy', 'parent', or 'term_taxonomy_id'. * Default 'name'. * @type string $order Sort order. Accepts 'ASC' or 'DESC'. Default 'ASC'. * @type string $fields Fields to return for matched terms. Accepts 'all', 'ids', 'names', and * 'all_with_object_id'. Note that 'all' or 'all_with_object_id' will result * in an array of term objects being returned, 'ids' will return an array of * integers, and 'names' an array of strings. * @type int $parent Optional. Limit results to the direct children of a given term ID. * @type bool $update_term_meta_cache Whether to prime termmeta cache for matched terms. Only applies when * `$fields` is 'all', 'all_with_object_id', or 'term_id'. Default true. * @type array $meta_query Meta query clauses to limit retrieved terms by. See `WP_Meta_Query`. * Default empty. * } * @return array|WP_Error The requested term data or empty array if no terms found. * WP_Error if any of the $taxonomies don't exist. */ function wp_get_object_terms($object_ids, $taxonomies, $args = array()) { global $wpdb; if (empty($object_ids) || empty($taxonomies)) { return array(); } if (!is_array($taxonomies)) { $taxonomies = array($taxonomies); } foreach ($taxonomies as $taxonomy) { if (!taxonomy_exists($taxonomy)) { return new WP_Error('invalid_taxonomy', __('Invalid taxonomy')); } } if (!is_array($object_ids)) { $object_ids = array($object_ids); } $object_ids = array_map('intval', $object_ids); $defaults = array('orderby' => 'name', 'order' => 'ASC', 'fields' => 'all', 'parent' => '', 'update_term_meta_cache' => true, 'meta_query' => ''); $args = wp_parse_args($args, $defaults); $terms = array(); if (count($taxonomies) > 1) { foreach ($taxonomies as $index => $taxonomy) { $t = get_taxonomy($taxonomy); if (isset($t->args) && is_array($t->args) && $args != array_merge($args, $t->args)) { unset($taxonomies[$index]); $terms = array_merge($terms, wp_get_object_terms($object_ids, $taxonomy, array_merge($args, $t->args))); } } } else { $t = get_taxonomy($taxonomies[0]); if (isset($t->args) && is_array($t->args)) { $args = array_merge($args, $t->args); } } $orderby = $args['orderby']; $order = $args['order']; $fields = $args['fields']; if (in_array($orderby, array('term_id', 'name', 'slug', 'term_group'))) { $orderby = "t.{$orderby}"; } elseif (in_array($orderby, array('count', 'parent', 'taxonomy', 'term_taxonomy_id'))) { $orderby = "tt.{$orderby}"; } elseif ('term_order' === $orderby) { $orderby = 'tr.term_order'; } elseif ('none' === $orderby) { $orderby = ''; $order = ''; } else { $orderby = 't.term_id'; } // tt_ids queries can only be none or tr.term_taxonomy_id if ('tt_ids' == $fields && !empty($orderby)) { $orderby = 'tr.term_taxonomy_id'; } if (!empty($orderby)) { $orderby = "ORDER BY {$orderby}"; } $order = strtoupper($order); if ('' !== $order && !in_array($order, array('ASC', 'DESC'))) { $order = 'ASC'; } $taxonomy_array = $taxonomies; $object_id_array = $object_ids; $taxonomies = "'" . implode("', '", $taxonomies) . "'"; $object_ids = implode(', ', $object_ids); $select_this = ''; if ('all' == $fields) { $select_this = 't.*, tt.*'; } elseif ('ids' == $fields) { $select_this = 't.term_id'; } elseif ('names' == $fields) { $select_this = 't.name'; } elseif ('slugs' == $fields) { $select_this = 't.slug'; } elseif ('all_with_object_id' == $fields) { $select_this = 't.*, tt.*, tr.object_id'; } $where = array("tt.taxonomy IN ({$taxonomies})", "tr.object_id IN ({$object_ids})"); if ('' !== $args['parent']) { $where[] = $wpdb->prepare('tt.parent = %d', $args['parent']); } // Meta query support. $meta_query_join = ''; if (!empty($args['meta_query'])) { $mquery = new WP_Meta_Query($args['meta_query']); $mq_sql = $mquery->get_sql('term', 't', 'term_id'); $meta_query_join .= $mq_sql['join']; // Strip leading AND. $where[] = preg_replace('/^\\s*AND/', '', $mq_sql['where']); } $where = implode(' AND ', $where); $query = "SELECT {$select_this} FROM {$wpdb->terms} AS t INNER JOIN {$wpdb->term_taxonomy} AS tt ON tt.term_id = t.term_id INNER JOIN {$wpdb->term_relationships} AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id {$meta_query_join} WHERE {$where} {$orderby} {$order}"; $objects = false; if ('all' == $fields || 'all_with_object_id' == $fields) { $_terms = $wpdb->get_results($query); $object_id_index = array(); foreach ($_terms as $key => $term) { $term = sanitize_term($term, $taxonomy, 'raw'); $_terms[$key] = $term; if (isset($term->object_id)) { $object_id_index[$key] = $term->object_id; } } update_term_cache($_terms); $_terms = array_map('get_term', $_terms); // Re-add the object_id data, which is lost when fetching terms from cache. if ('all_with_object_id' === $fields) { foreach ($_terms as $key => $_term) { if (isset($object_id_index[$key])) { $_term->object_id = $object_id_index[$key]; } } } $terms = array_merge($terms, $_terms); $objects = true; } elseif ('ids' == $fields || 'names' == $fields || 'slugs' == $fields) { $_terms = $wpdb->get_col($query); $_field = 'ids' == $fields ? 'term_id' : 'name'; foreach ($_terms as $key => $term) { $_terms[$key] = sanitize_term_field($_field, $term, $term, $taxonomy, 'raw'); } $terms = array_merge($terms, $_terms); } elseif ('tt_ids' == $fields) { $terms = $wpdb->get_col("SELECT tr.term_taxonomy_id FROM {$wpdb->term_relationships} AS tr INNER JOIN {$wpdb->term_taxonomy} AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tr.object_id IN ({$object_ids}) AND tt.taxonomy IN ({$taxonomies}) {$orderby} {$order}"); foreach ($terms as $key => $tt_id) { $terms[$key] = sanitize_term_field('term_taxonomy_id', $tt_id, 0, $taxonomy, 'raw'); // 0 should be the term id, however is not needed when using raw context. } } // Update termmeta cache, if necessary. if ($args['update_term_meta_cache'] && ('all' === $fields || 'all_with_object_ids' === $fields || 'term_id' === $fields)) { if ('term_id' === $fields) { $term_ids = $fields; } else { $term_ids = wp_list_pluck($terms, 'term_id'); } update_termmeta_cache($term_ids); } if (!$terms) { $terms = array(); } elseif ($objects && 'all_with_object_id' !== $fields) { $_tt_ids = array(); $_terms = array(); foreach ($terms as $term) { if (in_array($term->term_taxonomy_id, $_tt_ids)) { continue; } $_tt_ids[] = $term->term_taxonomy_id; $_terms[] = $term; } $terms = $_terms; } elseif (!$objects) { $terms = array_values(array_unique($terms)); } /** * Filter the terms for a given object or objects. * * @since 4.2.0 * * @param array $terms An array of terms for the given object or objects. * @param array $object_id_array Array of object IDs for which `$terms` were retrieved. * @param array $taxonomy_array Array of taxonomies from which `$terms` were retrieved. * @param array $args An array of arguments for retrieving terms for the given * object(s). See wp_get_object_terms() for details. */ $terms = apply_filters('get_object_terms', $terms, $object_id_array, $taxonomy_array, $args); /** * Filter the terms for a given object or objects. * * The `$taxonomies` parameter passed to this filter is formatted as a SQL fragment. The * {@see 'get_object_terms'} filter is recommended as an alternative. * * @since 2.8.0 * * @param array $terms An array of terms for the given object or objects. * @param int|array $object_ids Object ID or array of IDs. * @param string $taxonomies SQL-formatted (comma-separated and quoted) list of taxonomy names. * @param array $args An array of arguments for retrieving terms for the given object(s). * See {@see wp_get_object_terms()} for details. */ return apply_filters('wp_get_object_terms', $terms, $object_ids, $taxonomies, $args); }
public function test_empty_compare() { global $wpdb; $query = new WP_Meta_Query( array( 'relation' => 'OR', array( 'key' => 'exclude', 'compare' => '' ), array( 'key' => 'exclude', 'compare' => '!=', 'value' => '1' ), ) ); $sql = $query->get_sql( 'post', $wpdb->posts, 'ID', $this ); // Use regex because we don't care about the whitespace before OR. $this->assertRegExp( "/{$wpdb->postmeta}\.meta_key = \'exclude\'\s+OR/", $sql['where'] ); $this->assertNotContains( "{$wpdb->postmeta}.post_id IS NULL", $sql['where'] ); }
/** * Given a meta query, generates SQL clauses to be appended to a main query. * * @since 3.2.0 * * @see WP_Meta_Query * * @param array $meta_query A meta query. * @param string $type Type of meta. * @param string $primary_table Primary database table name. * @param string $primary_id_column Primary ID column name. * @param object $context Optional. The main query object * @return array Associative array of `JOIN` and `WHERE` SQL. */ function get_meta_sql($meta_query, $type, $primary_table, $primary_id_column, $context = null) { $meta_query_obj = new WP_Meta_Query($meta_query); return $meta_query_obj->get_sql($type, $primary_table, $primary_id_column, $context); }
/** * Get the SQL for the 'meta_query' param in BP_Activity_Activity::get() * * We use WP_Meta_Query to do the heavy lifting of parsing the * meta_query array and creating the necessary SQL clauses. * * @since 1.8.0 * * @param array $meta_query An array of meta_query filters. See the * documentation for {@link WP_Meta_Query} for details. * @return array $sql_array 'join' and 'where' clauses. */ protected static function get_meta_query_sql($meta_query = array()) { global $wpdb; $sql_array = array('join' => '', 'where' => ''); if (!empty($meta_query)) { $groups_meta_query = new WP_Meta_Query($meta_query); // WP_Meta_Query expects the table name at // $wpdb->group. $wpdb->groupmeta = buddypress()->groups->table_name_groupmeta; $meta_sql = $groups_meta_query->get_sql('group', 'g', 'id'); $sql_array['join'] = $meta_sql['join']; $sql_array['where'] = self::strip_leading_and($meta_sql['where']); } return $sql_array; }