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

by

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

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

Leave a Comment