Custom SQL Query on Custom Post Type. Order by Taxonomy?

Question

I've created a custom post type called 'publications' and a custom taxonomy called 'topics'. I'm also using the standard taxonomy of 'category'.

My Custom Query makes sure that it fetches ALL 'publications' that are in the correct 'category' but I'd like it to also ORDER BY the additional 'topics' taxonomy.

This custom query does fetch all the correct 'publications' but I'm having no luck with the ORDER BY section:

$querystr = "
    SELECT * 
    FROM $wpdb->posts
    LEFT JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    LEFT JOIN $wpdb->terms ON ($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
    WHERE $wpdb->posts.post_type = 'publications'
    AND $wpdb->terms.slug = %s
    AND $wpdb->term_taxonomy.taxonomy = 'category'
    ORDER BY $wpdb->term_taxonomy.taxonomy = 'topics' DESC
";
$pageposts = $wpdb->get_results($wpdb->prepare($querystr, array($parent_post_slug)));

The $parent_post_slug is the 'category' name. And it's fetching all the correct Posts. Just how would I order them by the taxonomy called 'topics'?

An example of the order I'd like:

Category Name = Fiction (This page is just showing the fiction publications)
Publication 1 = has custom taxonomy topic of Alligators
Publication 2 = has custom taxonomy topic of Alligators
Publication 3 = has custom taxonomy topic of Antelopes
Publication 4 = has custom taxonomy topic of Buffalos
Publication 5 = has custom taxonomy topic of Buffalos

Any idea of what I should be using in the ORDER BY line to get this to work?

Answer 1

Just looking at the term_taxonomy table, you are selecting everything correctly, but the last part:

WHERE $wpdb->posts.post_type = 'publications'
AND $wpdb->terms.slug = %s
AND $wpdb->term_taxonomy.taxonomy = 'category'
ORDER BY $wpdb->term_taxonomy.taxonomy = 'topics' DESC

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):

<tr>
    <td>term_taxonomy_id</td>
    <td>term_id</td>
    <td>taxonomy</td>
    <td>description</td>
    <td>parent</td>
    <td>count</td>
</tr>
<tr>
    <td>1</td>
    <td>1</td>
    <td>category</td>
    <td>This is the description for the category taxonomy</td>
    <td>0</td>
    <td>1</td>
</tr>
<tr>
    <td>1</td>
    <td>1</td>
    <td>topics</td>
    <td>This is the description for the topics taxonomy</td>
    <td>0</td>
    <td>1</td>
</tr>

(I put it in tabular notation to express it easier)

try throwing the topics into the select query, then sorting by it:

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

Answer 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.

<?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();

        } ?>