Adding custom field and querying from post table

Question

I've been trying to figure this out for a while now. So I'm trying to add a custom field call "counter" to every single post. I try simply inserting the field "counter" to the post table. Every time a user clicks a button on the post, it increments the counter So far so good.

But now the tricky part is being able to get back the information with WP_Query. I want to do a custom query for posts where the counter is greater than 0, loop through the posts, and display them with template tags.

If i were to write an sql stamens it would look like this

$query = "SELECT * FROM $wpdb->post WHERE counter > 0";

But I can't do a normal SQL query because then I won't have access to template tags such as Wordpress's "show next page | show older page" template tags.

I'm not looking for a plugin but I'm trying to find a way that i can add a field to the post table and be able to query the posts base on the custom fields, loop through the result, and display the information about the post. If you guys have any suggestions, I would really appreciate it.

Note: Am i suppose to add the custom field to post meta? Looking at the SQL table, it doesn't seem like a right place to do it...

Answer 1

Adding a column to the posts table is a bad idea, this is indeed what Custom Fields (Post Meta) are for. Query and sort your posts based on the values of your custom field with WP_Query and a post meta (custom field) query:

$query = new WP_Query(
    array(
        'meta_key' => 'counter',
        'meta_value' => 0,
        'meta_compare' => '>'
    )
);

Answer 2

Milo is right, it's bad idea, but i was forced to do that on one big site, because with relation on unindexed meta_value it was too slow. Here is my example with post_views column. Use it with caution and only if you are aware of what you are doing!

<?php


define("AMP_OPTION_VIEWS", '_prefix_views');


/**
 * Pridava funkie okolo pocitania zobrazeni a kliknuti pre posty
 */
function prefix_get_count($post_id = 0)
{
    $post = get_post($post_id);

    return isset($post->post_views) ? intval($post->post_views) : 0;
}

function prefix_set_count($views, $post_id = 0)
{
    global $wpdb;
    $post_id = $post_id ?: get_the_ID();

    if ($post_id) {
        $wpdb->query($wpdb->prepare("UPDATE " . $wpdb->posts . " SET post_views = %d WHERE ID = %d", $views, $post_id));
    }

    return !$wpdb->last_error;
}

function prefix_increment_count($post_id = 0)
{
    global $wpdb;
    $post_id = $post_id ?: get_the_ID();

    if ($post_id) {
        $wpdb->query($wpdb->prepare("UPDATE " . $wpdb->posts . " SET post_views = post_views + 1 WHERE ID = %d",
            $post_id));
    }

    return !$wpdb->last_error;
}


function prefix_posts_columns($defaults)
{
    if (current_user_can('edit_published_posts')) {
        $defaults[AMP_OPTION_VIEWS] = __("Views");
    }

    return $defaults;
}

add_filter('manage_posts_columns', __NAMESPACE__ . NAMESPACE_SEPARATOR . 'posts_columns');

function prefix_posts_custom_column($column_name, $post_ID)
{
    if ($column_name == AMP_OPTION_VIEWS) {
        echo prefix_get_count($post_ID);
    }
}

add_action('manage_posts_custom_column', __NAMESPACE__ . NAMESPACE_SEPARATOR . 'posts_custom_column', 10, 2);

/**
 * This function will return most viewed posts
 *
 * @return WP_Query
 */
function get_most_views($args)
{
    global $wpdb;

    $args['suppress_filters'] = false;
    $orderby_function = function ($orderby) {
        global $wpdb;

        return "{$wpdb->posts}.post_views DESC";
    };

    add_filter('posts_orderby', $orderby_function);
    $return = new WP_Query( $args );

    if (stripos($wpdb->last_error, "posts.post_views' in 'order clause'") !== false) {
        global $wpdb;
        $exists = $wpdb->get_row("SELECT meta_id FROM {$wpdb->postmeta} WHERE meta_key = '" . AMP_OPTION_VIEWS . "' LIMIT 1");
        // zisti meta_key, ak je stara tema je iny
        $meta_key = $exists ? AMP_OPTION_VIEWS : '_im_views';

        // prida sa novy stlpec
        $add_views_column = "ALTER TABLE `{$wpdb->posts}` ADD COLUMN `post_views` INT NOT NULL DEFAULT '0';";
        $wpdb->query($add_views_column);

        // prida sa index pre stlpec
        $add_views_index = "ALTER TABLE `{$wpdb->posts}` ADD INDEX `post_views` (`post_views`);";
        $wpdb->query($add_views_index);

        // updatnu sa views z metadat
        $update_values = "UPDATE {$wpdb->posts} 
      SET post_views = IFNULL((SELECT meta_value FROM {$wpdb->postmeta} 
      WHERE post_id = ID AND meta_key = '{$meta_key}' LIMIT 1), 0);";
        $wpdb->query($update_values);

        // vymazu sa zbytocne metadata
        if (!$wpdb->last_error) {
            $update_values = "DELETE FROM {$wpdb->postmeta} WHERE meta_key = '{$meta_key}'";
            $wpdb->query($update_values);
        }

        $return = new WP_Query( $args );
    }
    remove_filter('posts_orderby', $orderby_function);

    return $return;
}

function prefix_ajax()
{
    $post_id = $_POST['postID'];

    $result = prefix_increment_count($post_id);

    echo $result ? "ok" : "fail";
    exit;
}

add_action("wp_ajax_nopriv_amp_views", __NAMESPACE__ . NAMESPACE_SEPARATOR . 'ajax');
add_action("wp_ajax_amp_views", __NAMESPACE__ . NAMESPACE_SEPARATOR . 'ajax');