Get all posts (of any post type) an attachment is used in

It’s not easy to count the amount of posts an image is attached to – WordPress simply doesn’t keep track of that. It just keeps track of the post an attachment was originally uploaded to (not necessarily even using it there).

enter image description here

Plugin

To get you started as quick as possible, here’s the plugin code:

<?php
/**
 * Plugin Name: Media Count
 * Description: Adds a column to the media admin list table to show the count of posts
 */

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' :
            $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
            $file  = pathinfo( $file, PATHINFO_FILENAME );
            // @TODO Fill in the blanks
            break;
    }
}, 10, 2 );

Question:

How to count the amount of posts an attachment is used in – the most efficient way.

Final Plugin

The full plugin can be downloaded as Gist here.

Solutions Collecting From Web of "Get all posts (of any post type) an attachment is used in"

Second pass. Known issues:

  • Needs caching (and the cache needs to be tidied up where possible)
  • Post types are hard-coded
  • Which post statuses are we interested in?

Here’s the function:

/**
 * Given an attachment ID, searches for any post with that attachment used
 * as a featured image, or if it is present in the content of the post.
 * (Note above known issues).
*/
function get_image_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
    $file = sprintf( "%s.%s",
        pathinfo( $file, PATHINFO_FILENAME ),
        pathinfo( $file, PATHINFO_EXTENSION )
    );

    $sql = "SELECT {$wpdb->posts}.ID 
        FROM {$wpdb->posts} 
        INNER JOIN {$wpdb->postmeta} 
        ON ({$wpdb->posts}.ID = {$wpdb->postmeta}.post_id) 
        WHERE {$wpdb->posts}.post_type IN ('post', 'page', 'event') 
        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";

    $prepared_sql = $wpdb->prepare( $sql, $id, "%src=\"%".$wpdb->esc_like( $file )."\"%" );

    $post_ids  = $wpdb->get_results( $prepared_sql );

    $count = count( $post_ids );

    return $count;
}

Just as an addition, as an improvement to account for different image sizes, I would do:

$file_name      = pathinfo( $file, PATHINFO_FILENAME );
$file_extension = '.' . pathinfo( $file, PATHINFO_EXTENSION );

Instead having a combined value for $file.

And change the SQL preparation to:

$prepared_sql =
    $wpdb->prepare(
        $sql,
        $id,
        "%src=\"%"
            . like_escape( $file_name )
            . "%"
            . like_escape( $file_extension )
            . "\"%"
    );


As second addition an example that makes use of MySQL’s REGEXP/RLIKE functionality, it additionally does get images just linked in an a tag, besides that, this is capable – contrary to the first addition, which only gets the full, uploaded size – of getting the image independent from the image size – where, for example, »the-image.jpg« would be full size and »the-image-150×150.jpg« a generated size – used:

$file_name      = pathinfo( $file, PATHINFO_FILENAME );
// beware different syntax
$file_extension = '[[...]]'.pathinfo( $file, PATHINFO_EXTENSION );

$sql = "SELECT {$wpdb->posts}.ID 
    FROM {$wpdb->posts} 
    INNER JOIN {$wpdb->postmeta} 
    ON ({$wpdb->posts}.ID = {$wpdb->postmeta}.post_id) 
    WHERE {$wpdb->posts}.post_type IN ('post', 'page', 'event') 
    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 REGEXP %s )
    ) 
    GROUP BY {$wpdb->posts}.ID";

$exp =
    '([[.<.]])' // tag start
        . '(img|a)' // define tag types
        . '.*' // other attributes void
        . '(src|href)' // define anchor(s) attribute
        . '=([[.".]]|[[.\'.]])' // quotes
        . '.*' // path/URL void
        . $file_name
        . '.*' // image size void
        . $file_extension
        . '([[.".]]|[[.\'.]])' // quotes
        . '.*' // other attributes void
        . '([[.>.]])' // tag end
    ;

$prepared_sql =
    $wpdb->prepare(
        $sql,
        $id,
        $exp
    );

$post_ids  = $wpdb->get_results( $prepared_sql );