WordPress.org

1. eggroup
   Member
   Posted 1 year ago #
   

   I'm using a Custom Select Query to return posts sorted by Custom Field value and it's working but it returns all my 350 posts in one page I need to add a pagination to only show 20 posts per page like the rest of my website.

   I used the exact same code shown in this Codex page :
   http://codex.wordpress.org/Displaying_Posts_Using_a_Custom_Select_Query

   <?php
    $querystr = "
       SELECT $wpdb->posts.*
       FROM $wpdb->posts, $wpdb->postmeta
       WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
       AND $wpdb->postmeta.meta_key = 'top'
       AND $wpdb->posts.post_status = 'publish'
       AND $wpdb->posts.post_type = 'post'
       AND $wpdb->posts.post_date < NOW()
       ORDER BY ABS($wpdb->postmeta.meta_value) DESC
    ";
    $pageposts = $wpdb->get_results($querystr, OBJECT);
   ?>
    <?php if ($pageposts): ?>
     <?php global $post; ?>
     <?php foreach ($pageposts as $post): ?>
       <?php setup_postdata($post); ?>
   <div id="post-<?php the_ID(); ?>" class="thumbnail" >

   PS: I have the WP-PageNavi plugin already installed.

2. Big Bagel
   Member
   Posted 1 year ago #
   

   Wouldn't it be easier to use WP_Query?

   $args = array(
       'posts_per_page' => 20,
       'order_by' => 'meta_value',
       'meta_key' => 'top',
       'paged' => get_query_var('paged')
   );
   
   $query = new WP_Query( $args );

   The query above should be equivalent to yours but with proper pagination and 20 results per page. If you do use WP_Query make sure to add wp_reset_postdata(); after your wp_pagenavi(); call as recommended by the author of that plugin.

3. eggroup
   Member
   Posted 1 year ago #
   

   That's sounds good and interesting. Sorry I'm not so experienced in WP & PHP. Should I replace my whole code with your query or what should I do?

   I really appreciate your help. Thank you!

4. Big Bagel
   Member
   Posted 1 year ago #
   

   I'm not that experienced when it comes to doing queries, (I mainly just go go through this codex page). The query I gave:

   <?php
   $args = array(
       'posts_per_page' => 20,
       'order_by' => 'meta_value',
       'meta_key' => 'top',
       'paged' => get_query_var('paged')
   );
   
   $my_query = new WP_Query( $args );
   ?>

   would essentially replace this part:

   <?php
   $querystr = "
       SELECT $wpdb->posts.*
       FROM $wpdb->posts, $wpdb->postmeta
       WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
       AND $wpdb->postmeta.meta_key = 'top'
       AND $wpdb->posts.post_status = 'publish'
       AND $wpdb->posts.post_type = 'post'
       AND $wpdb->posts.post_date < NOW()
       ORDER BY ABS($wpdb->postmeta.meta_value) DESC
    ";
    $pageposts = $wpdb->get_results($querystr, OBJECT);
   ?>

   Then to start the loop you would do something like:

   <?php
   while ( $my_query->have_posts() ) { $my_query->the_post();
   
   ...all the bits to display your posts (the_title(); the_content(); and all those sorts of things)...
   
   }
   ?>

   And then, if you're using that plugin you mentioned, you would probably call wp_reset_postata(); after using it. For example:

   wp_pagenavi( array( 'query' => $my_query ) );
   
   wp_reset_postdata();

   *I'm not familiar with the use of that plugin; I'm just going by what the author of it says here.

   If you want to post your entire query and loop into a pastebin, I could look it over for any obvious weirdness.

5. eggroup
   Member
   Posted 1 year ago #
   

   Thank you so much. You just helped me to solve a problem I've been trying to solve for over two days now. Your code did put me on the right path but there is a couple of problems I faced but I managed to solve them and get the code work probably. my meta_key was a numeric value and therefore the posts weren't sorted right and also I didn't get a pagination even after I used your code. Here is the whole code after I edited. Thanks!

   First :
   

   <?php
   $paged = get_query_var( 'paged' ) ? get_query_var( 'paged' ) : 1;
   $args = array(
       'posts_per_page' => 20,
       'orderby' => meta_value_num,
       'meta_key' => 'top',
   	'order' => 'desc',
       'paged' => $paged
   );
   $my_query = new WP_Query( $args );
   ?>
   <?php if (have_posts()) : ?>
   <?php while ( $my_query->have_posts() ) : $my_query->the_post(); ?>

   Then replace
   echo "<div id=\"pagenavi\">"; wp_pagenavi(); echo"</div>";
   With
   

   echo "<div id=\"pagenavi\">"; wp_pagenavi( array( 'query' => $my_query ) ); echo"</div>";
   wp_reset_postdata();

   This will sort all your posts desc by custom field called "top" and you will 20 posts per page.

   I can't thank you enough for putting an end for my problem and believe me while I'm searching I came across tens of posts regarding this problem which was always missing something or a bit different than what I need.

   Finally, Thank you! :)

6. Big Bagel
   Member
   Posted 1 year ago #
   

   I don't believe you need the 'order' => 'desc' argument since that's what it automatically defaults to. You used 'orderby' correctly, except for the single quotes around 'meta_value_num'. As far as the pagination, I always have problems with it. Try it with 'page' instead, like this:

   $args = array(
       'posts_per_page' => 20,
       'orderby' => 'meta_value_num',
       'meta_key' => 'top',
       'paged' => get_query_var( 'page' )
   );

   There's another way to try if it still gives you problems with the order. If the pagination doesn't work with get_query_var( 'paged' ) or get_query_var( 'page' ), I'll look for something else to try; I really hope it's not just a problem with the plugin.

7. eggroup
   Member
   Posted 1 year ago #
   

   Thanks for the tip. The current code I pasted above did make the pagination work just fine. I just needed to edit wp_pagenavi() as I posted above.

   You're right. I don't need 'order' => 'desc'. I removed it and everything is still working just fine.

   Thanks again! :)

8. yeasin33
   Member
   Posted 1 year ago #
   

   What if I want to paginate Category lists. Maybe from all the category or the child categories of a parent category?

Topic Closed

This topic has been closed to new replies.