Example #1
0
/**
 * Given a meta query, generates SQL clauses to be appended to a main query
 *
 * @since 3.2.0
 *
 * @see nxt_Meta_Query
 *
 * @param array $meta_query A meta query
 * @param string $type Type of meta
 * @param string $primary_table
 * @param string $primary_id_column
 * @param object $context (optional) The main query object
 * @return array( 'join' => $join_sql, 'where' => $where_sql )
 */
function get_meta_sql($meta_query, $type, $primary_table, $primary_id_column, $context = null)
{
    $meta_query_obj = new nxt_Meta_Query($meta_query);
    return $meta_query_obj->get_sql($type, $primary_table, $primary_id_column, $context);
}
Example #2
0
 /**
  * Prepare the query variables
  *
  * @since 3.1.0
  * @access private
  */
 function prepare_query()
 {
     global $nxtdb;
     $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[] = $nxtdb->users . '.' . esc_sql($field);
         }
         $this->query_fields = implode(',', $this->query_fields);
     } elseif ('all' == $qv['fields']) {
         $this->query_fields = "{$nxtdb->users}.*";
     } else {
         $this->query_fields = "{$nxtdb->users}.ID";
     }
     if ($this->query_vars['count_total']) {
         $this->query_fields = 'SQL_CALC_FOUND_ROWS ' . $this->query_fields;
     }
     $this->query_from = "FROM {$nxtdb->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 {$nxtdb->posts}\n\t\t\t\t{$where}\n\t\t\t\tGROUP BY post_author\n\t\t\t) p ON ({$nxtdb->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 = $nxtdb->prepare("LIMIT %d, %d", $qv['offset'], $qv['number']);
         } else {
             $this->query_limit = $nxtdb->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'] = $nxtdb->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'] = $nxtdb->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 nxt_Meta_Query();
     $meta_query->parse_query_vars($qv);
     if (!empty($meta_query->queries)) {
         $clauses = $meta_query->get_sql('user', $nxtdb->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(',', nxt_parse_id_list($qv['include']));
         $this->query_where .= " AND {$nxtdb->users}.ID IN ({$ids})";
     } elseif (!empty($qv['exclude'])) {
         $ids = implode(',', nxt_parse_id_list($qv['exclude']));
         $this->query_where .= " AND {$nxtdb->users}.ID NOT IN ({$ids})";
     }
     do_action_ref_array('pre_user_query', array(&$this));
 }