$wpdb：计算与3个不同分类中的3个术语对应的帖子

时间：2012-10-22 作者：stefano1

如果我举个例子就更容易了：

我有一个自定义的帖子类型MYPOSTTYPE, 有3种分类法TAX_1, TAX_2 和TAX_3

如果我有3个术语：“TERM\\u TAX\\u 1”TAX_1, \'TERM\\u TAX\\u 2\'英寸TAX_2, 和中的“TERM\\u TAX\\u 3”TAX_3, 我如何计算有多少MYPOSTTYPE具有SQL查询和$wpdb的所有3个术语？

我试过这样的东西，但没用。。。计数器始终为0

SELECT COUNT($wpdb->posts.ID) AS counter
FROM $wpdb->posts 
    LEFT JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    LEFT JOIN $wpdb->terms AS tax_one ON ($wpdb->term_taxonomy.term_id = tax_one.term_id)
    LEFT JOIN $wpdb->terms AS tax_two ON ($wpdb->term_taxonomy.term_id = tax_two.term_id)
    LEFT JOIN $wpdb->terms AS tax_three ON ($wpdb->term_taxonomy.term_id = tax_three.term_id)
WHERE 1=1
    AND $wpdb->posts.post_type = \'MYPOSTTYPE\'
    AND $wpdb->posts.post_status = \'publish\'
    AND $wpdb->term_taxonomy.taxonomy  IN (\'TAX_1\', \'TAX_2\', \'TAX_3\')
    AND tax_one.slug = \'TERM_TAX_1\' 
    AND tax_two.slug = \'TERM_TAX_1\'
    AND tax_three.slug = \'TERM_TAX_1\'
UPDATE: 我也试过tax_query, 但我有这个问题Problem with get_posts, tax_query and counting the number of posts

所以我想尝试直接查询数据库

2 个回复
SO网友:kaiser

执行 然后count 结果。无需使用几十个JOINS.

$posts = new WP_Query( array(
    \'post_type\' => \'MYPOSTTYPE\', 
    \'tax_query\' => array(     
        array(         
            \'taxonomy\' => \'TAX_1\',
            \'field\'    => \'slug\',         
            \'terms\'    => array( \'TERM_TAX_1\' ),
            \'operator\' => \'IN\'
        ),
        array(
            // etc.
        )
    ),
    \'post_status\' => \'publish\'
);
prinft(
     \'<h3>COUNT: %s</h3>\'
    ,count( $posts )
);

SO网友:Mridul Aggarwal

由于MySQL允许在where子句中嵌套查询，我认为这样的查询应该可以工作

SELECT COUNT($wpdb->posts.ID) AS counter
FROM $wpdb->posts 
    INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    INNER JOIN $wpdb->terms ON ($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE
    $wpdb->term_taxonomy.taxonomy = \'TAX_1\'
    AND $wpdb->terms.slug = \'TERM_TAX_1\'
    AND $wpdb->posts.ID IN ( SELECT $wpdb->posts.ID
FROM $wpdb->posts
    INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    INNER JOIN $wpdb->terms ON ($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE
    $wpdb->term_taxonomy.taxonomy = \'TAX_2\'
    AND $wpdb->terms.slug = \'TERM_TAX_2\'
    AND $wpdb->posts.ID IN ( SELECT $wpdb->posts.ID
FROM $wpdb->posts 
    INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    INNER JOIN $wpdb->terms ON ($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE
    $wpdb->posts.post_type = \'MYPOSTTYPE\'
    AND $wpdb->posts.post_status = \'publish\'
    AND $wpdb->term_taxonomy.taxonomy = \'TAX_3\'
    AND $wpdb->terms.slug = \'TERM_TAX_3\'
))

结束
标签： custom-post-types   custom-taxonomy   mysql   wpdb   advanced-taxonomy-queries   小码农  

相关推荐

$wpdb的增量值(Value=Value+1)->更新

此sql查询$wpdb->query(\"UPDATE log SET total=total+1 WHERE id=\'1\')\"); 如何将其转换为$wpdb->update（）语句？例如：。$wpdb->update(\'log\', array(\'total\'=>\'total+1\'), array(\'id\'=>\'1\'));

$wpdb：计算与3个不同分类中的3个术语对应的帖子 - 小码农CODE - 行之有效找到问题解决它

$wpdb：计算与3个不同分类中的3个术语对应的帖子

时间：2012-10-22 作者：stefano1

如果我举个例子就更容易了：

我有一个自定义的帖子类型MYPOSTTYPE, 有3种分类法TAX_1, TAX_2 和TAX_3

如果我有3个术语：“TERM\\u TAX\\u 1”TAX_1, \'TERM\\u TAX\\u 2\'英寸TAX_2, 和中的“TERM\\u TAX\\u 3”TAX_3, 我如何计算有多少MYPOSTTYPE具有SQL查询和$wpdb的所有3个术语？

我试过这样的东西，但没用。。。计数器始终为0

SELECT COUNT($wpdb->posts.ID) AS counter
FROM $wpdb->posts 
    LEFT JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    LEFT JOIN $wpdb->terms AS tax_one ON ($wpdb->term_taxonomy.term_id = tax_one.term_id)
    LEFT JOIN $wpdb->terms AS tax_two ON ($wpdb->term_taxonomy.term_id = tax_two.term_id)
    LEFT JOIN $wpdb->terms AS tax_three ON ($wpdb->term_taxonomy.term_id = tax_three.term_id)
WHERE 1=1
    AND $wpdb->posts.post_type = \'MYPOSTTYPE\'
    AND $wpdb->posts.post_status = \'publish\'
    AND $wpdb->term_taxonomy.taxonomy  IN (\'TAX_1\', \'TAX_2\', \'TAX_3\')
    AND tax_one.slug = \'TERM_TAX_1\' 
    AND tax_two.slug = \'TERM_TAX_1\'
    AND tax_three.slug = \'TERM_TAX_1\'
UPDATE: 我也试过tax_query, 但我有这个问题Problem with get_posts, tax_query and counting the number of posts

所以我想尝试直接查询数据库

2 个回复
SO网友:kaiser

执行 然后count 结果。无需使用几十个JOINS.

$posts = new WP_Query( array(
    \'post_type\' => \'MYPOSTTYPE\', 
    \'tax_query\' => array(     
        array(         
            \'taxonomy\' => \'TAX_1\',
            \'field\'    => \'slug\',         
            \'terms\'    => array( \'TERM_TAX_1\' ),
            \'operator\' => \'IN\'
        ),
        array(
            // etc.
        )
    ),
    \'post_status\' => \'publish\'
);
prinft(
     \'<h3>COUNT: %s</h3>\'
    ,count( $posts )
);

SO网友:Mridul Aggarwal

由于MySQL允许在where子句中嵌套查询，我认为这样的查询应该可以工作

SELECT COUNT($wpdb->posts.ID) AS counter
FROM $wpdb->posts 
    INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    INNER JOIN $wpdb->terms ON ($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE
    $wpdb->term_taxonomy.taxonomy = \'TAX_1\'
    AND $wpdb->terms.slug = \'TERM_TAX_1\'
    AND $wpdb->posts.ID IN ( SELECT $wpdb->posts.ID
FROM $wpdb->posts
    INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    INNER JOIN $wpdb->terms ON ($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE
    $wpdb->term_taxonomy.taxonomy = \'TAX_2\'
    AND $wpdb->terms.slug = \'TERM_TAX_2\'
    AND $wpdb->posts.ID IN ( SELECT $wpdb->posts.ID
FROM $wpdb->posts 
    INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    INNER JOIN $wpdb->terms ON ($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE
    $wpdb->posts.post_type = \'MYPOSTTYPE\'
    AND $wpdb->posts.post_status = \'publish\'
    AND $wpdb->term_taxonomy.taxonomy = \'TAX_3\'
    AND $wpdb->terms.slug = \'TERM_TAX_3\'
))

标签： custom-post-types   custom-taxonomy   mysql   wpdb   advanced-taxonomy-queries   小码农  

相关推荐

如何像使用wpdb一样使用MySQL？

根据标题，我需要使用LIKE操作符以编程方式使用$wpdb删除db上的一行。我想使用$wpdb->delete 消除发生的情况，但我不知道如何设置LIKE 操作员，因为在WHERE 它需要一个指定单个值或另一个数组的数组。我问你这个语法是否正确。$wpdb->delete(\'wp_posts\', array(\'post_type\' => \'flamingo_inbound\', \'post_content\' => \'%\'.$mail.\'%\'));提前感谢