Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.
    Bounty Ended with 50 reputation awarded by Lucio Crusca
2 Add unit test and fixed example that actually works
source | link
CASE 
    WHEN (wp_terms.slug LIKE 'downtown' AND wp_term_taxonomy.taxonomy LIKE 'place') THEN 01
    WHEN (wp_terms.slug LIKE 'abroad' AND wp_term_taxonomy.taxonomy LIKE 'place') THEN 1
    WHEN (wp_terms.slug LIKE 'expensive' AND wp_term_taxonomy.taxonomy LIKE 'pricetag') THEN 2
    WHEN (wp_terms.slug LIKE 'cheap' AND wp_term_taxonomy.taxonomy LIKE 'pricetag') THEN 30
END

This will order based on the priority that you assign to each of the terms (01, 10, etc., higher being higher priority, unless you use ASC instead of DESC for ordering).

Because you want to order these two taxonomies independently, you will need to have two joins, and two case statements. (See below for example.)

You also need to cause a GROUP BY on the post ID, to avoid duplicate results:

    $clauses['groupby'] = 'wptests_posts.ID';

So your final query would end up looking something like this (formatted for easier reading):

   SELECT SQL_CALC_FOUND_ROWS  wp_postswptests_posts.*ID FROM wp_postswptests_posts 
            LEFT JOIN (wp_term_relationships
                wptests_term_relationships tr_place, 
 wp_term_taxonomy               wptests_term_taxonomy tt_place, 
 wp_terms               wptests_terms t_place
            ) ON (
                tr_place.object_id = wptests_posts.ID 
                AND tt_place.term_taxonomy_id = tr_place.term_taxonomy_id
                AND tt_place.taxonomy = 'place'
                AND t_place.term_id = tt_place.term_id
            ) 

            LEFT JOIN (
                wptests_term_relationships tr_pricetag,
                wptests_term_taxonomy tt_pricetag,
                wptests_terms t_pricetag
            ) ON (wp_term_relationships
                tr_pricetag.object_id = wp_postswptests_posts.ID 
                AND wp_term_taxonomytt_pricetag.term_taxonomy_id = wp_term_relationshipstr_pricetag.term_taxonomy_id
                AND wp_termstt_pricetag.taxonomy = 'pricetag'
                AND t_pricetag.term_id = wp_term_taxonomytt_pricetag.term_id
            ) 
   WHERE 1=1  AND wptests_posts.post_type = 'course' AND (wptests_posts.post_status = 'publish')
   GROUP BY wptests_posts.ID
   ORDER BY 
        (CASE 
            WHEN (wp_postst_place.post_titleslug LIKE '%%''downtown') ORTHEN 1
            WHEN (wp_postst_place.post_excerptslug LIKE '%%''abroad') ORTHEN 0
        END) DESC, (wp_postsCASE
            WHEN (t_pricetag.post_contentslug LIKE '%%''expensive') THEN 1
            WHEN (t_pricetag.slug LIKE 'cheap') THEN 0
        END) DESC,
        wptests_posts.post_date DESC
   ANDLIMIT wp_posts0, 10

Here is an example PHPUnit test that demonstrates that this works, including example code for generating the joins and orderbys (it was used to generate the above query):

class My_Test extends WP_UnitTestCase {

    public function test() {

        // Create the post type.post_type 
 IN       register_post_type( 'course' );

        // Create the posts.
        $cooking_post_id = $this->factory->post->create(
            array('post' 'post_title' => 'Cooking', 'page''post_type' => 'course' )
        );
        $surfing_post_id = $this->factory->post->create(
            array( 'post_title' => 'Surfing', 'attachment''post_type' => 'course' )
        );
        $building_post_id = $this->factory->post->create(
            array( 'post_title' => 'Building', 'post_type' => 'course' )
        );
        $hacking_post_id = $this->factory->post->create(
  AND          array(wp_posts 'post_title' => 'Hacking', 'post_type' => 'course' )
        );

        // Create the taxonomies.post_status 
 = 'publish' OR wp_posts    register_taxonomy( 'place', 'course' );
        register_taxonomy( 'pricetag', 'course' );

        // Create the terms.post_author
        $downtown_term_id = 1wp_create_term( AND'downtown', wp_posts.post_status'place' );
        $abroad_term_id = 'private'wp_create_term( 'abroad', 'place' );

        $expensive_term_id = wp_create_term( 'expensive', 'pricetag' );
        $cheap_term_id = wp_create_term( 'cheap', 'pricetag' );

        // Give the terms to the ANDcorrect wp_postsposts.post_type='course'
        wp_add_object_terms( $cooking_post_id, $downtown_term_id, 'place' );
  ORDER BY     wp_add_object_terms( $cooking_post_id, $cheap_term_id, 'pricetag' );

        wp_add_object_terms( $surfing_post_id, $abroad_term_id, 'place' );
        wp_add_object_terms( $surfing_post_id, $expensive_term_id, 'pricetag' );

        wp_add_object_terms( $building_post_id, $downtown_term_id, 'place' );
        wp_add_object_terms( $building_post_id, $expensive_term_id, 'pricetag' );

        wp_add_object_terms( $hacking_post_id, $abroad_term_id, 'place' );
        wp_add_object_terms( $hacking_post_id, $cheap_term_id, 'pricetag' );

        $query = new WP_Query(
            array(
                'fields'    => 'ids',
                'post_type' => 'course',
            )
        );

        add_filter( 'posts_clauses', array( $this, 'filter_post_clauses' ) );

        $results = $query->get_posts();

        $this->assertSame(
            array(
                $building_post_id,
                $cooking_post_id,
                $surfing_post_id,
                $hacking_post_id,
            )
            , $results
        );
    }

