Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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?

share|improve this question
    
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... –  G. M. Jul 29 '13 at 13:11
5  
Benchmark it. Do not worry about performance issue until you profile and study it. –  Rarst Jul 29 '13 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 '13 at 14:07
1  
@John : There is a posts_join filter. –  s_ha_dum Jul 29 '13 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 '13 at 17:34
show 6 more comments

2 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
    
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 at 3:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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