Relational_Hot_Cold_Filter.php

add_filter( 'query', 'hot_cold_database_logic' );
function hot_cold_database_logic( $query ){
	global $wpdb, $pagenow;

	if ( strpos( $query, sprintf( '%sposts' , $wpdb->prefix ) ) === false ) return $query;
	if ( strpos( $query, 'SQL_CALC_FOUND_ROWS' ) !== false ) return $query;
	if ( in_array( $pagenow, array( 'post.php', 'post-new.php' ) ) ) return $query;

	$query_posts     = $query;
	$limiters_orders = '';

	#find ORDER BY and|or LIMIT clauses and remove it from main query
	if( ( $position_order_by = strpos( $query, 'ORDER BY' ) ) !== false ):
		#first filter remove order by and so on
		$query_posts     = substr( $query, 0,  $position_order_by );
		#hold rest of query
		$limiters_orders = str_replace( sprintf( '%sposts.' , $wpdb->prefix ) , '', substr( $query, $position_order_by ) );
	elseif ( ( $position_limit = strpos( $query, 'LIMIT' ) ) !== false ):
		#first filter remove order by and so on
		$query_posts     = substr( $query, 0,  $position_limit );
		$limiters_orders = substr( $query, $position_limit );
	endif;

	#second filter, create a string query from wp_posts to wp_posts_hot
	$query_hot_posts = str_replace( sprintf( '%sposts' , $wpdb->prefix ) , sprintf( '%sposts_hot' , $wpdb->prefix ) , $query_posts );

	#third filter get only collumns used in order by for final query
	$columns_from_order = $limiters_orders;
	if ( ( $limit =  strpos( $limiters_orders, 'LIMIT' ) ) !== false ) $columns_from_order = substr( $limiters_orders, 0,  $limit );
	$columns_from_order = preg_replace( '#DESC|ASC|ORDER BY#', '', $columns_from_order );


	if ( $columns_from_order != '' ):
		if (  strpos( $query_hot_posts, '*' ) === false  ):
		$query_hot_posts =  preg_replace('#FROM#', sprintf(',%s FROM', $columns_from_order ), $query_hot_posts, 1);
		$query_posts     =  preg_replace('#FROM#', sprintf(',%s FROM', $columns_from_order ), $query_posts, 1);
		endif;
	endif;

	if ( strpos( $limiters_orders, '.') !== false ):
		$limiters_orders = preg_replace( "# [a-z\_?]*\.#i", ' ', $limiters_orders );
	endif;

	$query = <<<SQL
	(
		$query_hot_posts
	)
	UNION ALL
	(
		$query_posts
		AND NOT EXISTS (
			$query_hot_posts
			)
	)
	$limiters_orders
SQL;
	return $query;
}