PHP get_posts_by_author_sql函数代码示例

 /**
  * 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;
 }

/**
 * count user post by post type
 * @param (integer) $userID (required) The ID of the user to count posts for.
 * @param (string) $post_type The post type you want to count, Default is post.
 * @version 1.0
 * @author dakachi
 * @package AE
 * http://codex.wordpress.org/Function_Reference/count_user_posts
*/
function ae_count_user_posts_by_type($userid, $post_type = 'post')
{
    global $wpdb;
    $where = get_posts_by_author_sql($post_type, true, $userid);
    $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->posts} {$where}");
    return apply_filters('get_usernumposts', $count, $userid);
}

function amr_count_user_posts($userid, $post_type)
{
    // wordpress function does not allow for custom post types
    global $wpdb;
    if (!post_type_exists($post_type)) {
        return false;
    }
    $where = get_posts_by_author_sql($post_type, true, $userid);
    $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->posts} {$where}");
    return apply_filters('get_usernumposts', $count, $userid);
}

 function count_user_posts_by_type($user_id = '', $post_type = 'post')
 {
     global $wpdb;
     if (!$user_id) {
         $user_id = um_user('ID');
     }
     if (!$user_id) {
         return 0;
     }
     $where = get_posts_by_author_sql($post_type, true, $user_id);
     $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->posts} {$where}");
     return apply_filters('um_pretty_number_formatting', $count);
 }

function ssmf_count_user_posts_by_type($userid, $post_type = 'subscribe_me_forms')
{
    global $wpdb;
    $userid = get_current_user_id();
    $where = get_posts_by_author_sql($post_type, true, $userid);
    $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->posts} {$where}");
    $screen = get_current_screen();
    if (current_user_can('edit_posts') and $screen->post_type === 'subscribe_me_forms') {
        //Is  admin and all users - so impose the limit
        if ($count >= 2) {
            header("Location: /wp-content/plugins/mailchimp-subscribe-sm/phuf.php");
        }
    }
}

function mpsp_count_user_posts_by_type($userid, $post_type = 'mpsp_slider')
{
    global $wpdb;
    $userid = get_current_user_id();
    $where = get_posts_by_author_sql($post_type, true, $userid);
    $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->posts} {$where}");
    $screen = get_current_screen();
    if (current_user_can('edit_posts') and $screen->post_type === 'mpsp_slider') {
        //Is  admin and all users - so impose the limit
        if ($count >= 300) {
            header("Location: /wp-content/plugins/Posts-Slider-NEW/phuf.php");
        }
    }
}

/**
 * Count user posts by post type
 * @param  int $userid
 * @param  string $post_type
 * @return int
 * @since unknown
 */
function ap_count_user_posts_by_type($userid, $post_type = 'question')
{
    global $wpdb;
    $where = get_posts_by_author_sql($post_type, true, $userid);
    $query = "SELECT COUNT(*) FROM {$wpdb->posts} {$where}";
    $key = md5($query);
    $cache = wp_cache_get($key, 'count');
    if ($cache === false) {
        $count = $wpdb->get_var($query);
        wp_cache_set($key, $count, 'count');
    } else {
        $count = $cache;
    }
    return apply_filters('ap_count_user_posts_by_type', $count, $userid);
}

/**
 * Gets related posts by taxonomy.
 *
 * @since 0.1
 *
 * @global object       $wpdb
 *
 * @param int     $post_id    The post id to get related posts for.
 * @param array|string $taxonomies The taxonomies to retrieve related posts from
 * @param array|string $args       Optional. Change what is returned
 * @return array                    Empty array if no related posts found. Array with post objects.
 */
