WordPress plugin: Adds a column to the media admin list table to show the count of posts. Also adds a "size" column which displays the duration of Audio files or sizes of image files.

media_att_count.php

<?php

namespace WCM;

/**
 * Plugin Name: (WCM) Media Count
 * Description: Adds a column to the media admin list table to show the count of posts
 * License:     MIT
 */

add_filter( 'manage_media_columns', function( $cols, $detached )
{
	$cols['count'] = 'Count';
	$cols['size']  = 'Size';
	return $cols;
}, 10, 2 );

add_action( 'manage_media_custom_column', function( $col, $id )
{
	switch ( $col )
	{
		case 'size' :
			$meta = wp_get_attachment_metadata( $id );
			// Image
			isset( $meta['width'] )
				AND print "{$meta['width']} &times; {$meta['height']}";
			// Audio
			isset( $meta['bitrate'] )
				AND print "{$meta['length_formatted']} min";
			break;

		case 'count' :
			$posts = \WCM\wcm_get_att_use_count( $id );

			foreach ( $posts as $post )
				edit_post_link(
					$post->post_title,
					'<strong>',
					'</strong><br>',
					$post->ID
				);
			break;
	}
}, 10, 2 );

/**
 * Retrieve an array of IDs and post titles
 * of posts where the image is in use
 * (feat. image and in the post content)
 * @author SQL Query Stephen Harris
 * @license CC-BY-SA 3.0
 * @param int $id
 * @return array
 */
function wcm_get_att_use_count( $id )
{
	global $wpdb;

	$att  = get_post_custom( $id );
	$file = $att['_wp_attached_file'][0];
	// Do not take full path as different image sizes could
	// have different month, year folders due to theme and image size changes
	$name = pathinfo( $file, PATHINFO_FILENAME );
	$ext  = pathinfo( $file, PATHINFO_EXTENSION );

	$sql = <<<SQL
select distinct {$wpdb->posts}.ID, {$wpdb->posts}.post_title
from {$wpdb->posts}
inner join {$wpdb->postmeta}
on {$wpdb->posts}.ID = {$wpdb->postmeta}.post_id
where
	post_type not in ( 'attachment', 'revision', 'nav_menu_item' )
	and ( {$wpdb->posts}.post_status = 'publish' )
	and (
		(
			{$wpdb->postmeta}.meta_key = '_thumbnail_id'
			and cast({$wpdb->postmeta}.meta_value as char) = '%d'
		)
		or ( {$wpdb->posts}.post_content like %s )
	)
group by {$wpdb->posts}.ID
SQL;

	return $wpdb->get_results( $wpdb->prepare(
		$sql,
		$id,
			"%src=\"%"
			. like_escape( $name )
			. "%"
			. like_escape( $ext )
			. "\"%"
	) );
}

As mentioned in WPSE chat. This is hard to cache. I'm starting it might be better to check a post's content for images and store any found as post meta whenever the post content is updated. Then you can tell which images have bee added/removed and clear the cache accordingly.

There's an improved LIKE statement here http://wordpress.stackexchange.com/questions/154324/get-all-posts-of-any-post-type-an-attachment-is-used-in/154325#154325 - which itself can easily be improved to reduce false positives.

@stephenharris Do you mean the Regex statement by @ialocin ?

About the post meta - yes that might be a good idea. Still keeping the post IDs as post meta of the attachment might be an option as well.

@stephenharris @franz-josef-kaiser I think use a post meta on attachment post is a better approach. Only problem there is that on plugin installation, it should check all already existent posts or count will be not available (or 0) for them. Other issues I see here are:

• probably private posts should be added in the SQL query, because they are published, just password protected
• a filter should be added in the SQL query to allow a better control for custom post types, and/or probably exclude all post types registered as non-public

@franz-josef-kaiser kind of. Except not as part of an SQL statement, but just regex $post->post_content when the content is updated. You could then store any image files (or better still, look-up the image ID and store that). Then you'll have a reference of all images included/associated with the post in post meta and query by that.

It makes caching much easier as you can detect when an image is added/removed and regenerate the cache then.

@Giuseppe-Mazzapica agreed. I got lazy and just hard-coded stuff :)