get_posts_by_author_sql() – Function | Developer.WordPress.org

Retrieves the post SQL based on capability, author, and type.

Parameters

$post_typestring|string[]required: Single post type or an array of post types.
$fullbooloptional: Returns a full WHERE statement instead of just an 'andalso' term.
Default:true
$post_authorintoptional: Query posts having a single author ID.
Default:null
$public_onlybooloptional: Only return public posts. Skips cap checks for $current_user.
Default:false

Return

string SQL WHERE code that can be added to a query.

This function provides a standardized way to appropriately select on the post_status of a post type. The function will return a piece of SQL code that can be added to a WHERE clause; this SQL is constructed to allow all published posts, and all private posts to which the user has access.

Source

function get_posts_by_author_sql( $post_type, $full = true, $post_author = null, $public_only = false ) {
	global $wpdb;

	if ( is_array( $post_type ) ) {
		$post_types = $post_type;
	} else {
		$post_types = array( $post_type );
	}

	$post_type_clauses = array();
	foreach ( $post_types as $post_type ) {
		$post_type_obj = get_post_type_object( $post_type );

		if ( ! $post_type_obj ) {
			continue;
		}

		/**
		 * Filters the capability to read private posts for a custom post type
		 * when generating SQL for getting posts by author.
		 *
		 * @since 2.2.0
		 * @deprecated 3.2.0 The hook transitioned from "somewhat useless" to "totally useless".
		 *
		 * @param string $cap Capability.
		 */
		$cap = apply_filters_deprecated( 'pub_priv_sql_capability', array( '' ), '3.2.0' );

		if ( ! $cap ) {
			$cap = current_user_can( $post_type_obj->cap->read_private_posts );
		}

		// Only need to check the cap if $public_only is false.
		$post_status_sql = "post_status = 'publish'";

		if ( false === $public_only ) {
			if ( $cap ) {
				// Does the user have the capability to view private posts? Guess so.
				$post_status_sql .= " OR post_status = 'private'";
			} elseif ( is_user_logged_in() ) {
				// Users can view their own private posts.
				$id = get_current_user_id();
				if ( null === $post_author || ! $full ) {
					$post_status_sql .= " OR post_status = 'private' AND post_author = $id";
				} elseif ( $id === (int) $post_author ) {
					$post_status_sql .= " OR post_status = 'private'";
				} // Else none.
			} // Else none.
		}

		$post_type_clauses[] = "( post_type = '" . $post_type . "' AND ( $post_status_sql ) )";
	}

	if ( empty( $post_type_clauses ) ) {
		return $full ? 'WHERE 1 = 0' : '1 = 0';
	}

	$sql = '( ' . implode( ' OR ', $post_type_clauses ) . ' )';

	if ( null !== $post_author ) {
		$sql .= $wpdb->prepare( ' AND post_author = %d', $post_author );
	}

	if ( $full ) {
		$sql = 'WHERE ' . $sql;
	}

	return $sql;
}

View all references View on Trac View on GitHub

Hooks

apply_filters_deprecated( ‘pub_priv_sql_capability’, string $cap ): Filters the capability to read private posts for a custom post type when generating SQL for getting posts by author.

Uses	Description
apply_filters_deprecated()`wp-includes/plugin.php`	Fires functions attached to a deprecated filter hook.
is_user_logged_in()`wp-includes/pluggable.php`	Determines whether the current visitor is a logged in user.
current_user_can()`wp-includes/capabilities.php`	Returns whether the current user has the specified capability.
get_current_user_id()`wp-includes/user.php`	Gets the current user’s ID.
get_post_type_object()`wp-includes/post.php`	Retrieves a post type object by name.
wpdb::prepare()`wp-includes/class-wpdb.php`	Prepares a SQL query for safe execution.

Show 4 more Show less

Used by	Description
WP_User_Query::parse_orderby()`wp-includes/class-wp-user-query.php`	Parses and sanitizes ‘orderby’ keys passed to the user query.
count_user_posts()`wp-includes/user.php`	Gets the number of posts a user has written.
count_many_users_posts()`wp-includes/user.php`	Gets the number of posts written by a list of users.
get_private_posts_cap_sql()`wp-includes/post.php`	Retrieves the private post SQL based on capability.

Changelog

Version	Description
4.3.0	Introduced the ability to pass an array of post types to `$post_type`.
3.0.0	Introduced.

User Contributed Notes

Skip to note 3 content

Paul Bearne 11 years ago

You must log in to vote on the helpfulness of this noteVote results for this note: 0You must log in to vote on the helpfulness of this note

the full option set to false currently doesn’t return the post filter as you would expect

see this patch and test for more info

https://core.trac.wordpress.org/ticket/30354

Log in to add feedback

Codex 10 years ago

Example

$where = get_posts_by_author_sql( 'post' );
echo $where;

// user logged in: WHERE post_type = 'post' AND (post_status = 'publish' OR post_status = 'private')
// user not logged in: WHERE post_type = 'post' AND (post_status = 'publish')

// get post ID with title "Hello world!" query
global $wpdb;
$query = "SELECT ID FROM $wpdb->posts $where AND post_title = %s";
$post_id = $wpdb->get_var( $wpdb->prepare( $query, 'Hello world!' ) );

get_posts_by_author_sql( string|string[] $post_type, bool $full = true, int $post_author = null, bool $public_only = false ): string

In this article

Description

See also