1

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

Like this: filter 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)

3

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 
    ) 
) 

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.