按(分层定制)分类术语和术语子项对帖子进行排序

时间：2012-10-15 作者：kaiser

场景是一种自定义的帖子类型wikitopicsarchive-wiki.php

出现这种情况，然后由post_date （这是默认设置）。

相应的核心查询是：

SELECT SQL_CALC_FOUND_ROWS {$wpdb->prefix}posts.ID 
    FROM {$wpdb->prefix}posts WHERE 1=1 
    AND {$wpdb->prefix}posts.post_type = \'wiki\' 
    AND ({$wpdb->prefix}posts.post_status = \'publish\' OR {$wpdb->prefix}posts.post_status = \'private\') 
ORDER BY {$wpdb->prefix}posts.post_date 
DESC 
LIMIT 0, {$setting->posts_per_page}
是的，我在获取此查询时已登录-因此状态+=private.

该任务按自定义层次结构主要术语对所有帖子进行排序。在主分类单元内，将职位分配给子项。因此，主数组只包含分类法主分类单元和实际wiki-分配给相应轴突的立柱。如果它们没有被分配到子分类单元，或者该分类单元没有子分类单元，那么只需将其添加到主数组中即可。

这应该发生在哪里

内部pre_get_posts 或posts_pieces （和类似过滤器）。我不想为此添加其他查询。

非常感谢。

1 个回复
最合适的回答,由SO网友:marfarma 整理而成

此查询将处理分类法中的两个层次结构。两个以上的层次结构级别，您将需要一个递归的自连接。

这样做的目的是在父级顺序中以正确的子级返回帖子。要创建适当的父级标题，您需要将当前帖子的父分类单元与之前帖子的父分类单元进行比较。在页面顶部打印分类单元标题，并在父分类单元值更改的每个点打印另一个级别。您需要在每个post上调用一个函数来获取父级分类单元，因为我不知道如何将查询中的值传递给post对象。

 SELECT *
   from wp_posts 
  LEFT JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
    LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
    LEFT JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id)
    LEFT JOIN wp_term_taxonomy AS parent ON (wp_term_taxonomy.parent = parent.term_taxonomy_id)
    LEFT JOIN wp_terms AS parent_terms ON (parent.term_id = parent_terms.term_id) 
  WHERE wp_posts.post_type = \'post\'
  AND wp_term_taxonomy.taxonomy = \'category\'
  and not exists(select 1 
                   from wp_posts AS subposts
                        LEFT JOIN wp_term_relationships as subtr ON subposts.ID = subtr.object_id
                        LEFT JOIN wp_term_taxonomy as subtt ON (subtr.term_taxonomy_id = subtt.term_taxonomy_id)
                        LEFT JOIN wp_terms as subt ON (subtt.term_id = subt.term_id)
                        LEFT JOIN wp_term_taxonomy AS subparent ON (subtt.parent = subparent.term_taxonomy_id)
                  where subtt.parent > wp_term_taxonomy.parent
                    and subposts.ID = wp_posts.ID 
                    AND subtt.taxonomy = wp_term_taxonomy.taxonomy)
  ORDER BY IFNULL(  parent_terms.slug ,  wp_terms.slug) ASC, wp_terms.slug  ASC;
考虑到这是存档页面的正确SQL，让我们考虑如何使用过滤器实现，例如pre\\u get\\u posts等。

UPDATE上面的SQL查询已经过测试和修改，以返回正确的结果。下面的查询与我最初的查询类似，它为任何有父项的帖子返回两行。

   SELECT wp_posts.ID,  parent_terms.slug parent_slug, wp_terms.slug, wp_term_taxonomy.taxonomy, wp_term_taxonomy.parent
         , IFNULL(  parent_terms.slug ,  wp_terms.slug) sort_col2
   from wp_posts 
   LEFT JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
    LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
    LEFT JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id)
    LEFT JOIN wp_term_taxonomy AS parent ON (wp_term_taxonomy.parent = parent.term_taxonomy_id)
    LEFT JOIN wp_terms AS parent_terms ON (parent.term_id = parent_terms.term_id) 
  WHERE wp_posts.post_type = \'post\'
  AND wp_term_taxonomy.taxonomy = \'category\'
  ORDER BY IFNULL(  parent_terms.slug ,  wp_terms.slug) ASC, wp_terms.slug  ASC;
