WordPress.org

1. skarck
   Member
   Posted 1 year ago #

   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?

2. MichaelH
   Volunteer
   Posted 1 year ago #

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

3. skarck
   Member
   Posted 1 year ago #

   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!

4. MichaelH
   Volunteer
   Posted 1 year ago #

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

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

5. skarck
   Member
   Posted 1 year ago #

   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...

6. MichaelH
   Volunteer
   Posted 1 year ago #

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

7. skarck
   Member
   Posted 1 year ago #

   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!

8. MichaelH
   Volunteer
   Posted 1 year ago #

   Please look closely at the example using get_col.

9. skarck
   Member
   Posted 1 year ago #

   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!

Topic Closed

This topic has been closed to new replies.