0

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.

1
  • Please use the WYSIWG editor buttons to format code. This is a pain to read.
    – kaiser
    Nov 21, 2012 at 18:49

3 Answers 3

2

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.

12
  • @ Tom J Nowell: I don't understand how multiple metafields should help me. Example: There is already one field with the value 10. Request A would now add a second field with the value 11. But what will the concurrent requests C and D do? They will possible start from an outdated value...
    – Igor
    Nov 21, 2012 at 18:00
  • You are right. I would use Google Analytics, if I would count simple visits. But in this case, "visits" are just an example. I am counting data GA cannot count. So using GA is no option.
    – Igor
    Nov 21, 2012 at 18:04
  • Using multiple tables via simple joins won't affect the performance. So this is not the problem. And to your final note: There is no different in talking to your cache or running a very simple SELECT statement (if you are already connect to your database). This would be more a problem, if it would be an additional query, but thanks to the join; it isn't.
    – Igor
    Nov 21, 2012 at 18:09
  • It helps you because the locking problem is no longer relevant. Instead take the meta with the highest value and increment it then save, cleanup some old at regular intervals
    – Tom J Nowell
    Nov 21, 2012 at 20:39
  • I'm not talking about a select, I'm talking about the UPDATE/INSERT that modifying the database on every single visit would involve. Using multiple meta fields would at least mean using an INSERT rather than an UPDATE which would at least be faster. Also by focusing on accuracy and instant updates, you're paying the price in page speed, as you're unable to spread the cost out over time.
    – Tom J Nowell
    Nov 21, 2012 at 20:42
1

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...

3
  • Am I missing something? I cannot run an "INSERT ... ON DUPLICATED KEY UPDATE ..." query against postmeta table. Making sure there is already a row I can update would be an additional query... am I wrong?
    – Igor
    Nov 21, 2012 at 18:12
  • no, his SQL statement grabs, increments, and updates the post meta all in one action. Also what is this INSERT you speak of? I see an UPDATE statement, not an INSERT.
    – Tom J Nowell
    Nov 21, 2012 at 20:45
  • ...and that's the problem: To be able to "grab", there must be already something. With my statement (dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html) I would do an INSERT, but when I already have data, MYSQL would just update the existing row.
    – Igor
    Nov 21, 2012 at 20:54
0

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')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.