请注意这篇文章。id=629在结果中出现两次：

  +-----+-------------+---------------+----------+--------+-----------+
  | ID  | parent_slug | slug          | taxonomy | parent | sort_col2 |
  +-----+-------------+---------------+----------+--------+-----------+
  | 629 |             | business      | category | 0      | business  |
  | 629 | business    | press-release | category | 3      | business  |
  | 618 |             | media         | category | 0      | media     |
  | 608 |             | media         | category | 0      | media     |
  | 624 |             | startups      | category | 0      | startups  |
  | 621 |             | startups      | category | 0      | startups  |
  +-----+-------------+---------------+----------+--------+-----------+
  6 rows in set (0.00 sec)
通过添加NOT EXISTS(...) 条件：

  SELECT wp_posts.ID,  parent_terms.slug parent_slug, wp_terms.slug, wp_term_taxonomy.taxonomy, wp_term_taxonomy.parent
         , IFNULL(  parent_terms.slug ,  wp_terms.slug) sort_col2
   from wp_posts 
  LEFT JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
    LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
    LEFT JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id)
    LEFT JOIN wp_term_taxonomy AS parent ON (wp_term_taxonomy.parent = parent.term_taxonomy_id)
    LEFT JOIN wp_terms AS parent_terms ON (parent.term_id = parent_terms.term_id) 
  WHERE wp_posts.post_type = \'post\'
  AND wp_term_taxonomy.taxonomy = \'category\'
  and not exists(select 1 
                   from wp_posts AS subposts
                        LEFT JOIN wp_term_relationships as subtr ON subposts.ID = subtr.object_id
                        LEFT JOIN wp_term_taxonomy as subtt ON (subtr.term_taxonomy_id = subtt.term_taxonomy_id)
                        LEFT JOIN wp_terms as subt ON (subtt.term_id = subt.term_id)
                        LEFT JOIN wp_term_taxonomy AS subparent ON (subtt.parent = subparent.term_taxonomy_id)
                  where subtt.parent > wp_term_taxonomy.parent
                    and subposts.ID = wp_posts.ID )
  ORDER BY IFNULL(  parent_terms.slug ,  wp_terms.slug) ASC, wp_terms.slug  ASC;
以及结果，在父级中按子级排序的帖子，并且没有重复记录：

  +-----+-------------+---------------+----------+--------+-----------+
  | ID  | parent_slug | slug          | taxonomy | parent | sort_col2 |
  +-----+-------------+---------------+----------+--------+-----------+
  | 629 | business    | press-release | category | 3      | business  |
  | 618 |             | media         | category | 0      | media     |
  | 608 |             | media         | category | 0      | media     |
  | 624 |             | startups      | category | 0      | startups  |
  | 621 |             | startups      | category | 0      | startups  |
  +-----+-------------+---------------+----------+--------+-----------+
  5 rows in set (0.00 sec)
<小时>

UPDATE: Function NOT current with the latest SQL above

function wpse69290_query( $pieces, $obj )
{
    global $wpdb;

    #$pieces[\'fields\'] = "* ";

    $pieces[\'join\'] .= " LEFT JOIN `$wpdb->term_relationships` AS trs ON ($wpdb->posts.ID = trs.object_id)";
    $pieces[\'join\'] .= " LEFT JOIN `$wpdb->term_taxonomy` AS tt ON (trs.term_taxonomy_id = tt.term_taxonomy_id)";
    $pieces[\'join\'] .= " LEFT JOIN `$wpdb->terms` AS t ON (tt.term_id = t.term_id)";
    $pieces[\'join\'] .= " LEFT JOIN `$wpdb->term_taxonomy` AS parent ON (parent.parent = trs.term_taxonomy_id)";
    $pieces[\'join\'] .= " LEFT JOIN `$wpdb->terms` AS parent_terms ON (parent.term_id = parent_terms.term_id)";

    $pieces[\'where\'] .= " AND (tt.taxonomy = \'topics\')";

    $pieces[\'orderby\'] = "IFNULL(parent_terms.slug, t.slug) ASC";

    $pieces[\'limits\'] = "LIMIT 0, 999";

    return $pieces;
}
add_filter( \'posts_clauses\', \'wpse69290_query\', 10, 2 );
是的，不需要使用prepare() 对于仅具有默认表名的查询部件。

结束
标签： custom-post-types   custom-taxonomy   order   pre-get-posts   tax-query   小码农  

相关推荐

Orderby = none not working

我正在尝试将“orderby=none”设置为我的循环，但它不起作用。这是我的代码：$query = new WP_Query(array(\'showposts\'=>2, \'post__in\' => array(99,4,5,2,8,55), \'orderby\'=>\'none\')); 有人能帮我吗？谢谢