Help w/query to order by custom field subset

  • Resolved nfb02001

    (@nfb02001)


    16 years, 4 months ago

    I’m working on a new website for my labgroup at work (https://www.orcatech.org/wordpress for now) and am having some trouble sorting a subset of posts by a custom field.

    What I want to happen is:
    If a study is currently active, it should show up on the front page sorted by start_date.

    Current state:
    Able to pull from the study category and get only the active studies. Unable to sort properly.

    Here’s the code I’m using:

    <h2>Current Studies</h2>
		<dl class="dp_columnlisting">

		<?php query_posts('category_name=studies&orderby=start_date');
		$current_year = date('Y');?>

	<?php while (have_posts()) : the_post(); ?>
            <?php
			$start_date=get_post_meta($post->ID, "start_date", true);
			$end_date=get_post_meta($post->ID, "end_date", true);
			?>
				<? if ($start_date <= $current_year && $end_date >= $current_year){?>
     			<!-- Display the title as a link to the post's permalink -->
				<dt><?php echo $start_date; ?> - <?php echo $end_date; ?></dt><dd><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></dd>
			<?php } ?>
		<?php endwhile;?>

Viewing 1 replies (of 1 total)
  • Thread Starter nfb02001

    (@nfb02001)

    I found a solution, so I thought I’d share. If you have a more elegant solution, I’d still love to see it.

    <h2>Current Studies</h2>
		<dl class="dp_columnlisting">

		<?php

$study_query = "
SELECT * FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta AS start_date ON(
$wpdb->posts.ID = start_date.post_id
AND start_date.meta_key = 'start_date'
)
LEFT JOIN $wpdb->postmeta AS end_date ON(
$wpdb->posts.ID = end_date.post_id
AND end_date.meta_key = 'end_date'
)
WHERE $wpdb->posts.post_status = 'publish'
ORDER BY end_date.meta_value, start_date.meta_value DESC
";

 $pageposts = $wpdb->get_results($study_query, OBJECT);

?>
 <?php if ($pageposts): ?>
  <?php foreach ($pageposts as $post): ?>
    <?php setup_postdata($post); ?>
            <?php
			$current_year = date('Y');
			$start_date=get_post_meta($post->ID, "start_date", true);
			$end_date=get_post_meta($post->ID, "end_date", true);
			?>
				<?php
					if ( $start_date <= $current_year && $end_date >= $current_year){?>
     			<!-- Display the title as a link to the post's permalink -->
					<dt><?php echo $start_date; ?> - <?php echo $end_date; ?></dt><dd><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></dd>
			<?php } ?>

  <?php endforeach; ?>

  <?php else : ?>
    <h2 class="center">Not Found</h2>
    <p class="center">Sorry, but you are looking for something that isn't here.</p>
    <?php include (TEMPLATEPATH . "/searchform.php"); ?>
 <?php endif; ?>

Viewing 1 replies (of 1 total)
  • The topic ‘Help w/query to order by custom field subset’ is closed to new replies.