Pagination not working using a custom SQL query with a custom type

Question

I've been struggling with this problem for 2 days now. I've read dozens of posts but can't get to the solution.

Note: all the var names are in spanish since this is a spanish website.

I've created a custom type named "promocion", but when listing the archives when I try to go to page 2 I get a 404 error.

The structure I'd like to set up is the following:

domain.com/promocion/new-promocion -> this works well
domain.com/promociones -> list of all the promociones, this works well too
domain.com/promociones/page/2 -> Error 404 - Not Found
Name of the archive file in my template: archive-promocion.php
Name of the single page view in my template: single-promocion.php

WordPress version: 3.1

Plugins:

wp-page-navi
Posts 2 Posts plugin (http://wordpress.org/extend/plugins/posts-to-posts/), used to create a relation between posts and promociones.

Here's the custom type created in functions.php

 register_post_type('promocion', array(
    'label' => 'Promociones',
    'description' => 'Promociones',
    'public' => true,
    'show_ui' => true,
    'show_in_menu' => true,
    'capability_type' => 'post',
    'hierarchical' => false,
    'rewrite' => array('slug' => 'promocion'),
    'query_var' => true,
    'has_archive' => 'promociones',
    'menu_position' => 4,
    'supports' => array('title','editor',),'labels' => array (
      'name' => 'promociones',
      'singular_name' => 'promocion',
      'menu_name' => 'Promociones',
      'add_new' => 'Añadir nueva',
      'add_new_item' => 'Añadir nueva',
      'edit' => 'Editar',
      'edit_item' => 'Editar Promoción',
      'new_item' => 'Nueva Promoción',
      'view' => 'Ver Promoción',
      'view_item' => 'Ver Promoción',
      'search_items' => 'Buscar Promociones',
      'not_found' => 'No se encontraron promociones',
      'not_found_in_trash' => 'No se encontraron promociones en la papelera',
      'parent' => 'Parent Promoción',),) );


    function my_connection_types() {
    if ( !function_exists( 'p2p_register_connection_type' ) )
        return;

    p2p_register_connection_type( array(
        'from' => 'promocion',
        'to' => 'post',
        'reciprocal' => true
    ) );
}
add_action( 'init', 'my_connection_types', 100 );

And here's the beginning of my archive page (archive-promocion.php) where I do a custom SQL query and set the pagination:

   if ( $cat != '' ) {
        $cat_filter = 'wp_term_taxonomy.term_id =  "' . $cat . '" AND';
    } else {
        $cat_filter = '';
    }

 $querystr = '
    SELECT DISTINCT
        promociones.ID,
        promociones.post_title
        FROM
        wp_terms
        Inner Join wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
        Inner Join wp_term_relationships AS wpr ON wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
        Inner Join wp_posts AS comercios ON comercios.ID = wpr.object_id
        Inner Join wp_p2p ON wp_p2p.p2p_to = comercios.ID
        Inner Join wp_posts AS promociones ON promociones.ID = wp_p2p.p2p_from
        WHERE
        wp_term_taxonomy.taxonomy =  "category" AND
        comercios.post_type =  "post" AND
        ' . $cat_filter . '
        promociones.post_type =  "promocion"
        ORDER BY
        promociones.menu_order ASC
 ';
    $totalposts = $wpdb->get_results($querystr, OBJECT);
    $ppp = 2;
    $wp_query->found_posts = count($totalposts);
    $wp_query->max_num_pages = ceil($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 = $querystr . " LIMIT $ppp OFFSET $offset";
    $pageposts = $wpdb->get_results($wp_query->request, OBJECT);

.htaccess file

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteBase /sitiodeloschicos/
RewriteRule ^index\.php$ - [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /sitiodeloschicos/index.php [L]
</IfModule>

Please help me, I'm going insane here and I'm already late with this project. Thanks!

score 2 · Answer 1 · answered May 05 '11 at 23:15

Well, after 1 week, yes, 1 week of pulling my hair, reading every forum on the Internet and asking everybody, I found the solution, which is pretty dumb.

All the code posted above is working perfect, there's nothing wrong with it.

What was messing with the code is that I had setup in the admin panel to display 10 posts per page. In my code I changed it to display only 1 per page for testing purposes, but somehow wordpress was still using the 10 value configured in the admin panel.

So what I did is I changed the value to 1 in the admin panel, and then increased mine in the code. This is a nasty solution but it works.

Is there any way to setup the global value of posts per page by code?

Yes. You can get the global value of posts_per_page by using get_option('posts_per_page'); That'll give you the value set in your Settings. If you want to manually set how many posts per page show up in each section (like say, category "dogs" you want 3 to show, which in "cats" you want 5) to can *add* to the query instead of writing a new one: $current = $wp_query->query_vars; $limit = '3'; query_posts(array('query_var' => $current, 'posts_per_page' => $limit)); (That's from memory though, which isn't reliable.. you;ll have to try it out first.) — Shelly, Jun 14 '11 at 17:59

score 0 · Answer 2 · answered Apr 29 '11 at 02:16

0

My guess is that this is because you are using custom SQL to get your custom post type. By using the WP_Query class, you can perform the query in a more standard WP way. I would use this to get my custom posts:

$query = new WP_Query();
$query->query('post_type=promociones');
if($query->have_posts()){
    while($query->have_posts()){
        // Do your loop stuff
    }
}

If you perform your query as such, you will be able to use the built in WP functions for getting the next (http://codex.wordpress.org/Function_Reference/next_post_link) and previous posts (http://codex.wordpress.org/Template_Tags/previous_post_link). Additionally, you could take advantage of this awesome pagination plugin (http://wordpress.org/extend/plugins/wp-paginate/).

Make sure to check out all that you can do with the WP_Query class: http://codex.wordpress.org/Function_Reference/WP_Query.

answered Apr 29 '11 at 02:16

tollmanz

3,113
4
29
34

But I need to do that complex query to get the posts I want. BTW, if I change the permalinks to default and try this http://localhost/sitiodeloschicos/?post_type=promocion&paged=2 I also get a 404 error. – Javier Renzi Apr 29 '11 at 04:01
Why do you need to use that query? It looks like you are finding a custom post type in a specific category. That can be done with WP_Query. Sorry to push using this class. I just know that getting expected results within WP is much easier when you conform to the way WP is intended to be used. – tollmanz Apr 29 '11 at 14:37
It's more complex than that. This query gets the promociones associated to a post under a certain category. I had to include wp_p2p in the query to get these results. – Javier Renzi Apr 29 '11 at 17:11

score 0 · Answer 3 · answered Apr 29 '11 at 19:50

If you're getting a 404 error with pretty permalinks, it's because you haven't created a custom post type archive template file. Custom post types don't pull default template files for them. So you have to create archive-post_type_name.php, single-post_type_name.php, etc. to avoid the 404's.

If you want categories associated with your custom post type, then you need to register_taxonomy and add categories that are meant solely for your custom post type. ( http://codex.wordpress.org/Function_Reference/register_taxonomy and http://justintadlock.com/archives/2010/06/10/a-refresher-on-custom-taxonomies)

Then you can use the regular WP_Query.

In any case, when you query_posts, you're removing all capabilities for pagination. You have to add it in. i.e.:

$cat = get_query_var('cat');
// this "activates" the pagination
$paged = (get_query_var('paged')) ? get_query_var('paged') : 1; 
query_posts(array('posts_per_page' => '9', 'cat' => $cat, 'paged' => $paged)));

I've created a custom template file and it's working (I've specified that above). It's not working after from page 1. — Javier Renzi, Apr 30 '11 at 04:19
Yes, I understand that - but you said:"BTW, if I change the permalinks to default and try this localhost/sitiodeloschicos/?post_type=promocion&paged=2 I also get a 404 error." which tells me that you have not created a custom template file for your *archives*(i.e. custom_type_name-archive.php) - which will cause the 404 error. And as for " It's not working after from page 1." that's what the code I provided above is for. When you use a custom query, you strip out the $paged stuff. You need to add it back into the query. — Shelly, Apr 30 '11 at 21:36

Pagination not working using a custom SQL query with a custom type

3 Answers3