Deluxe Blog Tips About Projects

WordPress: display correct number of posts in posts list table after filtering

In the WordPress admin, when you go to all posts screen, you’ll see a table of all posts. On that screen, you also see numbers of posts by post status (WordPress calls it table views). And you can also add your own filters for posts. The problem is WordPress doesn’t show correct number of posts after filtering. Instead, it always show the number of posts without filtering.

To fix that, we have to write our custom table views to query the number of posts by status, with the current filters.

Create custom table views

To add custom table views, use this code:

add_filter( "views_edit-{$post_type}", 'dbt_get_views' );
function dbt_get_views( $views ) {
    // Our code here
    return $views;
}

Replace $post_type with your own post type slug.

Get number of posts

To get the current filters, we can either re-make the query that we use to filter posts. And then get all posts, and group them by status. But that creates a very slow query, since we have to query all posts.

A better approach is making our own SQL query with minimum data as possible. Using SQL, we have to get the current filters in SQL. Thanks to the global $wp_query, we can extract the it from the current query as follows:

global $wpdb, $wp_query;

$sql = $wp_query->request;

if ( strpos( $sql, 'FROM' ) !== false ) {
	list ( , $sql ) = explode( 'FROM', $sql );
}
if ( strpos( $sql, 'GROUP' ) !== false ) {
	list ( $sql ) = explode( 'GROUP', $sql );
}
if ( strpos( $sql, 'ORDER' ) !== false ) {
	list ( $sql ) = explode( 'ORDER', $sql );
}

$data = $wpdb->get_results( "SELECT post_status, count(ID) AS count FROM $sql GROUP BY post_status" );

The full SQL has the following format:

SELECT ... FROM ... [INNER JOIN ...] WHERE ... [GROUP BY ...] [ORDER BY ...]

We need to get only part:

FROM ... [INNER JOIN ...] WHERE ... 

That’s why we use some string functions above. And then we select only post_status and number of posts (count(ID)) and group them by post_status.

Build the table views

And now we can build the table views with the following code:

$total = array_sum( wp_list_pluck( $data, 'count' ) );

$views = [
	'all' => sprintf(
		'<a href="%s" class="%s">%s <span class="count">(%d)</span></a>',
		esc_url( admin_url( "edit.php?post_type={$this->post_type}" ) ),
		empty( $_GET['post_status'] ) ? '' : 'current',
		'All',
		$total
	),
];
$labels = [
	'publish' => 'Publish',
	'draft'   => 'Draft',
	'trash'   => 'Trash',
	'custom-status' => 'Custom status',
];
foreach ( $data as $item ) {
	$label = $labels[ $item->post_status ];
	$views[ $item->post_status ] = sprintf(
		'<a href="%s" class="%s">%s <span class="count">(%d)</span></a>',
		esc_url( admin_url( "edit.php?post_type={$post_type}&post_status={$item->post_status}" ) ),
		isset( $_GET['post_status'] ) && $_GET['post_status'] === $item->post_status ? 'current' : '',
		$label,
		$item->count
	);
}

Note that you need to list all the labels for the available statuses in the $labels variable. That might include custom post statuses.

Again, don’t forget to replace $post_type with your post type slug.

Final code

Here is the complete code:

<?php
add_filter( "views_edit-{$post_type}", 'dbt_get_views' );
function dbt_get_views( $views ) {
	global $wpdb, $wp_query;

	$sql = $wp_query->request;

	if ( strpos( $sql, 'FROM' ) !== false ) {
		list ( , $sql ) = explode( 'FROM', $sql );
	}
	if ( strpos( $sql, 'GROUP' ) !== false ) {
		list ( $sql ) = explode( 'GROUP', $sql );
	}
	if ( strpos( $sql, 'ORDER' ) !== false ) {
		list ( $sql ) = explode( 'ORDER', $sql );
	}

	$data = $wpdb->get_results( "SELECT post_status, count(ID) AS count FROM $sql GROUP BY post_status" );

	$total = array_sum( wp_list_pluck( $data, 'count' ) );

	$views = [
		'all' => sprintf(
			'<a href="%s" class="%s">%s <span class="count">(%d)</span></a>',
			esc_url( admin_url( "edit.php?post_type={$this->post_type}" ) ),
			empty( $_GET['post_status'] ) ? '' : 'current',
			'All',
			$total
		),
	];
	$labels = [
		'publish' => 'Publish',
		'draft'   => 'Draft',
		'trash'   => 'Trash',
		'custom-status' => 'Custom status',
	];
	foreach ( $data as $item ) {
		$label = $labels[ $item->post_status ];
		$views[ $item->post_status ] = sprintf(
			'<a href="%s" class="%s">%s <span class="count">(%d)</span></a>',
			esc_url( admin_url( "edit.php?post_type={$post_type}&post_status={$item->post_status}" ) ),
			isset( $_GET['post_status'] ) && $_GET['post_status'] === $item->post_status ? 'current' : '',
			$label,
			$item->count
		);
	}

	return $views;
}

Subscribe to the Newsletter

Subscribe to get my latest content by email. I won't send you spam. Unsubscribe at any time.