Counting number of identical meta keys

Arya: 2 weeks ago

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!

Ethan: 2 weeks ago

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.