function km_rpbt_related_posts_by_taxonomy($post_id = 0, $taxonomies = 'category', $args = '')
{
    global $wpdb;
    if (!absint($post_id)) {
        return array();
    }
    $defaults = array('post_types' => 'post', 'posts_per_page' => 5, 'order' => 'DESC', 'fields' => '', 'limit_posts' => -1, 'limit_year' => '', 'limit_month' => '', 'orderby' => 'post_date', 'exclude_terms' => '', 'include_terms' => '', 'exclude_posts' => '', 'post_thumbnail' => '', 'related' => true);
    $args = wp_parse_args($args, $defaults);
    $taxonomies = !empty($taxonomies) ? $taxonomies : array('category');
    if (!is_array($taxonomies)) {
        $taxonomies = array_unique(explode(',', (string) $taxonomies));
    }
    $terms = array();
    // validates ids and returns an array
    $included = km_rpbt_related_posts_by_taxonomy_validate_ids($args['include_terms']);
    if (!$args['related'] && !empty($included)) {
        // related, use included term ids
        $terms = $included;
    } else {
        // related and not related terms
        $terms = wp_get_object_terms($post_id, array_map('trim', (array) $taxonomies), array('fields' => 'ids'));
        if (is_wp_error($terms) || empty($terms)) {
            return array();
        }
        // only use included terms from the post terms
        if ($args['related'] && !empty($included)) {
            $terms = array_values(array_intersect($included, $terms));
        }
    }
    // exclude terms
    if (empty($included)) {
        // validates ids and returns an array
        $excluded = km_rpbt_related_posts_by_taxonomy_validate_ids($args['exclude_terms']);
        $terms = array_values(array_diff($terms, $excluded));
    }
    if (empty($terms)) {
        return array();
    }
    $args['related_terms'] = $terms;
    // term ids sql
    if (count($terms) > 1) {
        $term_ids_sql = "tt.term_id IN (" . implode(', ', $terms) . ")";
    } else {
        $term_ids_sql = isset($terms[0]) ? "tt.term_id = " . $terms[0] : "tt.term_id = 0";
    }
    // validates ids and returns an array
    $exclude_posts = km_rpbt_related_posts_by_taxonomy_validate_ids($args['exclude_posts']);
    // add current post ID to exclude
    $exclude_posts[] = $post_id;
    $exclude_posts = array_unique($exclude_posts);
    // post ids sql
    $post_ids_sql = "AND {$wpdb->posts}.ID";
    if (count($exclude_posts) > 1) {
        $post_ids_sql .= " NOT IN (" . implode(', ', $exclude_posts) . ")";
    } else {
        $post_ids_sql .= " != {$post_id}";
    }
    // post types
    if (!is_array($args['post_types'])) {
        $args['post_types'] = explode(',', (string) $args['post_types']);
    }
    // sanitize post type names and remove duplicates
    $post_types = array_unique(array_map('sanitize_key', (array) $args['post_types']));
    $post_types = array_filter($post_types, 'post_type_exists');
    // default to post type post if no post types are found
    $post_types = !empty($post_types) ? $post_types : array('post');
    // where sql (post types and post status)
    if (count($post_types) > 1) {
        $where = get_posts_by_author_sql('post');
        $post_type_sql = "'" . implode("', '", $post_types) . "'";
        $where_sql = preg_replace("/post_type = 'post'/", "post_type IN ({$post_type_sql})", $where);
    } else {
        $where_sql = get_posts_by_author_sql($post_types[0]);
    }
    $order_by_rand = false;
    // order sql
    switch (strtoupper((string) $args['order'])) {
        case 'ASC':
            $order_sql = 'ASC';
            break;
        case 'RAND':
            $order_sql = 'RAND()';
            $order_by_rand = true;
            break;
        default:
            $order_sql = 'DESC';
            break;
    }
//.........这里部分代码省略.........

 public function test_user_has_access_only_to_private_posts_for_certain_post_types()
 {
     register_post_type('foo', array('capabilities' => array('read_private_posts' => 'read_private_foo')));
     register_post_type('bar', array('capabilities' => array('read_private_posts' => 'read_private_bar')));
     register_post_type('baz', array('capabilities' => array('read_private_posts' => 'read_private_baz')));
     $current_user = get_current_user_id();
     $u = self::factory()->user->create(array('role' => 'editor'));
     $editor_role = get_role('editor');
     $editor_role->add_cap('read_private_baz');
     wp_set_current_user($u);
     $maybe_string = get_posts_by_author_sql(array('foo', 'bar', 'baz'));
     $editor_role->remove_cap('read_private_baz');
     $this->assertNotContains("post_type = 'foo' AND ( post_status = 'publish' OR post_status = 'private' )", $maybe_string);
     $this->assertNotContains("post_type = 'bar' AND ( post_status = 'publish' OR post_status = 'private' )", $maybe_string);
     $this->assertContains("post_type = 'baz' AND ( post_status = 'publish' OR post_status = 'private' )", $maybe_string);
     _unregister_post_type('foo');
     _unregister_post_type('bar');
     _unregister_post_type('baz');
     wp_set_current_user($current_user);
 }

 /**
  * Get the users based on sorting criteria and order
  * 
  * Function identificator: fn0
  *
  */
 function select_sorting_order_criteria()
 {
     global $wpdb;
     $qv =& $this->query_vars;
     $this->fn0_query_results_array = array();
     $this->fn0_query_fields = "wppb_t1.ID";
     $this->fn0_query_from = "FROM {$wpdb->users} AS wppb_t1";
     if ($qv['sorting_criteria'] == 'ID') {
         $criteria = 'wppb_t1.ID';
     } elseif ($qv['sorting_criteria'] == 'login') {
         $criteria = 'wppb_t1.user_login';
     } elseif ($qv['sorting_criteria'] == 'email') {
         $criteria = 'wppb_t1.user_email';
     } elseif ($qv['sorting_criteria'] == 'url') {
         $criteria = 'wppb_t1.user_url';
     } elseif ($qv['sorting_criteria'] == 'registered') {
         $criteria = 'wppb_t1.user_registered';
     } elseif ($qv['sorting_criteria'] == 'nicename') {
         $criteria = 'wppb_t1.display_name';
     } elseif ($qv['sorting_criteria'] == 'post_count') {
         $where = get_posts_by_author_sql('post');
         $this->fn0_query_from .= " LEFT OUTER JOIN (SELECT post_author, COUNT(*) AS post_count FROM {$wpdb->posts} {$where} GROUP BY post_author) p ON wppb_t1.ID = p.post_author";
         $criteria = 'wppb_t1.ID';
     } elseif ($qv['sorting_criteria'] == 'bio') {
         $this->fn0_query_from .= " LEFT OUTER JOIN {$wpdb->usermeta} AS wppb_t2 ON wppb_t1.ID = wppb_t2.user_id AND wppb_t2.meta_key = 'description'";
         $criteria = 'wppb_t2.meta_value';
     } elseif ($qv['sorting_criteria'] == 'aim') {
         $this->fn0_query_from .= " LEFT OUTER JOIN {$wpdb->usermeta} AS wppb_t2 ON wppb_t1.ID = wppb_t2.user_id AND wppb_t2.meta_key = 'aim'";
         $criteria = 'wppb_t2.meta_value';
     } elseif ($qv['sorting_criteria'] == 'yim') {
         $this->fn0_query_from .= " LEFT OUTER JOIN {$wpdb->usermeta} AS wppb_t2 ON wppb_t1.ID = wppb_t2.user_id AND wppb_t2.meta_key = 'yim'";
         $criteria = 'wppb_t2.meta_value';
     } elseif ($qv['sorting_criteria'] == 'jabber') {
         $this->fn0_query_from .= " LEFT OUTER JOIN {$wpdb->usermeta} AS wppb_t2 ON wppb_t1.ID = wppb_t2.user_id AND wppb_t2.meta_key = 'jabber'";
         $criteria = 'wppb_t2.meta_value';
     } elseif ($qv['sorting_criteria'] == 'firstname') {
         $this->fn0_query_from .= " LEFT OUTER JOIN {$wpdb->usermeta} AS wppb_t2 ON wppb_t1.ID = wppb_t2.user_id AND wppb_t2.meta_key = 'first_name'";
         $criteria = 'wppb_t2.meta_value';
     } elseif ($qv['sorting_criteria'] == 'lastname') {
         $this->fn0_query_from .= " LEFT OUTER JOIN {$wpdb->usermeta} AS wppb_t2 ON wppb_t1.ID = wppb_t2.user_id AND wppb_t2.meta_key = 'last_name'";
         $criteria = 'wppb_t2.meta_value';
     } else {
         $wppbFetchArray = get_option('wppb_custom_fields', 'not_found');
         if ($wppbFetchArray != 'not_found') {
             foreach ($wppbFetchArray as $thisKey => $thisValue) {
                 if ($thisValue['item_type'] != 'heading') {
                     if ($qv['sorting_criteria'] == $thisValue['item_metaName']) {
                         $this->fn0_query_from .= " LEFT OUTER JOIN {$wpdb->usermeta} AS wppb_t2 ON wppb_t1.ID = wppb_t2.user_id AND wppb_t2.meta_key = '" . $thisValue['item_metaName'] . "'";
                         $criteria = 'wppb_t2.meta_value';
                     }
                 }
             }
         }
     }
     $this->fn0_query_where = "WHERE 1";
     $this->fn0_query_orderby = "ORDER BY {$criteria} " . strtoupper(trim($qv['sorting_order']));
     $this->fn0_query_limit = "";
     do_action_ref_array('wppb_pre_select_by_sorting_order_and_criteria', array(&$this));
     $this->fn0_query_results = apply_filters('wppb_select_by_sorting_order_and_criteria_result', $wpdb->get_results(trim("SELECT {$this->fn0_query_fields} {$this->fn0_query_from} {$this->fn0_query_where} {$this->fn0_query_orderby} {$this->fn0_query_limit}")));
     //create an array with IDs from result
     foreach ($this->fn0_query_results as $qr_key => $qr_value) {
         array_push($this->fn0_query_results_array, $qr_value->ID);
     }
     $this->fn0_query_results_array = apply_filters('wppb_select_by_sorting_order_and_criteria_array', $this->fn0_query_results_array);
     do_action_ref_array('wppb_post_select_by_sorting_order_and_criteria', array(&$this));
 }

/**
 * Number of posts written by a list of users.
 *
 * @since 3.0.0
 * @param array $userid User ID number list.
 * @return array Amount of posts each user has written.
 */
function count_many_users_posts($users)
{
    global $wpdb;
    $count = array();
    if (!is_array($users) || empty($users)) {
        return $count;
    }
    $userlist = implode(',', $users);
    $where = get_posts_by_author_sql('post');
    $result = $wpdb->get_results("SELECT post_author, COUNT(*) FROM {$wpdb->posts} {$where} AND post_author IN ({$userlist}) GROUP BY post_author", ARRAY_N);
    foreach ($result as $row) {
        $count[$row[0]] = $row[1];
    }
    foreach ($users as $id) {
        if (!isset($count[$id])) {
            $count[$id] = 0;
        }
    }
    return $count;
}

 function count_user_posts_by_type($userid, $post_type = 'post')
 {
     global $wpdb;
     $where = get_posts_by_author_sql($post_type, true, $userid);
     $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->posts} {$where}");
     return $count;
 }

