get_posts_by_author_sql()WP 3.0.0

Retrieves WHERE part of the SQL query where posts with 'private' post status for authenticated users will be taken into account.

Hooks from the function

Return

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

Usage

get_posts_by_author_sql( $post_type, $full, $post_author, $public_only );
$post_type(array/string) (required)
Single post type or an array of post types.
$full(true/false)

Returns a full WHERE statement instead of just an 'andalso' term.

  • true
    WHERE ( ( post_type = 'post' AND ( post_status = 'publish' OR post_status = 'private' ) ) )
  • false
    ( ( post_type = 'post' AND ( post_status = 'publish' OR post_status = 'private' ) ) )

    Default: true

$post_author(int)
Add author ID into the query: post_author = $post_author instead the ID of the current user.
Default: null
$public_only(true/false)
Return only public posts. Skips cap checks for $current_user (check for private status) — all private posts will be skipped for the current user.
Default: false

Examples

0

#1 Usage demonstration

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

// Authorized: WHERE ( ( post_type = 'post' AND ( post_status = 'publish' OR post_status = 'private' ) ) )
// Not Authorized: WHERE ( post_type = 'post' AND ( post_status = 'publish' ) )

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

Notes

Changelog

Since 3.0.0 Introduced.
Since 4.3.0 Introduced the ability to pass an array of post types to $post_type.

get_posts_by_author_sql() code WP 6.4.3

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