Support » Plugins » Hacks » grabbing posts by meta the advanced way

  • Resolved skarck

    (@skarck)


    Hi there!

    I’m currently writing a plugin for handling posts as events which stores a timestamp in metakey called e.g. ‘time’.
    While an event can have multiple occurences there can be several values with the same meta_key per post.
    Now I would like to query something like get all meta by key 'time', sorted by the value and grab all posts by the post-ids of the metas for keeping the posts still sotrted by date and allowing the posts to appear more than one time (because of the several occurences).

    the meta-query is easy but than querying the posts one by one by their ID seems to be quite inefficient, doesn’t it??

    any ideas?

Viewing 8 replies - 1 through 8 (of 8 total)
  • MichaelH

    (@michaelh)

    You are saying “sorted by value” and “keeping the posts still sotrted by date”. Could you clear that up please.

    skarck

    (@skarck)

    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!

    MichaelH

    (@michaelh)

    Not sure but have you tried the orderby=meta_value argument with query_posts?

    http://codex.wordpress.org/Template_Tags/query_posts#Order_Parameters

    skarck

    (@skarck)

    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…

    MichaelH

    (@michaelh)

    Well then you might have to resort to using wpdb and the example that is here http://codex.wordpress.org/wpdb#SELECT_a_Column

    skarck

    (@skarck)

    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!

    MichaelH

    (@michaelh)

    Please look closely at the example using get_col.

    skarck

    (@skarck)

    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!

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘grabbing posts by meta the advanced way’ is closed to new replies.