wp_query to find posts by year and month

Question

I need to create sorting by year and month of custom post type in taxonomy archive page.

Like this: Here is shown year, if post exists in that year. And Highlighted month if post exists in that month.

How to find out which year and which months of which year has posts? It would be "bad" to create loop to check each month of each year if there's a posts. Any solutions?

P.S. cpt name - document_base and taxonomy name - document_base_categories and date stored in field docs_pubdate (custom field suite plugin)

Answer 1

Solved by creating custom sql queries.

Request for years:

SELECT 
    count(*),
    $wpdb->posts.id as post_id_ts,
    YEAR(
        (SELECT 
            $wpdb->postmeta.meta_value 
        FROM 
            $wpdb->postmeta 
        WHERE 
            $wpdb->postmeta.post_id = post_id_ts 
        AND 
            $wpdb->postmeta.meta_key = 'docs_pubdate')) 
    AS 
        years
FROM 
    $wpdb->posts
WHERE 
    $wpdb->posts.post_type = 'document_base'
AND 
    $wpdb->posts.post_status = 'publish'
GROUP BY 
    years

which will return result like this:

Array ( 
    [0] => Array ( 
        [count(*)] => 1 
        [post_id_ts] => 1126 
        [years] => 2017 
    ) 
    [1] => Array ( 
        [count(*)] => 3 
        [post_id_ts] => 1121 
        [years] => 2019 
    ) 
) 

Request for months:

SELECT 
    count(*),
    $wpdb->posts.id as post_id_ts,
    MONTH(
        (SELECT 
            $wpdb->postmeta.meta_value 
        FROM 
            $wpdb->postmeta 
        WHERE 
            $wpdb->postmeta.post_id = post_id_ts 
        AND 
            $wpdb->postmeta.meta_key = 'docs_pubdate')) 
    AS 
        months
FROM 
    $wpdb->posts
WHERE 
    $wpdb->posts.post_type = 'document_base'
AND 
    $wpdb->posts.post_status = 'publish'
GROUP BY 
    months

And return is:

Array ( 
    [0] => Array ( 
        [count(*)] => 2 
        [post_id_ts] => 1121 
        [month] => 1 
    ) 
    [1] => Array ( 
        [count(*)] => 1 
        [post_id_ts] => 1122 
        [month] => 2 
    ) 
    [2] => Array ( 
        [count(*)] => 1 
        [post_id_ts] => 1126 
        [month] => 6 
    ) 
)