4

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.

4

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;
}
  • 2
    One thing we mentioned in chat was that the LIKE statement can be improved to avoid some silly edge cases. E.g. <img src="not-what-you-want.png"> [anything] what-you-want.png" – Stephen Harris Jul 12 '14 at 0:11
2

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-150x150.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 );
  • Very nice. Thank you! The only thing we still haven't solved is that the anchor src might differ from the actual HTML in the tag. – kaiser Jul 12 '14 at 12:45
  • My pleasure. Not quite sure if I follow what you said, the img tag is a non-closing tag. So, what tag are we talking about? @kaiser – Nicolai Jul 12 '14 at 14:02
  • 1
    Ok, I see, might be doable, but not for me...on the fly at least. Normally I would say regular expression, but MySQL doesn't support - all those fun and handy - capturing groups to do so. On the other hand MySQL REGEXP could be used to get images in other tags, like those only linked in an a tag - added a example to the answer. @kaiser – Nicolai Jul 12 '14 at 21:07
  • 1
    Have I ran the code code before posting, yes, works on first sight like expected. Did I thoroughly test it trough, hell no! But its pretty much just an REGEXP replacement for the LIKE, with the addition of the a tag href capability, so I'm not really expecting any problems. Another note, REGEXP is performance wise a bit more expensive then LIKE and a bit slower in the process, but its really only getting relevant for huge(r) amounts of data and even then, I think, its absolutely fine and not a real problem anyway, if the purpose justifies it. @kaiser – Nicolai Jul 13 '14 at 10:43
  • 1
    Maybe you somehow should edit the answer to include the fact that this will care about all different image sizes when they append the size to the file name. – kaiser Jul 22 '14 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.