Adding custom field to post object from custom table

Question

I am writing a counter plugin for WordPress, which will keep track of daily and total views.

Normally you would use post meta fields for storing such information. But I cannot:

I cannot use meta fields, because meta fields cannot be updated without the risk of loosing previous values (no locking). For example:

The meta field "dviews" has the value of 10. Request #1 comes in, it will fetch the field and call update_meta with $oldValue + 1 (=11). This will work in a world where only one request can occur. But in real world, there are multiple concurrent requests. So when the worker from Request #1 is saving "11 dviews", other requests could have already increased the value to 17. Result: The worker from request #1 will set the stats back to 11 - I would loose 7 views. On a quiet busy site, this can be a real problem.

Solution: I created another table "views" with two fields: post_id and dviews.

I can now run an "INSERT ... ON DUPLICATED KEY UPDATE ...*" statement. This is very powerful and I save a query.

Problem: I now want to show the views in the admin dashboard (as column in the edit.php). I could join my table views via hooks like *pre_get_posts*, *posts_fields* and *posts_joins*, so my fields are available in the loop.

But when I want to get a post object (I am using *get_post*), my fields are missing, because the function doesn't use the posts from the loop.

What can I do?

• Write my own "get_post" function?
• Is there another "get_post" function I could use?
• Is it safe to rely on the global $post variable which will show up?
• Adding my columns to WordPress' posts table?

I want to avoid additional queries just for the stats. When displaying 100 postings in a dashboard table, I would get additional 100 queries just for the stats. Not good. Is there a reliable way to access the already populated posts from the loop? I really don't understand why "get_post" doesn't use this data. Maybe someone can explain?

I also want to avoid manipulating the core tables. But I am not sure if there is a better way, because

function &get_post(&$post, $output = OBJECT, $filter = 'raw') {

    // [...]

        if ( ! $_post = wp_cache_get($post_id, 'posts') ) {
            $_post = $wpdb->get_row($wpdb->prepare("SELECT * FROM $wpdb->posts WHERE ID = %d LIMIT 1", $post_id));

    // [...]
}
// (from wp-includes\post.php)

will ask a cache and run its own non-hookable query at least.

Answer 1

Alternatively, instead of updating your metafield, add a new one!

Then the problem becomes, which meta has the highest value, and how do I cleanup the other meta fields.

How I Would Do It

Instead of reinventing the wheel, I can see that Google has already done a brilliant job of counting how many people viewed a page. I can also see Google Analyticator even shows me the top 5 and gives me a chart.

So instead, use the Google APIs, or integrate some other existing solution. Even wordpress.com & WP Stats sends the results off to a remote machine rather than hosting the entire stats package and database locally.

Using post meta, or options, or a custom table etc etc is going to slow down your page loads, make your databases size much larger, and introduce many problems, not just locking table rows.

A Final Note On Performance

I would also recognise that there is an inherent trade off here, between speed/scalability, and how fresh your data is. If you have a counter on the frontend that's 100% accurate all the time, there are computational and logistical costs associated with it.

An approximate value on the other hand doesn't need to be updated as often and can be cached more easily. Suddenly your program has breathing room and you can adjust the update rate to scale with your site and environment.

Answer 2

MySQL will prevent collisions for you on records that are being written/edited. So you actually CAN use post meta fields to store counters, etc. Just write your own query that reads the value and updates it all at the same time.

Example:

update wp_postmeta set meta_value = ( (CAST meta_value AS DECIMAL) + 1 ) where meta_key = "my_counter" and post_id = 123

Something like that...

Answer 3

First, select and join your custom table:

add_filter('post_clauses', function($clauses) {
    $clauses['fields'] .= ", my_table.my_column";
    $clauses['join'] .= " JOIN my_table ON my_table.post_id = wp_posts.ID";
    return $clauses;
});

Then, in the loop you can access the field with get_post_field('my_column')