wp query - wp_query to find posts by year and month|Programmer puzzle solving

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)

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)

Share Improve this question asked Jan 29, 2019 at 15:21 Daler 911 silver badge10 bronze badges

Add a comment |

1 Answer 1

Sorted by: Reset to default 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 
    ) 
)

Programmer puzzle solving

wp query - wp_query to find posts by year and month

1 Answer 1

Articles related to this article

comment list (0)