获取包含最多帖子的用户类别

时间:2016-09-24 作者:user1952854

我正在尝试查询前3个类别,每个用户的帖子最多,但到目前为止运气不好。这就是我现在的处境:


SELECT DISTINCT(terms.term_id) as term_ID, terms.name, terms.slug, posts.post_author, t0.count_id

FROM wp_posts as posts
JOIN wp_term_relationships as relationships ON posts.ID = relationships.object_ID
JOIN wp_term_taxonomy as tax ON relationships.term_taxonomy_id = tax.term_taxonomy_id
JOIN wp_terms as terms ON tax.term_id = terms.term_id

JOIN (
SELECT COUNT(*) as count_id, count_terms.term_id
FROM wp_posts as posts_count
JOIN wp_term_relationships as count_relationships ON posts_count.ID = count_relationships.object_ID
JOIN wp_term_taxonomy as count_tax ON count_relationships.term_taxonomy_id = count_tax.term_taxonomy_id
JOIN wp_terms as count_terms ON count_tax.term_id = count_terms.term_id
WHERE count_tax.taxonomy = "category"
AND posts_count.post_status = "publish"
AND posts_count.post_author in (1,2,3)
group by count_terms.term_id

) as t0 on posts.post_author in (1,2,3)

WHERE tax.taxonomy = "category"
AND posts.post_status = "publish"
AND posts.post_author in (1,2,3)

它返回具有类别的用户,但返回该用户在这些类别中的所有帖子的总和。我需要前3个类别,每个用户最多的帖子。知道怎么做吗?

2 个回复
SO网友:cjbj

你的问题有点模棱两可,但我是这样理解的:给定一位作者,你想知道发表文章最多的三个类别。让我们使用wp_query 去那里。

$author = \'johndoe\';
$author_posts = new WP_Query (array (
  \'author\' => $author,
  \'posts_per_page\'=>-1,
  ));
现在我们有了这位作者的所有帖子的数组。遗憾的是,无法按类别排序(因为一篇帖子可能有多个类别),更不用说按每个类别的帖子数量排序了。因此,我们必须循环计算结果,然后自己计算。

 $cat_array = array();
 if ( $author_posts->have_posts() ) {
   while ( $author_posts->have_posts() ) {
     $author_posts->the_post();
     $cat_array[] = wp_get_post_categories(get_the_ID(),array(\'fields\'=>\'names\'));
     }
   }
这将生成一个数组$cat_array 它只包含所有作者帖子的所有分类名称。我们将不得不计算重复项,以查看哪个类别使用最多。有一个PHP函数:array_count_values.

 $cat_count = array_count_values($cat_array);
 $cat_count = arsort($catcount); // sorts an array by value from high to low
我们的结局是$cat_count 持有该作者从高到低的类别列表。根据需要,可以使用此信息循环$author_posts 再次或执行新查询以获取三大类别中的所有帖子:

$author = \'johndoe\';
$top_cats = $cat_count[0] . \',\' . $cat_count[1] . \',\' . $cat_count[2];
$author_posts = new WP_Query (array (
  \'author\'         => $author,
  \'posts_per_page\' =>-1,
  \'category_name\'  => $top_cats,
  ));
请注意,就计算而言,这是一个相当昂贵的过程。还请注意,我没有测试此代码,因此可能需要进行一些调试。

SO网友:mukto90

将此函数写入functions.php 或插件文件-

function wpse_240422_get_author_post_categories( $author_id ) {

    /**
     * this $categories array will contain all the post categories an author posted in
     */
    $categories = array();

    /**
     * gather all the posts publised by $author_id
     */
    $posts = query_posts( array( \'author\' => $author_id, \'posts_per_page\' => -1 ) );

    /**
     * loop through posts, take their categoreis and store them in $categories array
     */
    foreach ( $posts as $post ) {
        foreach ( get_the_category( $post->ID ) as $category ) {
            $categories[] = $category->term_id;
        }
    }

    /**
     * now count the number of times every category was used
     */
    $category_counter = array_count_values( $categories );

    /**
     * sort them from high to low
     */
    arsort( $category_counter );

    return $category_counter;
}
Use:要获取作者1使用的文章类别,只需将作者id传递到此函数,并只获取键。例如:

$author_categories = array_keys( wpse_240422_get_author_post_categories( 1 ) );
现在,这个$author_categories 将包含作者发布的所有类别。

最后,使用数组索引获取所需的类别-

$most_1st = $author_categories[0]; // top most \'used\' category
$most_2nd = $author_categories[1]; // second most \'used\' category
$most_3rd = $author_categories[2]; // third most \'used\' category