Counting number of identical meta keys

Question

Counting number of identical meta keys

marctain

2022年5月27日 20:00

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!

Topic post-meta count Wordpress

Category Web

Emin Özlem · Accepted Answer · 2016年4月14日 16:52

1

Emin Özlem answered at 2016年4月14日 16:52

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

webtechie · Accepted Answer · 2014年9月30日 03:58

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.

marctain · Accepted Answer · 2012年7月19日 05:15

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

Counting number of identical meta keys

About