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

by

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.

2 Answers
2

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

Leave a Comment