根据类别结构获取复杂的结果集

时间：2019-03-11 作者：Janith Chinthana

我需要根据类别结构从WordPress数据库中获得一个复杂的结果集。

首先，我将尝试解释我的类别结构。我有以下三层结构。

flight [level 1] [ID : 100]
    - class      [level 2] [ID : 200]
        -- economy  [level 3] [ID : 201]
        -- business [level 3] [ID : 202]
        -- first    [level 3] [ID : 203]
    - alliance   [level 2] [ID : 210]
        -- star     [level 3] [ID : 211]
        -- oneworld [level 3] [ID : 212]
        -- skyteam  [level 3] [ID : 213]
现在算法：

我需要把所有的帖子都标记为flight 类别或其具有以下规则的子类别之一。

我需要排除标记为economy;

然而，如果它的兄弟之一(business 或first) 已标记alliance 或者它的一个孩子被标记，如果economy 也在相同的帖子中添加了标签[但当我们排除economy 一般分类]我目前的做法：

我在努力tax_query 使用以下类型的参数

$args = array(
    \'post_type\' => \'post\',
    \'tax_query\' => array(
        \'relation\' => \'AND\',
        array(
            \'taxonomy\' => \'category\',
            \'field\'    => \'term_id\',
            \'terms\'    => array( 100 ),
            \'include_children\' => 1,
        ),
        array(
            \'relation\' => \'AND\',
            array(
                \'taxonomy\' => \'category\',
                \'field\'    => \'term_id\',
                \'terms\'    => array( 200 ),
                \'include_children\' => 1,
            ),
            array(
                \'taxonomy\' => \'category\',
                \'field\'    => \'term_id\',
                \'terms\'    => array( 201 ),
                \'operator\' => \'NOT IN\',
            ),
        ),
    ),
);
$query = new WP_Query( $args );
但这里的问题是，它没有满足第一条规则。即使我需要贴上标签business 和first 不考虑economy, 上面的查询只会忽略所有标记为economy.

我甚至会考虑SQL 也要接近。任何帮助都会非常感激，因为我已经为此挣扎了几天了。

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

在这里大声思考。。。

$args = array(
    \'post_type\' => \'post\',
    \'tax_query\' => array(
        \'relation\' => \'OR\',
        array(
            \'taxonomy\' => \'category\',
            \'field\'    => \'term_id\',
            \'terms\'    => array( 202, 203 ),
            \'include_children\' => 1,
        ),
        array(
            \'relation\' => \'AND\',
            array(
                \'taxonomy\' => \'category\',
                \'field\'    => \'term_id\',
                \'terms\'    => array( 100 ),
                \'include_children\' => false,
            )
        ),
    ),
);
$query = new WP_Query( $args );
获取分类术语IDs 202、203等中的所有帖子。。。

或

仅获取taxonomy term ID 100中的所有帖子（不包括子帖子）

事实上，上述内容可以表示为（无嵌套查询）：

$args = array(
    \'post_type\' => \'post\',
    \'tax_query\' => array(
        \'relation\' => \'OR\',
        array(
            \'taxonomy\' => \'category\',
            \'field\'    => \'term_id\',
            \'terms\'    => array( 202, 203 ),
            \'include_children\' => 1,
        ),
        array(
            \'taxonomy\' => \'category\',
            \'field\'    => \'term_id\',
            \'terms\'    => array( 100 ),
            \'include_children\' => false,
        )
    ),
);
$query = new WP_Query( $args );
获取分类术语IDs 202、203等中的所有帖子。。。

或

仅获取taxonomy term ID 100中的所有帖子（不包括子帖子）

再一次。。。或者（如果您强制编写者始终通过挂接的方式设置子类别save_post 或类似）：

$args = array(
    \'post_type\' => \'post\',
    \'tax_query\' => array(
        \'relation\' => \'AND\',
        array(
            \'taxonomy\' => \'category\',
            \'field\'    => \'term_id\',
            \'terms\'    => array( 202, 203 ),
            \'include_children\' => 1,
        ),
    ),
);
$query = new WP_Query( $args );
获取分类术语IDs 202、203等中的所有帖子。。。

更新这将是我的快速方法：

$terms = get_terms(array(
        \'taxonomy\' => \'category\',
        \'exclude\' => 201
));

$term_ids = array_column( $terms, \'term_id\' );

$args = array(
    \'post_type\' => \'post\',
    \'tax_query\' => array(
        \'relation\' => \'AND\',
        array(
            \'taxonomy\' => \'category\',
            \'field\'    => \'term_id\',
            \'terms\'    => $term_ids,
            \'include_children\' => 1,
        ),
    ),
);
$query = new WP_Query( $args );

$post_term_ids = [];

foreach ( $query->posts as $post ) {
    $post_term_ids[$post->ID] = implode( \', \', array_column(
        wp_get_object_terms( $post->ID, \'category\' ), \'name\', \'term_id\'
    ));
}
返回的示例数据集是：

array (
  547 => \'Alliance, Business, Class, Flights, Oneworld\',
  540 => \'Business, Class, Economy, Flights\',
  605 => \'Star\',
  524 => \'Alliance, Flights, Oneworld\',
  594 => \'Skyteam\',
  569 => \'Star\',
  551 => \'Flights, Oneworld, Star\',
  582 => \'Business\',
  528 => \'Business, Class, Economy, Flights\',
  565 => \'Skyteam\',
)
如果我要删除\'exclude\' => 201 呼叫时get_terms 我会看到类似的结果：

