Extending WP_Query — Optimise SQL query

by

I’m storing posts, a user follows in a custom table that has the columns id, post_id and user_id. To fetch posts that a user follows I have extended the WP_Query as follows:

class WP_Query_Posts_User_Follows extends WP_Query {
    function __construct($args=array()) {
        if ( !empty($args['followed_by']) ) {
            $this->followed_by = $args['followed_by'];
            add_filter('posts_where', array($this, 'posts_where'));
        }
        parent::query($args);
    }

    function posts_where($where) {
        global $wpdb;
        $table = $wpdb->prefix . 'post_followed_by';
        $where .= $wpdb->prepare(" AND ID IN (SELECT post_id FROM $table WHERE user_id = %d)", $this->followed_by);
        return $where;
    }
}

If you notice there is sub-query in the WHERE clause. My understanding is that sub-queries are bad as they hinder performance and particularly in this case where the sub-query could potentially return hundred or thousands of post_ids that a user follows. What are the alternatives that I have, considering that I need to work with WP_Query and cannot run a custom SQL directly using wpdb?

2 Answers
2

Here’s an idea, however it will require the ability to make custom SQL queries – with $wpdb.

Since you want to get posts followed by a user, you can create a view, with the following structure:

id user_id post_id post_title post_content ... (all other post fields)

don’t worry about duplicating posts.

When you select, you just have to do a simple select * from the_view where... .

Later you can cache results from this view using memcache or other object cache technique to make it faster.

Leave a Comment