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 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;
}
}