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?
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:
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