2 Answers
2

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.

from the post_type of publications, the terms slug of %s, and has the taxonomy of categories, but you are never selecting the taxonomy topics. If you look at the table, you have one column reading taxonomy. In this column, you can either have category or topics. This is an example of two rows (plus the header):

Hi, thanks for looking into this, but it doesn't seem to be working. The query isn't bring back any results WHERE $wpdb->posts.post_type = 'publications' AND $wpdb->terms.slug = %s AND $wpdb->term_taxonomy.taxonomy = 'category' AND $wpdb->term_taxonomy.taxonomy = 'topics' ORDER BY $wpdb->term_taxonomy.taxonomy = 'topics' DESC I think this is because Categories and Topics are in the same table and there's never a case where the taxonomy equals both 'category' and 'topics'. Maybe a new table has to be created which lists topics only?
–
John KingOct 9 '12 at 17:16

True...I don't know why I didn't adjust...given how much I had looked at the table. You could also try WHERE $wpdb->posts.post_type = 'publications' AND $wpdb->terms.slug = %s AND $wpdb->term_taxonomy.taxonomy = 'category' OR $wpdb->term_taxonomy.taxonomy = 'topics'
–
JonathanOct 9 '12 at 19:32

Hi Jonathan, thanks for your input. Your new query using AND is trying to work but it's picking out each publication twice as it matches the post that are in the correct 'category' AND the correct 'topic'. I think the only solution may involve the creation of a new database table.
–
John KingOct 10 '12 at 8:55

Possibly. You could also try switching the last two AND's to OR's. Just a thought
–
JonathanOct 11 '12 at 15:43

Hi Jonathon, this doesn't work either as it lists every publication twice. Once where it matches the 'category' and once where it matches the 'topic'. It does order all the topic ones but not the ones matched by categories.
–
John KingOct 11 '12 at 18:37