    public function filter_post_clauses( $clauses ) {

        global $wpdb;

        $clauses['orderby'] = "
            (CASE 
                WHEN (wp_termst_place.slug LIKE 'downtown') ANDTHEN wp_term_taxonomy1
                WHEN (t_place.taxonomyslug LIKE 'place''abroad') THEN 0
          WHEN  END) DESC, (wp_terms.slugCASE
 LIKE 'abroad' AND wp_term_taxonomy            WHEN (t_pricetag.taxonomyslug LIKE 'place''expensive') THEN 1
                WHEN (wp_termst_pricetag.slug LIKE 'expensive''cheap') ANDTHEN wp_term_taxonomy0
            END) DESC,
            " .taxonomy LIKE$clauses['orderby'];

        foreach ( array( 'place', 'pricetag' ) THENas 2$taxonomy ) {

          WHEN  // Instead of interpolating directly here, you should use $wpdb->prepare(wp_terms) for $taxonomy.slug 
 LIKE 'cheap'          $clauses['join'] .= "
                LEFT JOIN (
                    $wpdb->term_relationships tr_$taxonomy,
                    $wpdb->term_taxonomy tt_$taxonomy,
                    $wpdb->terms t_$taxonomy
                ) ON (
                    tr_$taxonomy.object_id = $wpdb->posts.ID 
                    AND wp_term_taxonomytt_$taxonomy.term_taxonomy_id = tr_$taxonomy.term_taxonomy_id
                    AND tt_$taxonomy.taxonomy LIKE= 'pricetag')'$taxonomy'
 THEN 3                  AND t_$taxonomy.term_id = tt_$taxonomy.term_id
        END        ) DESC,
           wp_posts.post_title LIKE '%%' DESC, wp_posts ";
        }

        $clauses['groupby'] = 'wptests_posts.post_dateID';

 DESC       return $clauses;
 LIMIT 0, 300 }
}
CASE 
    WHEN (wp_terms.slug LIKE 'downtown' AND wp_term_taxonomy.taxonomy LIKE 'place') THEN 0
    WHEN (wp_terms.slug LIKE 'abroad' AND wp_term_taxonomy.taxonomy LIKE 'place') THEN 1
    WHEN (wp_terms.slug LIKE 'expensive' AND wp_term_taxonomy.taxonomy LIKE 'pricetag') THEN 2
    WHEN (wp_terms.slug LIKE 'cheap' AND wp_term_taxonomy.taxonomy LIKE 'pricetag') THEN 3
END

This will order based on the priority that you assign to each of the terms (0, 1, etc.).

So your final query would end up looking something like this:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts 
  LEFT JOIN (wp_term_relationships, wp_term_taxonomy, wp_terms) 
    ON (wp_term_relationships.object_id = wp_posts.ID 
        AND wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
        AND wp_terms.term_id = wp_term_taxonomy.term_id) 
  WHERE 1=1 AND (((wp_posts.post_title LIKE '%%') OR (wp_posts.post_excerpt LIKE '%%') OR (wp_posts.post_content LIKE '%%')))
            AND wp_posts.post_type IN 
                ('post', 'page', 'attachment', 'course') 
            AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 1 AND wp_posts.post_status = 'private') 
            AND wp_posts.post_type='course' 
  ORDER BY     
        (CASE 
          WHEN (wp_terms.slug LIKE 'downtown' AND wp_term_taxonomy.taxonomy LIKE 'place') THEN 0
          WHEN (wp_terms.slug LIKE 'abroad' AND wp_term_taxonomy.taxonomy LIKE 'place') THEN 1
          WHEN (wp_terms.slug LIKE 'expensive' AND wp_term_taxonomy.taxonomy LIKE 'pricetag') THEN 2
          WHEN (wp_terms.slug LIKE 'cheap' AND wp_term_taxonomy.taxonomy LIKE 'pricetag') THEN 3
        END) DESC,
           wp_posts.post_title LIKE '%%' DESC, wp_posts.post_date DESC
 LIMIT 0, 300
