Say I have a number of posts, across several post types. Each of these posts are members of several taxonomies. My queries will be AND across taxonomies, but OR within a taxonomy. I would then like to order the results by the total number of matched terms.

For a (fictional) example, say I have post types of Books, DVDs and CDs. All of them may have a Genre taxonomy, a Topics taxonomy, and an Age Range taxonomy. Any one item may be in multiple genres and will likely have multiple topics and age ranges. A user selects the Genre of "thriller", the Topics of "boat" and "horse", and the Age Ranges of "5-14" and "21-40".

We would return the items which:

  • Have the Genre of "thriller"
  • AND
    • Have the Topic of "boat" and the Topic of "horse" OR
    • Have the Topic of "boat" OR
    • Have the Topic of "horse"
  • AND
    • Have the Age Range of "5-14" and the Age Range of "21-40" OR
    • Have the Age Range of "5-14" OR
    • Have the Age Range of "21-40"

We'd get back the following items:

Pony at Sea 3

  • Topic: "boat", "horse"
  • Age Range: "5-14", "14-21", "21-40"
  • Genre: "Thriller", "Equestrian"

Murder At Sea

  • Topic: "boat", "crime"
  • Age Range: "21-40"
  • Genre: "Thriller"

Neigh-bourhood Dogs

  • Topic: "horse", "dogs"
  • Age Range: "5-14"
  • Genre: "Thriller"

When ordering those items, we would like to rank the items which match more terms higher. So "Pony at Sea 3" ranks higher than "Murder at Sea" and "Neigh-bourhood Dogs".

Constructing the query is no problem, that's easy enough to do.

But there's no straightforward way to reorder the items. The simplest solution I can come up with would be to query for all items, get the relevant terms per post, do the relatively simple sorting logic, and then return the appropriate slice of data. However, this is massively inefficient, requiring a query per taxonomy, per post. So in this example, 100 results will cause 300+ queries.

  • 1
    You could use a subquery in the ORDER BY clause, e.g. ORDER BY ( SELECT COUNT(*) FROM wp_term_relationships mytr INNER JOIN wp_term_taxonomy mytt ON mytt.term_taxonomy_id = mytr.term_taxonomy_id WHERE mytr.object_id = wp_posts.ID AND mytt.taxonomy IN ('genre', 'topic', 'age_range') ) DESC, but it's not a performant solution, either. So you might want to use a custom table for storing the post's terms count and then join the table and use it in the ORDER BY clause.
    – Sally CJ
    Aug 23 at 13:40
  • 1
    Yeah, I think you're right. A custom table would be the slickest way to go here. In this instance we actually decided on changing the requirements to something that suited the project better anyway, so I won't be able to share the end result unfortunately. But I might circle back and try implementing this one day. Aug 23 at 18:25

Your Answer

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

Browse other questions tagged or ask your own question.