Get posts by category with pure SQL query

Question

I have a meeting post type, whose titles automatically get set to a date of the form m/d/Y. I have the below query which gets all unique years from the post titles by selecting distinct values after the last / inside the post title.

SELECT DISTINCT substr(post_title, - instr(reverse(post_title), '/') + 1)
AS year FROM {$wpdb->posts}
WHERE post_status = 'publish'
AND post_type = 'meeting'

This outputs an array looking something like ["2019", "2018", "2017"].

What I want is to refine this down further to be able to consider posts of only a certain category.

Now I know the below example is wrong, since categories are not actually stored on the posts themselves, but basically I want to refine by post category like this:

SELECT DISTINCT substr(post_title, - instr(reverse(post_title), '/') + 1)
AS year FROM {$wpdb->posts}
WHERE post_status = 'publish'
AND post_type = 'meeting'
AND post_category = 'some_cat_slug'

This query is about the extent of my SQL knowledge so I’m not sure how to do the join in order to filter by category.

Edit: To clarify, this is just for generating pagination buttons by year. So in short, I am trying to find all years that have at least one meeting post for a specific category. If this can be done with WP_Query only, I’d be happy to hear it.

Edit 2: I’ve solved my issue by storing the post categories in the title as well. I’ll leave this here if anyone else wants to answer though.

0
, , chrispytoes 3 months 0 Answers 58 views 0

Leave an answer