5

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?

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
    Jul 29, 2013 at 13:11
  • 6
    Benchmark it. Do not worry about performance issue until you profile and study it.
    – Rarst
    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
    Jul 29, 2013 at 14:07
  • 1
    @John : There is a posts_join filter.
    – s_ha_dum
    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
    Jul 29, 2013 at 17:34

2 Answers 2

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.

-2

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

1
  • Link only answers are discouraged. If something happens to that link the answer becomes useless. Please try to make your answer here stand alone. And see How to Answer.
    – s_ha_dum
    Apr 10, 2014 at 3:26

Your Answer

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

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