I’m writing a plugin that makes searches work only on a particular custom post type and it sorts the results according to the taxonomies the user selects in its backend configuration.
The admin can select up to 4 taxonomies of the custom post type: each one is then a sort criterion that must be applied to the search results. The admin can then select the terms of each taxonomy that make the post appear before others in search results. The first selected taxonomy will be the most important sort criterion; the others will be applied in order.
I need a query along the lines of:
SELECT * FROM wp_posts ORDER BY choosen_terms_of_taxonomy1, choosen_terms_of_axonomy2, ...
Now let’s use some sample data. We have the course post type:
+----+------------+
| ID | post_title |
+----+------------+
| 12 | Cooking |
+----+------------+
| 13 | Surfing |
+----+------------+
| 14 | Building |
+----+------------+
| 15 | Hacking |
+----+------------+
Then we have two taxonomies for this custom post type. One is place and the other is pricetag. The place taxonomy has the following terms:
+---------+------------+
| term_id | slug |
+---------+------------+
| 21 | downtown |
+---------+------------+
| 22 | abroad |
+---------+------------+
The pricetag taxonomy has the following terms:
+---------+------------+
| term_id | slug |
+---------+------------+
| 31 | expensive |
+---------+------------+
| 32 | cheap |
+---------+------------+
And finally we have wp_term_relationships that links the courses to the taxonomies terms this way:
+-----------+------------+---------+
| object_id | post_title | term_id |
+-----------+------------+---------+
| 12 | Cooking | 21 | (downtown)
+-----------+------------+---------+
| 12 | Cooking | 32 | (cheap)
+-----------+------------+---------+
| 13 | Surfing | 22 | (abroad)
+-----------+------------+---------+
| 13 | Surfing | 31 | (expensive)
+-----------+------------+---------+
| 14 | Building | 21 | (downtown)
+-----------+------------+---------+
| 14 | Building | 31 | (expensive)
+-----------+------------+---------+
| 15 | Hacking | 22 | (abroad)
+-----------+------------+---------+
| 15 | Hacking | 32 | (cheap)
+-----------+------------+---------+
(Please note: I do know that’s not the real structure of the wp_term_relationships table, but I wanted to simplify it a bit for the sake of this question.)
Let’s assume the admin has chosen place as the first taxonomy to use as a sort criterion and that he has chosen to show downtown courses first (the admin screen of the plugin is already done and it already provides the admin with the UI to make such choices).
Then say the admin has chosen pricetag as second taxonomy to use as sort criterion and that he wants expensive courses to show up first. Note that being a second criterion, it has less sorting priority than the first, so the admin wants downtown courses first and then, in the group of downtown courses, expensive courses first.
Now a frontend user searches for all courses on the website, and he should see these results in this exact order:
- Building course (because it’s downtown and expensive)
- Cooking course (because it’s downtown and cheap)
- Surfing course (because it’s abroad and expensive)
- Hacking course (because it’s abroad and cheap)
My problem is writing the correct JOIN and ORDER BY clauses in the WordPress query object. I’ve already hooked posts_clauses and here is the SQL query I’m generating:
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 (wp_terms.slug LIKE 'downtown' AND wp_term_taxonomy.taxonomy LIKE 'place') DESC,
(wp_terms.slug LIKE 'abroad' AND wp_term_taxonomy.taxonomy LIKE 'place') DESC,
(wp_terms.slug LIKE 'expensive' AND wp_term_taxonomy.taxonomy LIKE 'pricetag') DESC,
(wp_terms.slug LIKE 'cheap' AND wp_term_taxonomy.taxonomy LIKE 'pricetag') DESC,
wp_posts.post_title LIKE '%%' DESC, wp_posts.post_date DESC
LIMIT 0, 300
However this query has at least two problems:
- it returns twice the results and I don’t understand why, because LEFT JOIN should not produce a Cartesian product between the specified tables
- the resulting sort order is unclear to me (it seems to be just
post_date DESC), but it’s clear it is NOT what I’m expecting.
I’ve tried simplifying the query by removing WordPress generated clauses:
SELECT 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_type="course"
ORDER BY (wp_terms.slug LIKE 'downtown' AND wp_term_taxonomy.taxonomy LIKE 'place') DESC,
(wp_terms.slug LIKE 'abroad' AND wp_term_taxonomy.taxonomy LIKE 'place') DESC,
(wp_terms.slug LIKE 'expensive' AND wp_term_taxonomy.taxonomy LIKE 'pricetag') DESC,
(wp_terms.slug LIKE 'cheap' AND wp_term_taxonomy.taxonomy LIKE 'pricetag') DESC
This one has exactly the same problems, but it is a bit easier to understand, and it returns the data as if no ORDER BY was there at all.
Can you help me please?
2 s
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;
}
}