custom select query w/ pagination

  • I have a list of events in 3 categories, that need to be listed in chronological order, based on a date that is added in the custom field meta value (exp_date) for each post. Category pages are named per their cat num – ex. category-6.php.

    It seemed I was able to do the sort, based on info from this post re: using a custom select query.

    At first, I thought it was working perfectly, until more posts were added, pushing the total number of posts to 15 – five more than the 10 per page default. Then realized that the post are sorted correctly but only on a per page basis.

    What I think is happening is that the results are being pulled from the posts table in order of post date (10 most recent), and then my query is being applied to sort the results on that page. On subsequent pages, results are offset by ten, and then sorted according to my query again.

    I think what I need to do stop the original wp query, and use only MY query to get results so that the results line up right on the next/previous pages.

    Unclear on how to make that happen, if it can be made to happen and would be very grateful for input or suggestions.

    my select query:

    SELECT * FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta
ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
AND $wpdb->posts.post_status = 'publish'
AND $wpdb->postmeta.meta_key = 'exp_date'
ORDER BY $wpdb->postmeta.meta_value ASC
Viewing 10 replies - 1 through 10 (of 10 total)

  • I have this almost identical problem. It’d be really nice if the WP_Query object allowed it’s own pagination. something like this would be good:

    $query = new WP_Query('cat=foo');
next_posts_link($query);

    if you use the pagenavi plugin, it’s possible, and it will probably work with standard wordpress previous and next links, but I haven’t tried that yet.

    $total = "your custom query goes here, but without LIMIT and OFFSET, so the total number of posts that match the query can be counted";

$totalposts = $wpdb->get_results($total, OBJECT);

$ppp = intval(get_query_var('posts_per_page'));

$wp_query->found_posts = count($totalposts);

$wp_query->max_num_pages = floor($wp_query->found_posts / $ppp);		

$on_page = intval(get_query_var('paged'));	

if($on_page == 0){ $on_page = 1; }		

$offset = ($on_page-1) * $ppp;

$wp_query->request = "your query again, but with the LIMIT and OFFSET as follows: LIMIT $ppp OFFSET $offset";

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

    i hope this helps someone ??

    i think you need to use ceil() instead of floor() so that you are rounding up the number of pages:

    $wp_query->max_num_pages = ceil($wp_query->found_posts / $ppp);

    true, true, thanks for pointing it out, ceil() it is ??

    Hi,

    I’ve a problem a bit similar, i think this solution can help me, but i don’t know how to use it, could you give us more information ?

    Thanks

    hi, calii ??

    could you please explain what kind of problem is it?

    omg, that was exactly what i was looking for. thanks aesqe!

    I’ve been searching for an answer for days. This is such a sigh of relief. Thanks to all!

    First of all, thanks for the help aesqe – I’ve had to use it on 2 projects just since you posted this, its been a lifesaver.

    Im working on another project now where I need to use it – Im using a custom sort to sort by total votes from a post voting system – but I’ve run into a problem:

    The blog Im working on is a normal blog first and foremost – I only need to work with one category. All is going fine, except that I want to set the page per post value to 5 – whereas it is 10 on the rest of the blog.

    Hardcoding $ppp seems to work great at first – but in practice, it gets a little trickier. When you hardcode $ppp, the proper number of posts are pulled, and everything paginates right. However, when you try to use the ‘Previous posts’ link to get to /page/2, if there are not enough posts (we’ll say 7 posts) to qualify for a page 2 according to the default settings (in my case, 10), wordpress 404s the request.

    That seems a little confusing, so I’ll try to reword it – the category has 7 posts. I’ve hardcoded $ppp to 5, and the blog’s ppp setting is 10. Because there are not more than 10 posts, wordpress 404s the request to /page/2, even though in this particular instance, there should be a page 2.

    I suppose I could try to catch this in archive.php, but it looks like it isnt setting up any of the variables (is_category() returns no, etc), so thats less than ideal.

    Anyone have any ideas?

    I came up with a solution – I needed to get my hands in there before wordpress could decide the page needed to be 404d, so I added a filter to remove the limit clause from the end of the db query – now, as long as there is a single post in the category, wordpress won’t try to 404 it.

    A side note for anyone who ends up with a similar situation – I needed this to happen only for a certain category, but because the action happens before is_category is even set, I had to use another query filter to test for it. Here’s the complete code:

    add_filter('request','SOTWrequest');

function SOTWrequest($request){
  if($request['category_name'] == 'sotw'){
    add_filter('post_limits', 'SOTWcustomquery');
  }
  return $request;
}

function SOTWcustomquery($limit){
  return '';
}

    Hope this helps someone!

Viewing 10 replies - 1 through 10 (of 10 total)
  • The topic ‘custom select query w/ pagination’ is closed to new replies.