We made an attempt to build a new function for ClassiPress over the last couple of weeks. This is the first time we have had to stop and say “its impossible to do this way”. Disappointing I know, but the real issue is simply they way WordPress stores the “sticky posts” into an array in the wp_options table. Instead of storing a sticky meta value for each post, it stores all “sticky post ID’s” in a value in the wp_options table and then uses that set of CSV’s to handle sticky posts.

This is very limiting when we are trying to ORDER and SORT posts.

SOLUTION 1 (Partial Fix)

This solution works great if you “turn off pagination”, because it re-orders the posts on the current page. However, because this filter only has access to the “posts on the current page” its unable to gather all the posts and order sticky posts first with pagination on. Instead it pushes “current page sticky posts” to the top on each of the paged results and usually you will see “a few sticky posts on every page”. Not exactly what we were trying to get, but try this code in your functions.php file to try it out:

//** Function only manages to push "current pages sticky results on top"
//add_filter( 'the_posts', 'custom_sticky_posts_to_top', 20, 1 );
add_filter( 'posts_results', 'custom_sticky_posts_to_top', 20, 1 );
function custom_sticky_posts_to_top($ref_array) {
	if(get_query_var('post_type') == 'post' || get_query_var('post_type') == 'page') return $ref_array;

	$sticky_posts = get_option('sticky_posts');
	$num_sticky_posts = count($sticky_posts);
	$num_posts = count($ref_array);
	$sticky_offset = 0;
	// Loop over posts and relocate stickies to the front.
	//echo "BEGGINING LOOP ($num_sticky_posts/$num_posts are sticky)<br />";
	for ( $i = 0; $i < $num_posts; $i++ ) {
		//var_export($ref_array[$i]->ID); echo '<br />';
		if ( in_array($ref_array[$i]->ID, $sticky_posts) ) {
			$sticky_post = $ref_array[$i];
			// Remove sticky from current position
			array_splice($ref_array, $i, 1);
			// Move to front, after other stickies
			array_splice($ref_array, $sticky_offset, 0, array($sticky_post));
			// Increment the sticky offset.  The next sticky will be placed at this offset.
			$sticky_offset++;
			// Remove post from sticky posts array
			$offset = array_search($sticky_post->ID, $sticky_posts);
			unset( $sticky_posts[$offset] );
		}
	}
	//echo '<pre>';var_export($ref_array); echo '</pre>';//exit;
	return $ref_array;
}

SOLUTION 2 (PARTIAL FIX)

Next we attempted to organize listings by adding a “ORDER BY” clause using WordPress filters. While this comes closer to bringing us the solution we sought out, it still doesn’t cover all case scenarios. It does manage to push all featured listings to the top of the list, and it works with pagination. So whats the problem? Well, if you change your pricing, you will get some unexpected results. If you manually publish and “sticky” ads, they won’t show up on top. Lastly, if you have multiple pricing packages, people who “pay more for there ads” will show up on top regardless if they paid for “featured listings” or not. It may solve the problem for some business models though, so here is the code for this solution:

//** Function only manages to organize posts by "cost to ad listing" and ignores sticky.
//if query has meta_value key, it will be ordered first by that key, then by the "order" parameter
add_filter( 'posts_clauses', 'orderby_postmeta_value_desc', 20, 1 );
function orderby_postmeta_value_desc( $clauses )
{
	global $wpdb;
	if(stristr($clauses['where'], 'meta_key'))
		$clauses['orderby'] = "$wpdb->postmeta.meta_value DESC, ".$clauses['orderby'];

	echo '<style>#post-clauses-dump { display: block; background-color: #777; color: #fff; white-space: pre-line; }</style>';
	if ( isset($_GET['debug']) && current_user_can( 'manage_options' ) )
	{
		$dump = var_export( $clauses, true );
		echo "<PRE id='post-clauses-dump'>{$dump}</PRE>";
	}
	return $clauses;

}

Yes, you can use both of these functions at the same time. However, without programming a solution that somehow “unions to SQL queries together and deals with sorting” or “programming ClassiPress to store the “sticky” value in a post_meta value, it will be impossible to have a clean solution that covers all case scenarios in “pushing featured listings to the top”.

If you have an idea or theory that you think will work, please leave us a comment so that we can further enhance this feature for the community.