Extending WP_Query — Optimise SQL query

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?

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?

• wp-query
• sql
• posts-where

Share Improve this question asked Jul 29, 2013 at 12:52 JohnJohn 1,44922 gold badges1919 silver badges2929 bronze badges 11

• I think you have no alternatives. You can perform the subquery and pass the result array inside the 'IN' clause, but is this better? I really don't think so. Maybe you can perform the query via ajax and give a visual feedback standing by results... – gmazzap Commented Jul 29, 2013 at 13:11
• 6 Benchmark it. Do not worry about performance issue until you profile and study it. – Rarst Commented Jul 29, 2013 at 14:05
• 1 A JOIN may perform better than the where clause but what you have is a pretty straightforward query. With the right indexes there may be no problem. But I agree with @kaiser. This is a pure SQL question and probably best asked wlsewhere, perhaps here: dba.stackexchange.com – s_ha_dum Commented Jul 29, 2013 at 14:07
• 1 @John : There is a posts_join filter. – s_ha_dum Commented Jul 29, 2013 at 17:25
• 2 @John then you are asking for hypothetical solution, to hypothetical problem on database schema that isn't native to WordPress. I concur this calls for different stack. :) – Rarst Commented Jul 29, 2013 at 17:34

 |  Show 6 more comments

2 Answers 2

Sorted by: Reset to default 0

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.

You use the best way:) Maybe, if you want to optimize your query you can use posts 2 posts plugin. You will not need to filter the query. Link - https://github.com/scribu/wp-posts-to-posts/wiki