doumengwei0138 2016-10-03 20:15
浏览 60
已采纳

使用posts_groupby的Wordpress wp_query过滤器 - 如何在连接表中COUNT值?

try to join my custom table to WP_Query, here is my wp_query code

     $the_query = new WP_Query( array('post_type'=> $post_type, 'paged' => $paged ,'posts_per_page' => 1, 'meta_query' => $meta_query, 'tax_query' => $tax_query, 'post_status' => 'publish', 'follower_id' => '') );

if ( $the_query->have_posts() ) : while ( $the_query->have_posts() ) : $the_query->the_post(); 

I saw the online tutorial said there I can join my custom table to the wp_query, using posts_join, posts_group, posts_where WordPress filter.

my problem is having a custom table, which calls wp_order_history

ID      post_id      order_number
1       211          AFD123D342234
2       211          dsafa23411414
3       110          sdafsaf234234
4       211          sdafasdfadsfs

there is the SQL code I want to merge to wp_query

LEFT JOIN(Select count(post_id), post_id as total_order From wp_order_history as oh WHERE oh.post_id = $wpdb->post_id group by post_id)

here I try to usings posts_where filter but not working :(

function custom_posts_where($where) {
            global $wpdb;
            $table_name = $wpdb->prefix . 'order_history';
            $where .= $wpdb->prepare("LEFT JOIN(Select count(post_id), post_id as total_order From wp_order_history as oh WHERE oh.post_id = $wpdb->post_id group by post_id)");
            return $where;
        }
add_filter('posts_where', 'custom_posts_where');

The result I want to get in the wp_query loop, count the total of orders each business have.

UPDATE, seem like code running through, but how can i output this? inside the wp_query loop? $the_query->total?

add_filter( 'posts_join', 'custom_posts_join', 10, 2 );
    function custom_posts_join( $join, $query ) {

        global $wpdb;
        //* if main query and search...
        // if ( is_main_query() && is_search() ) {

            //* join term_relationships, term_taxonomy, and terms into the current SQL where clause
            $join .= "
                LEFT JOIN 
                    (SELECT count(b.post_id) as total, b.post_id FROM wp_order_history as b group by b.business_id) as c
                ON c.post_id = $wpdb->posts.ID ";

        // }
        return $join;

    }

problem solve, thanks Hobo

  • 写回答

1条回答 默认 最新

  • douzhan2027 2016-10-04 06:49
    关注

    Combining your code with my comments (NB: the inner query in the join function is different to yours - the example table you posted doesn't have a business_id column):

    add_filter( 'posts_join', 'custom_posts_join', 10, 2 );
    function custom_posts_join( $join, $query ) {
    
        global $wpdb;
        //* if main query and search...
        // if ( is_main_query() && is_search() ) {
    
            //* join term_relationships, term_taxonomy, and terms into the current SQL where clause
    
            $join .= "
                LEFT JOIN 
                    (SELECT count(*) as total, b.post_id FROM {$wpdb->prefix}order_history as b group by b.post_id) as c
                ON c.post_id = $wpdb->posts.ID ";
    
        // }
        return $join;
    }
    
    add_filter( 'posts_fields', 'custom_posts_fields');
    function custom_posts_fields( $sql ) {
        // c matches the table alias used in custom_posts_join().
        // The ifnull() function makes sure we have a value of 0 if the joined post is not in wp_order_history
        return $sql . ", ifnull(c.total,0) as total";
    }
    

    The value of the total column can then be accessed on the global $post variable. For example, in the loop:

    <?php 
    global $post;
    if ( have_posts() ) :
        while ( have_posts() ) : the_post();
            print_r($post->total); // Field name should match the column name in the custom_posts_fields() function
        endwhile;
    endif;
    ?>
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 数据库数据成问号了,前台查询正常,数据库查询是?号
  • ¥15 算法使用了tf-idf,用手肘图确定k值确定不了,第四轮廓系数又太小才有0.006088746097507285,如何解决?(相关搜索:数据处理)
  • ¥15 彩灯控制电路,会的加我QQ1482956179
  • ¥200 相机拍直接转存到电脑上 立拍立穿无线局域网传
  • ¥15 (关键词-电路设计)
  • ¥15 如何解决MIPS计算是否溢出
  • ¥15 vue中我代理了iframe,iframe却走的是路由,没有显示该显示的网站,这个该如何处理
  • ¥15 操作系统相关算法中while();的含义
  • ¥15 CNVcaller安装后无法找到文件
  • ¥15 visual studio2022中文乱码无法解决