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 have one post id x, one category id y. I want to retrieve the position at which the post will appear in the category.

  • It is confirmed that the post is from the category
  • Post order will be based on a numeric value set in post meta, meta key 'facebook_shares'
  • Category have more that 1k other posts.

I am looking for a best solution which could consume less database query usage and single query will be the best.

Thanks in advance.

share|improve this question
1  
What do you mean by "the position at which the post will appear"? Are you looking for some kind of offset? –  s_ha_dum Feb 1 at 23:23
    
Yes offset, at which position/index the post will appear. Like: if i query post with no limit, the post appears at 297, after 296 posts. –  Shazzad Feb 1 at 23:28

1 Answer 1

up vote 6 down vote accepted

If I understand well, you need to retrieve all the posts having category 'y' and also meta key 'facebook_shares', after that, you need the position of a specific post.

My proposal is:

  1. Create a class that extends WP_Query and filter the results for required category and meta field. This class should also filter the SQL query to set the meta value as a property of post objects returned by WP_Query::get_posts
  2. an Spl ArrayIterator to iterate all posts and put every post inside a
  3. SplHeap that implements compare method looking at meta properties of post objects. SplHeap has the propery of automatic order elements passed inserted in it.
  4. a third class that make use of previous 2 by composition, to get the order of wanted post.

And only one db query is done.

Let's code.

First of all write our SplHeap implementation:

class SharesHeap extends SplHeap {  
  public function compare( $post1, $post2 ) {
    $key = FB_Shares_Query::$key;
    if ( ! $post1 instanceof WP_Post || ! isset( $post1->$key ) ) return 0;
    if ( ! $post2 instanceof WP_Post || ! isset( $post2->$key ) ) return 0;
    return (int)$post2->$key - (int) $post1->$key;
  }
}

Now the class that extends WP_Query to get the posts. Constructor of this class accept a category term, that can be passed as category id, a category slug or even a category object. By default the 'post_type' argument is set to 'post', but that can be overriden passing a second argument to constructor.

class FB_Shares_Query extends WP_Query {

  public static $key = 'facebook_shares'; // change the metakey here, if needed
  protected static $args = array( 'nopaging' => TRUE ); // get all posts

  public function __construct( $cat = NULL, $post_type = 'post' ) {
    if ( is_object($cat) && isset($cat->term_id) ) $cat = $cat->term_id;
    $cat_field = is_numeric($cat) ? 'cat' : 'category_name';
    self::$args['post_type'] = ! empty( $post_type ) ? $post_type : 'post';
    self::$args[$cat_field] = $cat;
    parent::__construct( self::$args );
  }

  public function get_posts() {
    add_filter('posts_clauses', array(__CLASS__, 'my_filters') );
    $results = parent::get_posts();
    remove_filter('posts_clauses', array(__CLASS__, 'my_filters') );
    return $results;
  }

  public static function my_filters( $pieces ) {
    $key = self::$key;
    $meta = $GLOBALS['wpdb']->postmeta;
    $posts = $GLOBALS['wpdb']->posts;
    $pieces['fields'] .= ", {$meta}.meta_value as {$key}";
    $pieces['join'] .= "INNER JOIN {$meta} ON ({$posts}.ID = {$meta}.post_id)";
    $where = " AND ( {$meta}.meta_key = '{$key}' AND ";
    $where .= " CAST( {$meta}.meta_value AS SIGNED ) > 0 )";
    $pieces['where'] .= $where;
    return $pieces;
  }
}

Then the class that "glues" previous:

class FB_Shares_Order {

  protected $heap;
  protected $posts;

  public function __construct( SplHeap $heap )  {
    $this->heap =  $heap;
  }

  public function getOrder( WP_Post $post = NULL ) {
    if ( ! $this->posts instanceof ArrayIterator ) return -1;
    if ( ! isset( $post->ID ) ) return FALSE; 
    while( $this->posts->valid() ) {
      $this->heap->insert( $this->posts->current() );
      $this->posts->next();
    }
    $position = 0;
    while( $this->heap->valid() ) {
      $current = $this->heap->extract();
      if ( $current->ID == $post->ID ) return $position + 1;
      $position++;
    }
    return FALSE;
  }

  public function getPosts( WP_Query $query ) {
    if ( $query->post_count > 0) $this->posts = new ArrayIterator( $query->posts );
  }
}

Finally, a function that work as façade to the 3 classes. It accepts a post (id or object) and a category (id, slug or object):

function getFB_Shares_Order( $post, $cat ) {
  if ( is_object($post) && isset( $post->ID ) ) $post = $post->ID;
  if ( ! is_numeric($post) || ! (int) $post > 0 ) return FALSE;
  if ( ! has_category($cat, $post) ) return FALSE;
  $order = new FB_Shares_Order( new SharesHeap );
  $order->getPosts( new FB_Shares_Query( $cat ) );
  return $order->getOrder( get_post( $post ) );
}

Usage Example

global $post;
$position = getFB_Shares_Order( $post, 'uncategorized' );

Returning values:

If the query return some posts and the post given is one of them, then the function returns the post order in the post collection, looking at 'facebook_shares' meta key.

E.G. the query returns 3 posts, having values of 'facebook_shares' respectively: 10, 15, 30 and you pass to function the id of post that has 'facebook_shares' = 15, then the function return 2, because the post is the second in order.

If the query returns no posts, then the function return -1.

If the query returns some posts, but the wanted posts is not present among them, the function return false.

Caveats

There are some issues on core WP_Query using meta_query without specific values ('compare' = EXIST or NOT EXIST) specially if the query contains also a tax query. To avoid them, in the class that extends WP query use some filters on SQL query, and where clause is setted "manually". However, if there are some external code (plugin, themes) adding custom filters on query (using posts_where, posts_join and similar filter hooks) that code can break the call behaviour.

The code is very quicky tested, and seems to work, however.. no warranty.

share|improve this answer
    
Thank you. I have tried with $post (object), $post_id for the 1st argument, $cat (object) & id/slug/name , but all of them are returning -1. Next i tried removing all external filters from posts_clauses with remove_all_filters( 'posts_clauses' ) just before where you used add_filter on posts_clauses, but that didn't helped either. Another thing, isn't there any way to get the index without querying and looping through all of the post ? –  Shazzad Feb 2 at 3:07
2  
@Shazzad If the function returs -1 the problem is on the query. Try to debug the query, possibly looking at the SQL performed or var_dump( new FB_Shares_Query( $cat ) ). Regarding get the index whitout looping, I don't think is possible, or at least I can't figure out how to do that. –  G. M. Feb 2 at 3:24
    
I don't understand why this didn't get more upvotes by now. Great answer! –  kaiser Feb 3 at 5:08
    
@kaiser maybe people hate spl :D ... –  G. M. Feb 3 at 6:03
1  
cool, interesting to see the SplHeap usage +1 –  birgire Feb 3 at 13:49

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.