Custom SQL query ORDER BY term_order

Question

I have custom taxonomy that list terms under "series" that contain posts that are also present in terms under "media_type" and where media_type is term_id 16.

I want to order the list by the term_order column in the terms table but it is not working. What am I doing wrong?

Thanks!

    $q_result = $wpdb->get_col("SELECT DISTINCT {$wpdb->terms}.name FROM {$wpdb->terms}
            INNER JOIN {$wpdb->term_taxonomy} ON {$wpdb->term_taxonomy}.term_id = {$wpdb->terms}.term_id
            INNER JOIN {$wpdb->term_relationships} ON {$wpdb->term_taxonomy}.term_taxonomy_id = {$wpdb->term_relationships}.term_taxonomy_id
            WHERE {$wpdb->term_taxonomy}.taxonomy = 'series' AND {$wpdb->term_relationships}.object_id IN (
            SELECT object_id FROM {$wpdb->term_relationships}
            INNER JOIN {$wpdb->term_taxonomy} ON {$wpdb->term_taxonomy}.term_taxonomy_id = {$wpdb->term_relationships}.term_taxonomy_id
            WHERE {$wpdb->term_taxonomy}.taxonomy = 'media_type' AND {$wpdb->term_taxonomy}.term_id = '16'
            ORDER BY {$wpdb->terms}.term_order
);");

Answer 1

In your case you are referring ORDER BY {$wpdb->terms}.term_order and as default in wordpress wp_terms table don't have this column the term_order column resides in the wp_term_relationships table and you are referring wp_terms table to ORDER BY this column which is not is present in that table instead of this you have to do something like this

$q_result = $wpdb->get_col("SELECT DISTINCT {$wpdb->terms}.name FROM {$wpdb->terms}
        INNER JOIN {$wpdb->term_taxonomy} ON {$wpdb->term_taxonomy}.term_id = {$wpdb->terms}.term_id
        INNER JOIN {$wpdb->term_relationships} ON {$wpdb->term_taxonomy}.term_taxonomy_id = {$wpdb->term_relationships}.term_taxonomy_id
        WHERE {$wpdb->term_taxonomy}.taxonomy = 'series' AND {$wpdb->term_relationships}.object_id IN (
        SELECT object_id FROM {$wpdb->term_relationships}
        INNER JOIN {$wpdb->term_taxonomy} ON {$wpdb->term_taxonomy}.term_taxonomy_id = {$wpdb->term_relationships}.term_taxonomy_id
        WHERE {$wpdb->term_taxonomy}.taxonomy = 'media_type' AND {$wpdb->term_taxonomy}.term_id = '16'
        ORDER BY {$wpdb->term_relationships}.term_order ASC
)");