PHP WP_Meta_Query Code Examples

PHP WP_Meta_Query - 30 examples found. These are the top rated real world PHP examples of WP_Meta_Query extracted from open source projects. You can rate examples to help us improve the quality of examples
Frequently Used Methods
 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;
 }
Example #3
1
 /**
  * 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;
 }
 /**
  * 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;
 }
Example #5
1
 /**
  * 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));
 }
 /**
  * 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));
 }
Example #7
0
 /**
  * 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));
 }
 /**
  * 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;
 }
Example #9
0
 /**
  * @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());
 }
Example #10
0
 /**
  * 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;
 }
Example #11
0
 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');
 }
 /**
  * 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'];
 }
 /**
  * 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));
 }
	/**
	 * @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' ) );
	}
Example #16
0
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;
}
 /**
  * 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;
 }
 /**
  * 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 #19
0
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;
}
 /**
  * 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;
 }
	/**
	 * @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'" ) );

	}
Example #22
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;
     }
 }
 /**
  * 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;
 }
Example #24
0
 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;
 }
Example #25
0
 /**
  * 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));
 }
Example #26
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);
 }
/**
 * 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'] );
	}
Example #29
0
/**
 * 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;
 }