grabbing posts by meta the advanced way

Hey!

Sorry for being unclear. By “sorted by date” i mean sorted by the meta value which is in my case an event date.

so lets say there is a post named “Event One” and it has three meta key/value pairs all of the key “eventdate” and a timestamp as value.
i want to list all posts with this meta key sorted by their meta value and let this specific posts appear three times in the list.

thx in advance!

using this method, the sample post i described above is only shown once in the post list. it should appear as often as it has the meta key/value pair.
think this should combine two queries…

Okay, i had a look at your exmaples but i’m not sure if this helps in terms of effiency.
my latest idea is to use wpdb->get_result to get the list of meta key/value pairs ordered by the meta_value (let’s call it result_1). in this list certain post_ids appear several times as intended. now i pull all posts with my meta_key set in an object by using query_posts() (result_2).

the last thing i’ve to do is go through the result_1 and get foreach post_id the post_content, title etc from my object result_2.
would need only 2 queries to do it.

But how can i get a certain post out of a objects of posts (result_1) by its ID??
looking for something like

$allposts = query_posts(some arguments);
foreach(...id from result_1..) {
$my_post =get_post_out_of_result($allposts, ID);
}

thx in advance!

This is how i got it work:

<?php

$today = mktime(0,0,0,date("m"),date("d"),date("Y"));
$query_str =
"
FROM        $wpdb->postmeta
INNER JOIN  $wpdb->posts
            on $wpdb->postmeta.post_id = $wpdb->posts.ID
INNER JOIN  $wpdb->term_relationships
            ON $wpdb->postmeta.post_id = $wpdb->term_relationships.object_id
WHERE       $wpdb->postmeta.meta_key = 'hme_timestamp'
            AND $wpdb->postmeta.meta_value >= $today
ORDER BY $wpdb->postmeta.meta_value
";

$postids = $wpdb->get_col($wpdb->prepare("
SELECT      post_id " . $query_str ));

$post_dates = $wpdb->get_col($wpdb->prepare("
SELECT      meta_value " . $query_str ));

if ($postids) {
  $i = 0;
  foreach ($postids as $id) {
    $post = get_post(intval($id));
    setup_postdata($post);?>
    <p><h4><?php the_title(); ?></h4>
    Date: <?php echo date("d.m.Y H:i",$post_dates[$i]); ?><br />
    more occurrences of this event:
    <ul><?php
    $all_occurrences = get_post_custom_values("hme_timestamp", $post->ID);

    foreach($all_occurrences as $occurrence) if($occurrence != $post_dates[$i]) echo "<li>" . date("d.m.Y H:i",$occurrence) . "</li>";
    ?>
    </ul>
    </p>
    <?php
    $i++;
  }
}
?>

maybe there are more efficient solutions but it does what it’s supposed to… 😉

thx michael!