function count_user_posts_by_type($userid, $post_type = 'post', $public_only = false)
{
    global $wpdb;
    $where = get_posts_by_author_sql($post_type, true, $userid, $public_only);
    $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->posts} WHERE ( ( post_type = 'quote' AND ( post_status = 'publish' ) ) ) AND post_author = 684");
    return $where;
    //apply_filters( 'get_usernumposts', $count, $userid );
}

function marketify_count_user_downloads($userid, $post_type = 'download')
{
    if (false === ($count = get_transient($userid . $post_type))) {
        global $wpdb;
        $where = get_posts_by_author_sql($post_type, true, $userid);
        $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->posts} {$where}");
        set_transient($userid . $post_type, $count, 12 * HOUR_IN_SECONDS);
    }
    return apply_filters('get_usernumposts', $count, $userid);
}

/**
 * Return the raw database count of replies by a user
 *
 * @since bbPress (r3633)
 * @global WPDB $wpdb
 * @uses bbp_get_user_id()
 * @uses get_posts_by_author_sql()
 * @uses bbp_get_reply_post_type()
 * @uses apply_filters()
 * @return int Raw DB count of replies
 */
function bbp_get_user_reply_count_raw($user_id = 0)
{
    $user_id = bbp_get_user_id($user_id);
    if (empty($user_id)) {
        return false;
    }
    global $wpdb;
    $where = get_posts_by_author_sql(bbp_get_reply_post_type(), true, $user_id);
    $count = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->posts} {$where}");
    return (int) apply_filters('bbp_get_user_reply_count_raw', $count, $user_id);
}