Finally, I have managed to make this work. I've found the holy grail of ordering custom post types by a custom taxonomy, with pagination. The code isn't pretty but it works.
My method was to forget about SQL queries and to just select ALL the custom posts that matched the correct Category and the correct Custom Post Type (my Category is taken from the current page slug, i.e. foo.com/bar 'bar' is my category).
Then create a custom array from the result containing each Post ID, Custom Taxonomy ('topics') Term and Custom Taxonomy Slug.
Then SORT this array. Then SLICE this array depending on what page you're on, i.e. Page 1, Page 2, Page 3. We just selecting the Publications to show on that page. And then loop the results.
I manage to 'group' my results into these 'topics' by just checking if we printed out the same Custom Taxonomy Term on the previous post.
All the pagination is done using the $current_paged_num code near the top and the paginate links code at the bottom.
Yes, my code is ugly and probably a huge resource hog but it works. And so I'm sharing it here in case I can help anybody else out. If you think you can tidy or prettify this code then please show us here.
<?php // Run new query on Publications custom post type
// Appologies for complexity but this is the only way to do this in wordpress
$posts_per_page = 6; // Set number of Posts per page
$parent_post_data = get_post($post->post_parent);
$parent_post_slug = $parent_post_data->post_name; // Get Parent Page Name to find the relevant Stakeholder section
$current_paged_num = 0;
$current_paged_num = intval(get_query_var('paged')); // Find current Pagination Page number
if (($current_paged_num) > 0) { // Calculate offset so that the correct posts are fetched from the database
$offset = (($current_paged_num-1) * $posts_per_page);
} else {
$offset = 0;
$current_paged_num = 1;
}
$query = new WP_Query("post_type=publications&category_name=$parent_post_slug&showposts=-1"); // Get ALL posts for this section
$total = $query->post_count; // Calculate total number of posts
if ($total > 0) { // If we find relevant posts
$x = 0; // Setup Array numbers
while($query->have_posts()): $query->next_post(); // Create new array containing Post IDs and Topic slugs
$customTermSlug = 'unclassified';
$customTermName = 'Unclassified';
$new_terms = get_the_terms( $query->post->ID, 'topics' );
if ($new_terms) {
foreach ($new_terms as $term) {
$customTermSlug = $term->slug;
$customTermName = $term->name;
break;
};
};
$new_array[$x][customID] = $query->post->ID;
$new_array[$x][customTermSlug] = $customTermSlug;
$new_array[$x][customTermName] = $customTermName;
$x++;
endwhile;
function subval_sort($a,$subkey) { // Sort array by Topic slug
foreach($a as $k=>$v) {
$b[$k] = strtolower($v[$subkey]);
}
asort($b);
foreach($b as $key=>$val) {
$c[] = $a[$key];
}
return $c;
}
$ordered_array = subval_sort($new_array, 'customTermSlug');
$filtered_array = array_slice($ordered_array, $offset, $posts_per_page); // Slice (filter) the array to remove all unneccessary items
if ($filtered_array): ?>
<section class="article-list">
<?php foreach ($filtered_array as $item) {
$postID = $item[customID]; // Set up item variables
$customTermName = $item[customTermName];
<article class="clearfix">
<?php $post_array = get_post($postID); ?>
<?php if ($customTermName != $previousTermName) { ?>
<h3><?php echo $customTermName; ?></h3>
<?php } ?>
<h4><?php echo $post_array->post_title; ?></h4>
<?php echo apply_filters('the_content', $post_array->post_content); ?>
<?php $previousTermName = $customTermName; ?>
</article>
<?php } ?>
</section>
<div class="pager">
<?php // Paginate WP using method http://wordpress.stackexchange.com/questions/43489/paginate-custom-post-type-page
$big = 999999999; // need an unlikely integer
echo paginate_links( array(
'base' => str_replace( $big, '%#%', get_pagenum_link( $big ) ),
'format' => '?paged=%#%',
'current' => max( 1, get_query_var('paged') ),
'total' => ceil($total / $posts_per_page),
'prev_text' => __('Previous | '),
'next_text' => __(' | Next')
)); ?>
</div>
<?php endif;
wp_reset_query();
} ?>