array (
  547 => \'Alliance, Business, Class, Flights, Oneworld\',
  540 => \'Business, Class, Economy, Flights\',
  605 => \'Star\',
  524 => \'Alliance, Flights, Oneworld\',
  594 => \'Skyteam\',
  569 => \'Star\',
  551 => \'Flights, Oneworld, Star\',
  582 => \'Business\',
  528 => \'Business, Class, Economy, Flights\',
  565 => \'Economy\', // <-- WHAT WE DO NOT WANT
)
正如你所看到的，除了那些economy 单独地哪里economy 存在，但也存在另一个分类，然后返回该帖子。这些键是post ID。

注意：我的示例结果显示了如果内容编写器忘记指定祖先，您的潜在价值可能是什么。如前所述，这在另一个问题中是可以解决的。

更新#2

# change the IDs below to match your environment
$class    = 92; // ancestor
$alliance = 96; // ancestor
$economy  = 93; // child

$terms_class = get_terms(array(
    \'taxonomy\' => \'category\',
    \'exclude\'  => [$alliance, $economy],
    \'child_of\' => $class,
));

$terms_alliance = get_terms(array(
    \'taxonomy\' => \'category\',
    \'child_of\' => $alliance,
));

$term_ids_class    = array_column($terms_class, \'term_id\');
$term_ids_alliance = array_column($terms_alliance, \'term_id\');

$args = array(
    \'post_type\'      => \'post\',
    \'posts_per_page\' => -1,
    \'tax_query\'      => array(
        \'relation\' => \'OR\',
        array(
            \'relation\' => \'AND\',
            array(
                \'taxonomy\' => \'category\',
                \'field\'    => \'term_id\',
                \'terms\'    => $term_ids_class,
            ),
            array(
                \'taxonomy\' => \'category\',
                \'field\'    => \'term_id\',
                \'terms\'    => $term_ids_alliance,
                \'operator\' => \'NOT IN\',
            ),
        ),
        array(
            \'relation\' => \'AND\',
            array(
                \'taxonomy\' => \'category\',
                \'field\'    => \'term_id\',
                \'terms\'    => $term_ids_alliance,
            ),
            array(
                \'taxonomy\' => \'category\',
                \'field\'    => \'term_id\',
                \'terms\'    => $economy,
                \'operator\' => \'NOT IN\',
            ),
        ),

    ),
);

$query = new WP_Query( $args );

$post_term_ids = [];

foreach ( $query->posts as $post ) {
    $post_term_ids[$post->ID] = implode( \', \', array_column(
        wp_get_object_terms( $post->ID, \'category\' ), \'name\', \'term_id\'
    ));
}
在示例数据集中，我得到如下结果：

array (
  547 => \'Alliance, Business, Class, Flights, Oneworld\',
  540 => \'Business, Class, Economy, Flights\',
  524 => \'Alliance, Flights, Oneworld\',
  594 => \'Skyteam\',
  569 => \'Star\',
  551 => \'Flights, Oneworld, Star\',
  582 => \'Business\',
  528 => \'Business, Class, Economy, Flights\',
  589 => \'Oneworld\',
  603 => \'Oneworld\',
  584 => \'Oneworld\',
  585 => \'First\',
  601 => \'First\',
  543 => \'Business, Class, Economy, Flights\',
  572 => \'First\',
  578 => \'Business\',
  592 => \'Alliance, Business, Class, Flights, Star\',
  563 => \'Star\',
  559 => \'Skyteam\',
  575 => \'Star\',
  549 => \'Flights, Oneworld, Skyteam\',
  596 => \'Star\',
  534 => \'Class, First, Flights\',
  561 => \'Star\',
  556 => \'Star\',
  587 => \'Oneworld\',
)
SQL如下：

SELECT 
  wp_posts.* 
FROM 
  wp_posts 
  LEFT JOIN wp_term_relationships ON (
    wp_posts.ID = wp_term_relationships.object_id
  ) 
  LEFT JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) 
WHERE 
  1 = 1 
  AND (
    (
      wp_term_relationships.term_taxonomy_id IN (94, 95) 
      AND wp_posts.ID NOT IN (
        SELECT 
          object_id 
        FROM 
          wp_term_relationships 
        WHERE 
          term_taxonomy_id IN (97, 98, 99)
      )
    ) 
    OR (
      tt1.term_taxonomy_id IN (97, 98, 99) 
      AND wp_posts.ID NOT IN (
        SELECT 
          object_id 
        FROM 
          wp_term_relationships 
        WHERE 
          term_taxonomy_id IN (93)
      )
    )
  ) 
  AND wp_posts.post_type = \'post\' 
  AND (
    wp_posts.post_status = \'publish\' 
    OR wp_posts.post_status = \'private\'
  ) 
GROUP BY 
  wp_posts.ID 
ORDER BY 
  wp_posts.post_date DESC
虽然效率不高，但这是一种蛮力的方式。

标签： wp-query   categories   tax-query   advanced-taxonomy-queries   小码农  

相关推荐

使用wp_Dropdown_Categories($args)的输出

请温柔点，我对所有这些编码都是新手！我终于找到了如何获得前端下拉字段，允许我从EDD类别中进行选择（我从Codex中获得）：<div class=\"gallery-row\"> <li id=\"categories\"> <h2><?php _e( \'Categories:\' ); ?></h2> <form id=\"category-select\" class=\"category-