Short of raw SQL, can I query for multiple attachment metadata that have a given array key?

Question

With raw SQL through $wpdb, I believe that I could do something along the lines of this (untested):

SELECT post_id, meta_value
FROM postmeta
WHERE meta_key = '_wp_attachment_metadata'
AND meta_value LIKE '%:"custom_key";a:%'

That said, if there is built-in functionality for this kind of query then I would rather not reinvent the wheel. All of my codex digging has just returned ways to get the attachment metadata for a given id or set of ids, which will not do what I need. I could also retain a list of IDs with this particular array key, but I'd rather not keep this data since it seems like extra clutter...

Answer 1

While you can do this with WP_Query, I'd do this the way you are trying to avoid-- save a list in a separate key. The "pros" strongly outweigh the "cons" in my mind.

1. LIKE queries with wildcards, especially with leading wildcards, are not efficient-- aka, "slow"
2. and a LIKE query on serialized data is going to be prone to error
3. You will likely be running this query on display, which means that users see a delay

Whereas...

1. The "space" needed to store another value in, say, the options table is negligible
2. And the query to retrieve the value is as about as efficient as a query can be
3. The work is pushed almost entirely to the backend, when the data is saved, so that any delay is seen by the administrators/authors of the site and not by the ordinary users.

You can add and retrieve the data with the core functions update_option and get_option, the latter of which has caching mechanisms built in.

Answer 2

You can do this with WP_Query by making use of the meta_query parameter. Do it like shown below. If you use attachment as value for the post_type parameter, remember this:

The default WP_Query sets 'post_status'=>'publish', but attachments default to 'post_status'=>'inherit' so you'll need to explicitly set post_status to 'inherit' or 'any' as well. (See post_status, below)

As stated in the Type Parameter Section of the WP_Query codex page.

$args = array(  
    'post_type' => 'attachment',  
    'post_status' => 'inherit',  
    'post_per_page' => -1,
    'meta_query' => array(  
        array(  
            'key' => '_wp_attachment_metadata',  
            'value' => 'your-key',  
            'compare' => 'LIKE'  
        )  
    )  
);  
$query = new WP_Query( $args );