How to Limit the number of order indexes in post queries
When using Auto Sort functionality, The Advanced Post Types Order plugin apply the order automatically on front side. This works very easy and does not require any coding skills, the customised order is inserted automatically in all front/dashboard queries which match the sort set-up.
In large sites with hundred of thousand of posts, the order indexes list inserted into the query can slightly decrease the overall speed. Considering that on a page load there are at least several queries, this might become an issue. Fortunate there’s an easy approach for this, through a filter a chunk of the whole list is being used instead, to avoid mentioned scenario. The first part of the order list is being used ( the first 1000 indezes), while the rest of the posts will be ordered as default by post date.
add_filter( 'apto/get_orderby', 'apto_get_orderby', 10, 5 );
function apto_get_orderby( $new_orderBy, $orderBy, $sort_view_id, $query, $order_list = FALSE )
{
global $wpdb;
if ( $order_list === FALSE )
return $new_orderBy;
//Only on front side
if ( is_admin() )
return $new_orderBy;
$_LimitIndexLiatTo = 1000;
$order_list = array_slice( $order_list, 0, $_LimitIndexLiatTo );
$query_order = isset($query->query['order']) ? strtoupper($query->query['order']) : 'ASC';
$counter = 0;
$orderBy = "CASE ";
foreach ( $order_list as $value )
{
$counter++;
$orderBy .= " WHEN ". $wpdb->posts .".ID = ". $value ." THEN ". $counter;
}
$counter++;
if ( $query_order == 'DESC')
$counter = 0;
$orderBy .= " ELSE ". $counter ." END, ".$wpdb->posts.".post_date " . $query_order;
return $orderBy;
}
- *The above code should be placed into theme functions.php or a file into wp-content/mu-plugins/
Important to understand the query ‘order’ argument, which usually should be the default ‘ASC’, or the list will be reverted.