使用自定义SQL查询进行分页

时间：2011-06-30 作者：dashaluna

我有自己的SQL字符串来选择带有特定WHERE子句的自定义Post类型Post。我使用了偏移量和限制，根据显示的页面返回适当的帖子。这很好用。

现在，我想previous_posts_link() 和next_posts_link() 功能正常。他们都是从get_posts_nav_link 其中使用global $wp_query.

有没有办法重新分配global $wp_query 使用我的SQL字符串或$wpdb->get_results 结果还是其他什么？所以本地人previous_posts_link() 和next_posts_link() WP功能将起作用。

如果没有，如何复制上一个和下一个后期链接功能？

我真的很感激任何帮助和建议！我完全受不了了
谢谢：）

NOTE: 我刚刚注意到previous_posts_link() 所有页面都正常工作，但是no idea why 在这种情况下，为什么next_posts_link 不工作：S

代码如下：

$paged = (get_query_var(\'paged\')) ? get_query_var(\'paged\') : 1;
$post_per_page = intval(get_query_var(\'posts_per_page\'));
$offset = ($paged - 1)*$post_per_page;

$sql = "
SELECT SQL_CALC_FOUND_ROWS  wp_posts.*, wp_postmeta.* 
FROM wp_posts 
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) 
WHERE 1=1  
    AND wp_posts.post_type = \'movie\' 
    AND (wp_posts.post_status = \'publish\' OR wp_posts.post_status = \'private\') 
    AND ((wp_postmeta.meta_key = \'_expiry_date\' AND CAST(wp_postmeta.meta_value AS DATE) >= \'".$current_date."\') 
        OR (mt1.meta_key = \'_expiry_date\' AND CAST(mt1.meta_value AS CHAR) = \'\'))
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC
LIMIT ".$offset.", ".$post_per_page;

$movies_all_current = $wpdb->get_results( $sql, OBJECT);

if($movies_all_current) {
global $post;

//loop
foreach( $movies_all_current as $key=>$post ) {
    setup_postdata($post);
    //display each post
    //...
} //end foreach ?>

    //navigation
<div class="navigation">
    <div class="previous panel"><?php previous_posts_link(\'&laquo; newer\') ?></div>
    <div class="next panel"><?php next_posts_link(\'older &raquo;\') ?></div>
</div>
}

4 个回复

最合适的回答,由SO网友:dashaluna 整理而成

好的，我最后到了那里。我无法使用WP_Query 类，因为我确实需要拥有自己的非常大和复杂的SQL。以下是我最终得到的：

在里面functions.php 我有自己的自定义SQL和逻辑，用于计算WP分页逻辑所需的值：

function vacancies_current( ){
    global $wpdb, $paged, $max_num_pages, $current_date;

    $paged = (get_query_var(\'paged\')) ? get_query_var(\'paged\') : 1;
    $post_per_page = intval(get_query_var(\'posts_per_page\'));
    $offset = ($paged - 1)*$post_per_page;

    /* Custom sql here. I left out the important bits and deleted the body 
     as it will be specific when you have your own. */
    $sql = "
        SELECT SQL_CALC_FOUND_ROWS  {$wpdb->posts}.*
        FROM {$wpdb->posts}
        ....
        GROUP BY {$wpdb->posts}.ID 
        ORDER BY {$wpdb->posts}.post_date DESC
        LIMIT ".$offset.", ".$post_per_page."; ";   

    $sql_result = $wpdb->get_results( $sql, OBJECT);

    /* Determine the total of results found to calculate the max_num_pages
     for next_posts_link navigation */
    $sql_posts_total = $wpdb->get_var( "SELECT FOUND_ROWS();" );
    $max_num_pages = ceil($sql_posts_total / $post_per_page);

    return $sql_result;
}

然后在我的模板文件中，我有：

<?php 
    $vacancies_current = vacancies_current();
    /*followed by a standart loop to display your results */ 
 ?>
<div class="navigation">
    <div class="previous panel"><?php previous_posts_link(\'&laquo; previous vacancies\',$max_num_pages) ?></div>
    <div class="next panel"><?php next_posts_link(\'more vacancies &raquo;\',$max_num_pages) ?></div>
</div>

诀窍在于供应previous_posts_link() 和next_posts_link 这个$max_num_pages 显然，在正确计算它时。

这很有效。希望对某人有所帮助：）

达莎

SO网友:anu

看看Custom Queries - 它允许您以许多有趣而有用的方式修改wp\\u查询调用，并将结果推回到全局查询对象中。

SO网友:Chris_O

扩展Anu的答案。您可以使用WP_Query class 让WordPress处理所有繁重的SQL提升。这肯定会解决您的导航问题。

在您的\\u expiry\\u date meta\\u关键字中查询电影帖子类型的示例：

$today = getdate();
$args = array(
    \'post_type\' => \'movie\',
    \'meta_query\' => array(
            \'meta_key\' => \'_expiry_date\',
            \'meta_value\' => $today,
            \'meta_compare\' => \'< \'
                    ),
    \'posts_per_page\' => -1,
     \'order\'    => \'DESC\'
    );

    $movie_query = new WP_Query( $args );

    while ( $movie_query->have_posts() ) : $movie_query->the_post(); 
    // Do stuff
   endwhile; ?>

 <div class="navigation">
<div class="previous panel"><?php previous_posts_link(\'&laquo; newer\') ?></div>
<div class="next panel"><?php next_posts_link(\'older &raquo;\') ?></div>
</div>

SO网友:iceoverr

<?php

global $wpdb, $paged;
query_posts($query_string . \'&posts_per_page=9\');
$paged = (get_query_var(\'paged\')) ? get_query_var(\'paged\') : 1;
$author = isset($_GET[\'author_name\']) ? get_userdatabylogin($author_name) : get_userdata(intval($author));

query_posts($query_string . \'&posts_per_page=9\');

$args = array(
\'post_type\' => \'post\',
\'meta_query\' => array(
        \'meta_key\' => \'autor\',
    \'post_status\' => \'publish\',
        \'meta_value\' => $author->id,
            ),
\'paged\' => $paged,
\'posts_per_page\' => 9,
\'order\'    => \'DESC\'
);

$postsQuery = new WP_Query( $args );

?>

Template:

<h1>Články od <?php echo $author->display_name; ?></h1>
        <ul class="thumbnails">

            <?php while ( $postsQuery->have_posts() ) : $postsQuery->the_post();  ?>
                <li class="span3">
                <div class="thumbnail">
                    <a href="<?php the_permalink(); ?>">
                    <?php the_post_thumbnail(array(260, 259)); ?>
                    </a>
                    <?php
                    $class = \'\';
                    if (in_category(\'fashion\')) {
                    $class = "link-fashion";
                    } else if (in_category(\'beauty\')) {
                    $class = "link-beauty";
                    } else if (in_category(\'gourmet\')) {
                    $class = "link-gourmet";
                    } else if (in_category(\'lifestyle\')) {
                    $class = "link-lifestyle";
                    } else if (in_category(\'about-us\')) {
                    $class = "link-about";
                    }
                    ?>
                    <a href="<?php the_permalink(); ?>">
                    <h2 class="<?=  $class ?>">
                        <span></span>
                        <?php
                        // short_title(\'...\', 25); 
                        echo get_the_title();
                        ?>
                    </h2>
                    </a>
                    <?php the_excerpt(); ?>
                    <hr>
                </div>
                </li>
            <?php endwhile; ?>

        </ul>
        <?php wp_pagenavi(); ?>

结束

小码农CODE

使用自定义SQL查询进行分页

相关推荐

从非WordPress MySQL数据库导入数据