Custom SQL for Archive with Pagination

  • skbrewer

    (@skbrewer)


    10 years, 3 months ago

    I’m working on a wordpress-based site for a real estate office. I have a custom post type for listings, and I’m using custom metadata to store listing data. I’d like to use the archive page for a display of the listings, which works fine at the moment, but I have some complicated sort requirements.

    I’ve put together a SQL query to get the order I want, but can’t get WordPress to use it in the way I want it to. Is there no easy way to override the default query for an archive page so that I can retain plugin support for things like wp_pagenavi without having to reinvent the wheel?

    My query is basically doing a union of two selects, each with joins and order by requirements, with a final order by for the union of a dummy table in each select. It’s only going to get more complicated since I’m going to add a search box to the page to also query metadata, so I need a method that’ll let me modify the query used based on the POST vars.

Viewing 2 replies - 1 through 2 (of 2 total)
  • Thread Starter skbrewer

    (@skbrewer)

    10 years, 3 months ago

    Just a note to add that I’ve searched this to death. The closest thing to being helpful is this:

    http://codex.wordpress.org/Displaying_Posts_Using_a_Custom_Select_Query

    However, it doesn’t preserve pagination, and trying to display 7000 listings on one page doesn’t work out so well. Also, whatever solution I come up with should probably be able to suppress or completely replace the stock query so that I’m not doubling my page execution time.

    The old version of our site handled this problem with a hardcoded “/listings/” page and we implemented all of our own logic via a custom shortcode and functions to list and search listings or display individual ones (ie: /listings/somenumber/) using server rewrite rules. I convinced myself it’d be a good idea to integrate as much of this as possible with WordPress for the rewrite, and so far that’s been a bit of a headache.

    Moderator keesiemeijer

    (@keesiemeijer)

    10 years, 3 months ago

    WordPress uses the global $wp_query object to determine how many pages are needed. You can set the total (max_num_pages) for your query before you use any of the WordPress pagination functions.

    Example with 150 posts found by your custom query:

    $posts_per_page = 10;
$post_count = 150;

global $wp_query;
$wp_query->max_num_pages = ceil( $post_count / $posts_per_page );

// pagination functions
next_posts_link( 'Older Entries' );
previous_posts_link( 'Newer Entries' );

    But you’ll need to limit the posts from your custom query yourself.
    http://php.about.com/od/mysqlcommands/g/Limit_sql.htm

    Can you post the custom query?

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Custom SQL for Archive with Pagination’ is closed to new replies.