Sticky Posts and Pagination again

There is still a Problem with sticky Posts and Pagination. The Stickies will be placed in Front of the Posts but AFTER the Pagination, because the Pagination uses the mysql LIMIT expression with the Query. The Sticky Posts will be load by a separate Query (get_option) because this Information is NOT a Property of the Post. The Stickies are placed in the Option Table.

This is a Design Error !

The GOAL is: get all Posts with the Stickies in Front of the List before the Pagination.

To solve the Problem, we have to include the Sticky Information into the Query, so that the Pagination can work fine with the LIMIT expression and returns the wanted Result.

We can sort the Query Arguments by the ‘post__in’ orderby -value. To be able to use it, we need an array with all the post-ids ordered by the stickies first.

1. get the sticky-post-id’s from the option-Table
2. get the post-ids from the post-Table and sort it

function getSortedPostIds() {
	global $wpdb;

	$stickies = get_option('sticky_posts');
	$sql = "SELECT ID, post_date, ID in(" . implode(',', $stickies) . ") as sticky FROM " . $wpdb->posts . " WHERE post_type = 'post' ORDER BY sticky DESC, post_date DESC";
	$posts = $wpdb->get_results($sql);

	$pids = array();
	foreach($posts as $p) {
		$pids[] = $p->ID;
	}
	return $pids;
}

3. Modify the Main Query

function _modify_posts_query( $query ) {
	if( $query->is_home() && $query->is_main_query()) {
		$query->set('ignore_sticky_posts', true);
		$query->set('post__in', getSortedPostIds());
		$query->set('orderby', array('post__in' => 'DESC', 'modified' => 'DESC'));
	}
}
add_action( 'pre_get_posts', '_modify_posts_query');

This is a working Example achieving the given Goal.