Get posts by year and meta key returns empty

Question

I'm building an index.php of a portfolio site, where i'd like to show posts by their year and meta_key. The meta key is used as an "featured project" option.

In the beginning I made an custom taxonomy "Year" with different project years as terms and used foreach to populate the posts next to the years.

<?php $years = get_terms('year', 'orderby=name&order=desc&hide_empty=1'); ?>

<?php foreach( $years as $year ) : ?>
<div class="front-index"><p><?php echo $year->name; ?></p></div>

    <?php
    $year_query = array( 
        'post_type' => 'works', 
        'meta_key' => 'post_h2_mask', 
        'meta_value' => '1', 
        'taxonomy' => 'year', 
        'term' => $year->slug );
    $year_posts = new WP_Query ($year_query);
    ?>

<?php while ( $year_posts->have_posts() ) : $year_posts->the_post(); ?>

…

This didn't work out, because the terms already have the value that something is taged with it and the meta value doesn't work for terms (!?) because it still outputs the year, even if the post doesn't have a meta_value.

…

So I thought that the solution might be to use the WordPress own "published" date to populate posts by year. And for this I found a mysql snippet and worked this a bit round.

<?php
$years = $wpdb->get_col("
            SELECT DISTINCT YEAR(post_date) 
            FROM $wpdb->posts 
            WHERE post_status = 'publish' 
            AND post_type = 'works' 
            ORDER BY post_date DESC");
?>

    <?php foreach( $years as $year ) : ?>
    <div class="front-index"><p><?php echo $year; ?></p></div>

        <?php
        $year_query = array( 
            'post_type' => 'works', 
            'meta_key' => 'post_h2_mask', 
            'meta_value' => '1',
            'year' => $year 
            );
        $year_posts = new WP_Query ($year_query);
        ?>

    <?php while ( $year_posts->have_posts() ) : $year_posts->the_post(); ?>
    <?php       
    $attachments = new Attachments( 'attachments' );
    if( $attachments->exist() ) :       
    ?>

    <div class="front-work">
    <div class="wraptocenter">
        <a href="<?php the_permalink() ?>" rel="bookmark">
        <?php the_post_thumbnail('front-thumbnail'); ?>
        <p><?php the_title(); ?><br /><span class="image-count">
        <?php 
        $project_cats = get_the_terms($post->ID, 'medium');
        $project_cats = array_values($project_cats);

        for($cat_count=0; $cat_count<count($project_cats); $cat_count++) {

            echo '<span>'.$project_cats[$cat_count]->name.'</span>';
            if ($cat_count<count($project_cats)-1){
                echo ', ';
            }
        }
        ?>,     
        <span><?php echo $attachments->total(); ?> images</span></span></p>
        </a>
    </div>  
    </div>  

<?php endif; endwhile; ?>
<?php endforeach ?>

This gets the years in their div, but it shows only one post with a link and that is only one from 2006!?

How to get posts next to their yearly belonging but with a meta_key?

r1987 r1987 · Accepted Answer · 2013-02-27T14:41:22

OK.

I think I got it myself this time. The problem with the first one was that:

The queries above return posts for a specific date period in history, i.e. "Posts from X year, X month, X day". They are unable to fetch posts from a timespan relative to the present, so queries like "Posts from the last 30 days" or "Posts from the last year" are not possible with a basic query, and require use of the posts_where filter to be completed. The examples below use the posts_where filter, and should be modifyable for most time-relative queries.

Via WordPress CODEX. So if you'd like to WP_query a specific year you also need a month in it.

For the MYSQL query part I used a found code from wordpress.org forum and fiddled with it. It's not may-be the best one, but works for me.

Code for getting WordPress posts by year and meta_value:

<?php
$years = $wpdb->get_col("
            SELECT DISTINCT YEAR(post_date) 
            FROM $wpdb->posts 
            LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
            WHERE wp_postmeta.meta_key = 'your_meta_key'
            AND wp_postmeta.meta_value = 'your_meta_vaue' 
            AND post_status = 'publish' 
            AND post_type = 'your_post_type' 
            ORDER BY post_date DESC");

    foreach($years as $year) :      
?>
    <p><?php echo $year ?></p>

    <?php
    $months = $wpdb->get_col("
                SELECT DISTINCT MONTH(post_date) 
                FROM $wpdb->posts 
                WHERE post_status = 'publish' 
                AND post_type = 'your_post_type' 
                AND YEAR(post_date) = '".$year."' 
                ORDER BY post_date DESC");

        foreach($months as $month) :
    ?>
        <?php
        $posts = $wpdb->get_col("
                    SELECT $wpdb->posts.*
                    FROM $wpdb->posts
                    LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
                    WHERE wp_postmeta.meta_key = 'your_meta_key'
                    AND wp_postmeta.meta_value = 'your_meta_value' 
                    AND post_status = 'publish' 
                    AND post_type = 'your_post_type' 
                    AND MONTH(post_date) = '".$month."' 
                    AND YEAR(post_date) = '".$year."' 
                    ORDER BY post_date DESC");

            foreach($posts as $post) :
        ?>

            <!-- your specific post html & php -->

        <?php endforeach;?>
    <?php endforeach;?>
<?php endforeach;?>

Get posts by year and meta key returns empty

1 Answers