3

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?

2 Answers 2

2

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
5
  • 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 King
    Oct 9, 2012 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'
    – Jonathan
    Oct 9, 2012 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 King
    Oct 10, 2012 at 8:55
  • Possibly. You could also try switching the last two AND's to OR's. Just a thought
    – Jonathan
    Oct 11, 2012 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 King
    Oct 11, 2012 at 18:37
0

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

        } ?>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.