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.

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.

• query
• query-posts
• sql

• 3 Could you not use the publish date for the meeting? That way you can use WP_Query and avoid SQL entirely – Tom J Nowell ♦ Commented Mar 12, 2019 at 17:27
• 1 May I ask, do you definitely want raw SQL? (i.e. for queries outside of WP). Otherwise all of this can (and should) be done using WP_Query and hooks. – TheDeadMedic Commented Mar 12, 2019 at 17:27
• @TomJNowell Yes, it must by done with SQL. The date I am referring to is populated from a custom field and is not the same as the publish date. I do not want to get all the fields either, I just want the unique years. If you want context, this is just for generating pagination buttons by years rather than pages, so really I'm just trying to find all years that have at least one meeting. If you know how to do that with WP_Query I'm all ears. – chrispytoes Commented Mar 12, 2019 at 17:32
• @TheDeadMedic check edit – chrispytoes Commented Mar 12, 2019 at 17:40
• 1 WP_Query can search by meta values, so if your custom field holds the date and it's not just stored as part of the title then you should be able to avoid a raw SQL query. – mrben522 Commented Mar 12, 2019 at 17:44

1 Answer 1

If you want to be able to get the category by name, this should work:

SELECT DISTINCT substr(post_title, - instr(reverse(post_title), '/') + 1)
AS year FROM {$wpdb->posts} p
LEFT JOIN {$wpdb->term_relationships} rel ON rel.object_id = p.ID
LEFT JOIN {$wpdb->term_taxonomy} tax ON tax.term_taxonomy_id = rel.term_taxonomy_id
LEFT JOIN {$wpdb->terms} t ON t.term_id = tax.term_id
WHERE post_status = 'publish'
AND post_type = 'meeting'
AND t.name = 'Category Name'
AND tax.taxonomy = 'category'

If you know the term_taxonomy_id of your category (mostly, but not always, the term_id), you can accomplish this with fewer left joins like this:

SELECT DISTINCT substr(post_title, - instr(reverse(post_title), '/') + 1)
AS year FROM {$wpdb->posts} p
LEFT JOIN {$wpdb->term_relationships} rel ON rel.object_id = p.ID
WHERE post_status = 'publish'
AND post_type = 'meeting'
AND rel.term_taxonomy_id = <term_taxonomy_id of your category>

Happy Coding!