count - Counting number of identical meta keys

admin管理员组

文章数量:1122846

Im looking to count how many meta keys exist within a post, because each time a specific user action happens, a new meta key is created with a date in it.

I have this, which does not return anything (I'm looking for a number)...

        ...
$post_title = $row->post_title;
        $id = $row->ID;

$post_count = $wpdb->get_var("
                SELECT COUNT(DISTINCT $wpdb->postmeta.meta_key) FROM $wpdb->posts
                LEFT JOIN $wpdb->postmeta 
                WHERE $wpdb->posts.post_type = 'things'
                AND $wpdb->postmeta.meta_key = 'dates' 
                AND post_id = $id
                ");

        echo $post_title . ' (' . $post_count . ')' . "\n";
    }

Similarly, I'd like to count the number of meta keys in total related to a custom post, any help on either of these would be greatly appreciated!

Im looking to count how many meta keys exist within a post, because each time a specific user action happens, a new meta key is created with a date in it.

I have this, which does not return anything (I'm looking for a number)...

        ...
$post_title = $row->post_title;
        $id = $row->ID;

$post_count = $wpdb->get_var("
                SELECT COUNT(DISTINCT $wpdb->postmeta.meta_key) FROM $wpdb->posts
                LEFT JOIN $wpdb->postmeta 
                WHERE $wpdb->posts.post_type = 'things'
                AND $wpdb->postmeta.meta_key = 'dates' 
                AND post_id = $id
                ");

        echo $post_title . ' (' . $post_count . ')' . "\n";
    }

Similarly, I'd like to count the number of meta keys in total related to a custom post, any help on either of these would be greatly appreciated!

• post-meta
• count

Share Improve this question asked Jul 19, 2012 at 5:00 marctainmarctain 56733 gold badges88 silver badges1919 bronze badges

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 0

You have a structural problem with your data. Serialized data in the database is terrible if you need to search over pieces of that serialized data.

There is no reliable, efficient, and certainly no easy, SQL query to search over serialized data. "serialization" is a PHP mechanism. It isn't SQL. To the database that is just a string. Your only SQL choice is a regex on the string. WP_Query certainly won't do it. That functionality is not built in, probably for the reason listed above. Iterating over the data is about the only solution you have given the data structure you are dealing with.

However, the correct solution in my opinion is to alter your data structure so that you are saving granular pieces of data in the database. In other words, use a lot of different keys for the values that you need to search over.

You can simply do : count( get_post_custom_keys($pid) )

I solved this problem with some more trial and error:

This got me the # of post meta with the correct id,

...
        $post_title = $row->post_title;
        $id = $row->ID;

$post_count = $wpdb->get_var("
                SELECT COUNT(*) FROM $wpdb->postmeta
                WHERE post_id = $id

本文标签： countCounting number of identical meta keys