CASE 
    WHEN (wp_terms.slug LIKE 'downtown' AND wp_term_taxonomy.taxonomy LIKE 'place') THEN 1
    WHEN (wp_terms.slug LIKE 'abroad' AND wp_term_taxonomy.taxonomy LIKE 'place') THEN 0
END

This will order based on the priority that you assign to each of the terms (1, 0, etc., higher being higher priority, unless you use ASC instead of DESC for ordering).

Because you want to order these two taxonomies independently, you will need to have two joins, and two case statements. (See below for example.)

You also need to cause a GROUP BY on the post ID, to avoid duplicate results:

    $clauses['groupby'] = 'wptests_posts.ID';

So your final query would end up looking something like this (formatted for easier reading):

   SELECT SQL_CALC_FOUND_ROWS  wptests_posts.ID FROM wptests_posts 
            LEFT JOIN (
                wptests_term_relationships tr_place, 
                wptests_term_taxonomy tt_place, 
                wptests_terms t_place
            ) ON (
                tr_place.object_id = wptests_posts.ID 
                AND tt_place.term_taxonomy_id = tr_place.term_taxonomy_id
                AND tt_place.taxonomy = 'place'
                AND t_place.term_id = tt_place.term_id
            ) 

            LEFT JOIN (
                wptests_term_relationships tr_pricetag,
                wptests_term_taxonomy tt_pricetag,
                wptests_terms t_pricetag
            ) ON (
                tr_pricetag.object_id = wptests_posts.ID 
                AND tt_pricetag.term_taxonomy_id = tr_pricetag.term_taxonomy_id
                AND tt_pricetag.taxonomy = 'pricetag'
                AND t_pricetag.term_id = tt_pricetag.term_id
            ) 
   WHERE 1=1  AND wptests_posts.post_type = 'course' AND (wptests_posts.post_status = 'publish')
   GROUP BY wptests_posts.ID
   ORDER BY 
        (CASE 
            WHEN (t_place.slug LIKE 'downtown') THEN 1
            WHEN (t_place.slug LIKE 'abroad') THEN 0
        END) DESC, (CASE
            WHEN (t_pricetag.slug LIKE 'expensive') THEN 1
            WHEN (t_pricetag.slug LIKE 'cheap') THEN 0
        END) DESC,
        wptests_posts.post_date DESC
   LIMIT 0, 10

Here is an example PHPUnit test that demonstrates that this works, including example code for generating the joins and orderbys (it was used to generate the above query):

class My_Test extends WP_UnitTestCase {

    public function test() {

        // Create the post type. 
        register_post_type( 'course' );

        // Create the posts.
        $cooking_post_id = $this->factory->post->create(
            array( 'post_title' => 'Cooking', 'post_type' => 'course' )
        );
        $surfing_post_id = $this->factory->post->create(
            array( 'post_title' => 'Surfing', 'post_type' => 'course' )
        );
        $building_post_id = $this->factory->post->create(
            array( 'post_title' => 'Building', 'post_type' => 'course' )
        );
        $hacking_post_id = $this->factory->post->create(
            array( 'post_title' => 'Hacking', 'post_type' => 'course' )
        );

        // Create the taxonomies. 
        register_taxonomy( 'place', 'course' );
        register_taxonomy( 'pricetag', 'course' );

        // Create the terms.
        $downtown_term_id = wp_create_term( 'downtown', 'place' );
        $abroad_term_id = wp_create_term( 'abroad', 'place' );

        $expensive_term_id = wp_create_term( 'expensive', 'pricetag' );
        $cheap_term_id = wp_create_term( 'cheap', 'pricetag' );

        // Give the terms to the correct posts.
        wp_add_object_terms( $cooking_post_id, $downtown_term_id, 'place' );
        wp_add_object_terms( $cooking_post_id, $cheap_term_id, 'pricetag' );

        wp_add_object_terms( $surfing_post_id, $abroad_term_id, 'place' );
        wp_add_object_terms( $surfing_post_id, $expensive_term_id, 'pricetag' );

        wp_add_object_terms( $building_post_id, $downtown_term_id, 'place' );
        wp_add_object_terms( $building_post_id, $expensive_term_id, 'pricetag' );

        wp_add_object_terms( $hacking_post_id, $abroad_term_id, 'place' );
        wp_add_object_terms( $hacking_post_id, $cheap_term_id, 'pricetag' );

        $query = new WP_Query(
            array(
                'fields'    => 'ids',
                'post_type' => 'course',
            )
        );

        add_filter( 'posts_clauses', array( $this, 'filter_post_clauses' ) );

        $results = $query->get_posts();

        $this->assertSame(
            array(
                $building_post_id,
                $cooking_post_id,
                $surfing_post_id,
                $hacking_post_id,
            )
            , $results
        );
    }

