6

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:

  1. Building course (because it's downtown and expensive)
  2. Cooking course (because it's downtown and cheap)
  3. Surfing course (because it's abroad and expensive)
  4. 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:

  1. it returns twice the results and I don't understand why, because LEFT JOIN should not produce a Cartesian product between the specified tables
  2. 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?

5
+50

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

You can use tax_query for sorting with taxonomy i.e.

  $args = array(
            posts_per_page => -1,
            post_type => 'your post type',
            tax_query' => array(
            'relation' => 'OR',
             array(
                  'taxonomy' => 'your custom taxonomy',
                  'field'    => 'slug',
                  'terms'    => $_REQUEST[your post requested],
                 ),
              array(
                    'taxonomy' => 'your 2nd custom taxonomy',
                    'field'    => 'slug',
                    'terms'    => $_REQUEST[your post requested],
                ),
            ),
        ):

       $query = new WP_Query( $args );
  • Could you please elaborate a bit? I don't understand how to specify the sorting by the terms, even assuming they were in the $_REQUEST array (which they are not, but that wouldn't be a big issue). Also I don't understand how this code makes WP sort the results at all. Seems to me this code just filters the results, and I do not need that. – Lucio Crusca Apr 12 '17 at 21:03
  • A tax_query should help get you started, but it won't sort the items in the order you might want it to. For that, you could write your own function that goes through all the results of the tax_query, checks which taxonomies are set, and then apply something like a 'score' to each result based on the criteria. You then order the results by the score it received. Just an idea. – Swen Apr 13 '17 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.