    public function filter_post_clauses( $clauses ) {

        global $wpdb;

        $clauses['orderby'] = "
            (CASE 
                WHEN (t_place.slug LIKE 'downtown') THEN 1
                WHEN (t_place.slug LIKE 'abroad') THEN 0
            END) DESC, (CASE
                WHEN (t_pricetag.slug LIKE 'expensive') THEN 1
                WHEN (t_pricetag.slug LIKE 'cheap') THEN 0
            END) DESC,
            " . $clauses['orderby'];

        foreach ( array( 'place', 'pricetag' ) as $taxonomy ) {

            // Instead of interpolating directly here, you should use $wpdb->prepare() for $taxonomy. 
            $clauses['join'] .= "
                LEFT JOIN (
                    $wpdb->term_relationships tr_$taxonomy,
                    $wpdb->term_taxonomy tt_$taxonomy,
                    $wpdb->terms t_$taxonomy
                ) ON (
                    tr_$taxonomy.object_id = $wpdb->posts.ID 
                    AND tt_$taxonomy.term_taxonomy_id = tr_$taxonomy.term_taxonomy_id
                    AND tt_$taxonomy.taxonomy = '$taxonomy'
                    AND t_$taxonomy.term_id = tt_$taxonomy.term_id
                ) 
                ";
        }

        $clauses['groupby'] = 'wptests_posts.ID';

        return $clauses;
    }
}
1
source | link

Unfortunately, although WP_Query supports the 'tax_query' arg, it does not support ordering based on post terms. So you will need to modify the query SQL, as you are doing now. However, you are constructing the ORDER BY clause incorrectly, and that is why it is ordering by post_date. What you need to do is use a CASE statement, like this:

CASE 
    WHEN (wp_terms.slug LIKE 'downtown' AND wp_term_taxonomy.taxonomy LIKE 'place') THEN 0
    WHEN (wp_terms.slug LIKE 'abroad' AND wp_term_taxonomy.taxonomy LIKE 'place') THEN 1
    WHEN (wp_terms.slug LIKE 'expensive' AND wp_term_taxonomy.taxonomy LIKE 'pricetag') THEN 2
    WHEN (wp_terms.slug LIKE 'cheap' AND wp_term_taxonomy.taxonomy LIKE 'pricetag') THEN 3
END

This will order based on the priority that you assign to each of the terms (0, 1, etc.).

So your final query would end up looking something like this:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts 
  LEFT JOIN (wp_term_relationships, wp_term_taxonomy, wp_terms) 
    ON (wp_term_relationships.object_id = wp_posts.ID 
        AND wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
        AND wp_terms.term_id = wp_term_taxonomy.term_id) 
  WHERE 1=1 AND (((wp_posts.post_title LIKE '%%') OR (wp_posts.post_excerpt LIKE '%%') OR (wp_posts.post_content LIKE '%%')))
            AND wp_posts.post_type IN 
                ('post', 'page', 'attachment', 'course') 
            AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 1 AND wp_posts.post_status = 'private') 
            AND wp_posts.post_type='course' 
  ORDER BY     
        (CASE 
          WHEN (wp_terms.slug LIKE 'downtown' AND wp_term_taxonomy.taxonomy LIKE 'place') THEN 0
          WHEN (wp_terms.slug LIKE 'abroad' AND wp_term_taxonomy.taxonomy LIKE 'place') THEN 1
          WHEN (wp_terms.slug LIKE 'expensive' AND wp_term_taxonomy.taxonomy LIKE 'pricetag') THEN 2
          WHEN (wp_terms.slug LIKE 'cheap' AND wp_term_taxonomy.taxonomy LIKE 'pricetag') THEN 3
        END) DESC,
           wp_posts.post_title LIKE '%%' DESC, wp_posts.post_date DESC
 LIMIT 0, 300