针对自定义发布类型的自定义SQL查询。按分类排序?

时间:2012-09-27 作者:John King

我创建了一个名为\'publications\' 还有一种自定义分类法\'topics\'. 我还使用了\'category\'.

我的自定义查询确保它获取所有\'publications\' 都是正确的\'category\' 但我也希望ORDER BY 额外的\'topics\' 分类学

此自定义查询获取所有正确的\'publications\' 但我的运气不好ORDER BY 第节:

$querystr = "
    SELECT * 
    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 ON ($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
    WHERE $wpdb->posts.post_type = \'publications\'
    AND $wpdb->terms.slug = %s
    AND $wpdb->term_taxonomy.taxonomy = \'category\'
    ORDER BY $wpdb->term_taxonomy.taxonomy = \'topics\' DESC
";
$pageposts = $wpdb->get_results($wpdb->prepare($querystr, array($parent_post_slug)));
The$parent_post_slug\'category\' 名称它正在获取所有正确的帖子。我该如何按照所谓的分类法对它们进行排序\'topics\'?

我想要的订单示例:

类别名称=小说(此页面仅显示小说出版物)
出版物1=具有鳄鱼的自定义分类主题
出版物2=具有鳄鱼的自定义分类主题
出版物3=具有羚羊的自定义分类主题
出版物4=具有水牛的自定义分类主题
出版物5=具有水牛的自定义分类主题

你知道我应该用什么吗ORDER BY 排队让它工作?

2 个回复
最合适的回答,由SO网友:John King 整理而成

最后,我成功地做到了这一点。我发现了通过自定义分类法和分页来排序自定义帖子类型的圣杯。代码并不漂亮,但很有效。

我的方法是忘记SQL查询,只选择与正确类别和正确自定义帖子类型匹配的所有自定义帖子(我的类别取自当前页面slug,即foo.com/bar“bar”是我的类别)。

然后从包含每个帖子ID、自定义分类(“主题”)术语和自定义分类Slug的结果创建一个自定义数组。

然后对该数组进行排序。然后根据您所在的页面(即第1页、第2页、第3页)对该数组进行切片。我们只是选择要在该页面上显示的出版物。然后循环结果。

我只需检查是否在上一篇文章中打印出了相同的自定义分类术语,就可以将我的结果“分组”到这些“主题”中。

所有分页都是使用顶部附近的$current\\u paged\\u num代码和底部的paginate links代码完成的。

是的,我的代码很难看,可能占用了大量的资源,但它确实有效。所以我在这里分享它,以防我能帮助其他人。如果您认为您可以整理或美化此代码,请在此处向我们展示。

<?php // Run new query on Publications custom post type

        // Appologies for complexity but this is the only way to do this in wordpress

        $posts_per_page = 6; // Set number of Posts per page

        $parent_post_data = get_post($post->post_parent);
        $parent_post_slug = $parent_post_data->post_name; // Get Parent Page Name to find the relevant Stakeholder section

        $current_paged_num = 0;
        $current_paged_num = intval(get_query_var(\'paged\')); // Find current Pagination Page number

        if (($current_paged_num) > 0) { // Calculate offset so that the correct posts are fetched from the database
            $offset = (($current_paged_num-1) * $posts_per_page);
        } else {
            $offset = 0;
            $current_paged_num = 1;
        }

        $query = new WP_Query("post_type=publications&category_name=$parent_post_slug&showposts=-1"); // Get ALL posts for this section

        $total = $query->post_count; // Calculate total number of posts

        if ($total > 0) { // If we find relevant posts

            $x = 0; // Setup Array numbers

            while($query->have_posts()): $query->next_post(); // Create new array containing Post IDs and Topic slugs

                $customTermSlug = \'unclassified\';
                $customTermName = \'Unclassified\';

                $new_terms = get_the_terms( $query->post->ID, \'topics\' );

                if ($new_terms) {

                    foreach ($new_terms as $term) {
                        $customTermSlug = $term->slug;
                        $customTermName = $term->name;
                        break;
                    };

                };

                $new_array[$x][customID] = $query->post->ID;
                $new_array[$x][customTermSlug] = $customTermSlug;
                $new_array[$x][customTermName] = $customTermName;

                $x++;

            endwhile;

            function subval_sort($a,$subkey) { // Sort array by Topic slug
                foreach($a as $k=>$v) {
                    $b[$k] = strtolower($v[$subkey]);
                }
                asort($b);
                foreach($b as $key=>$val) {
                    $c[] = $a[$key];
                }
                return $c;
            }
            $ordered_array = subval_sort($new_array, \'customTermSlug\');

            $filtered_array = array_slice($ordered_array, $offset, $posts_per_page); // Slice (filter) the array to remove all unneccessary items

            if ($filtered_array): ?>

            <section class="article-list">

            <?php foreach ($filtered_array as $item) { 

                $postID = $item[customID]; // Set up item variables
                $customTermName = $item[customTermName];

                <article class="clearfix">

                    <?php $post_array = get_post($postID); ?>

                    <?php if ($customTermName != $previousTermName) { ?>

                        <h3><?php echo $customTermName; ?></h3>

                    <?php } ?>          

                    <h4><?php echo $post_array->post_title; ?></h4>

                    <?php echo apply_filters(\'the_content\', $post_array->post_content); ?>

                    <?php $previousTermName = $customTermName; ?>

                </article>

            <?php } ?>

            </section>

            <div class="pager">

                <?php // Paginate WP using method http://wordpress.stackexchange.com/questions/43489/paginate-custom-post-type-page

                $big = 999999999; // need an unlikely integer 

                echo paginate_links( array(
                    \'base\' => str_replace( $big, \'%#%\', get_pagenum_link( $big ) ),
                    \'format\' => \'?paged=%#%\',
                    \'current\' => max( 1, get_query_var(\'paged\') ),
                    \'total\' => ceil($total / $posts_per_page),
                    \'prev_text\' => __(\'Previous | \'),
                    \'next_text\' => __(\' | Next\')
                )); ?> 

            </div>

            <?php endif; 

            wp_reset_query();

        } ?>

SO网友:Jonathan

只需查看term\\u taxonomy表,就可以正确地选择所有内容,但最后一部分:

WHERE $wpdb->posts.post_type = \'publications\'
AND $wpdb->terms.slug = %s
AND $wpdb->term_taxonomy.taxonomy = \'category\'
ORDER BY $wpdb->term_taxonomy.taxonomy = \'topics\' DESC
从post\\u类型的发布中,术语slug为%s,并且具有类别的分类法,但您永远不会选择分类法主题。如果查看该表,您有一列正在读取分类法。在此列中,您可以有类别或主题。这是两行(加上标题)的示例:

<tr>
    <td>term_taxonomy_id</td>
    <td>term_id</td>
    <td>taxonomy</td>
    <td>description</td>
    <td>parent</td>
    <td>count</td>
</tr>
<tr>
    <td>1</td>
    <td>1</td>
    <td>category</td>
    <td>This is the description for the category taxonomy</td>
    <td>0</td>
    <td>1</td>
</tr>
<tr>
    <td>1</td>
    <td>1</td>
    <td>topics</td>
    <td>This is the description for the topics taxonomy</td>
    <td>0</td>
    <td>1</td>
</tr>
(为了更容易表达,我把它放在了表格中)

尝试将主题放入select查询,然后按其排序:

WHERE $wpdb->posts.post_type = \'publications\'
AND $wpdb->terms.slug = %s
AND $wpdb->term_taxonomy.taxonomy = \'category\'
AND $wpdb->term_taxonomy.taxonomy = \'topics\'
ORDER BY $wpdb->term_taxonomy.taxonomy = \'topics\' DESC

结束

相关推荐

针对自定义发布类型的自定义SQL查询。按分类排序? - 小码农CODE - 行之有效找到问题解决它

针对自定义发布类型的自定义SQL查询。按分类排序?

时间:2012-09-27 作者:John King

我创建了一个名为\'publications\' 还有一种自定义分类法\'topics\'. 我还使用了\'category\'.

我的自定义查询确保它获取所有\'publications\' 都是正确的\'category\' 但我也希望ORDER BY 额外的\'topics\' 分类学

此自定义查询获取所有正确的\'publications\' 但我的运气不好ORDER BY 第节:

$querystr = "
    SELECT * 
    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 ON ($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
    WHERE $wpdb->posts.post_type = \'publications\'
    AND $wpdb->terms.slug = %s
    AND $wpdb->term_taxonomy.taxonomy = \'category\'
    ORDER BY $wpdb->term_taxonomy.taxonomy = \'topics\' DESC
";
$pageposts = $wpdb->get_results($wpdb->prepare($querystr, array($parent_post_slug)));
The$parent_post_slug\'category\' 名称它正在获取所有正确的帖子。我该如何按照所谓的分类法对它们进行排序\'topics\'?

我想要的订单示例:

类别名称=小说(此页面仅显示小说出版物)
出版物1=具有鳄鱼的自定义分类主题
出版物2=具有鳄鱼的自定义分类主题
出版物3=具有羚羊的自定义分类主题
出版物4=具有水牛的自定义分类主题
出版物5=具有水牛的自定义分类主题

你知道我应该用什么吗ORDER BY 排队让它工作?

2 个回复
最合适的回答,由SO网友:John King 整理而成

最后,我成功地做到了这一点。我发现了通过自定义分类法和分页来排序自定义帖子类型的圣杯。代码并不漂亮,但很有效。

我的方法是忘记SQL查询,只选择与正确类别和正确自定义帖子类型匹配的所有自定义帖子(我的类别取自当前页面slug,即foo.com/bar“bar”是我的类别)。

然后从包含每个帖子ID、自定义分类(“主题”)术语和自定义分类Slug的结果创建一个自定义数组。

然后对该数组进行排序。然后根据您所在的页面(即第1页、第2页、第3页)对该数组进行切片。我们只是选择要在该页面上显示的出版物。然后循环结果。

我只需检查是否在上一篇文章中打印出了相同的自定义分类术语,就可以将我的结果“分组”到这些“主题”中。

所有分页都是使用顶部附近的$current\\u paged\\u num代码和底部的paginate links代码完成的。

是的,我的代码很难看,可能占用了大量的资源,但它确实有效。所以我在这里分享它,以防我能帮助其他人。如果您认为您可以整理或美化此代码,请在此处向我们展示。

<?php // Run new query on Publications custom post type

        // Appologies for complexity but this is the only way to do this in wordpress

        $posts_per_page = 6; // Set number of Posts per page

        $parent_post_data = get_post($post->post_parent);
        $parent_post_slug = $parent_post_data->post_name; // Get Parent Page Name to find the relevant Stakeholder section

        $current_paged_num = 0;
        $current_paged_num = intval(get_query_var(\'paged\')); // Find current Pagination Page number

        if (($current_paged_num) > 0) { // Calculate offset so that the correct posts are fetched from the database
            $offset = (($current_paged_num-1) * $posts_per_page);
        } else {
            $offset = 0;
            $current_paged_num = 1;
        }

        $query = new WP_Query("post_type=publications&category_name=$parent_post_slug&showposts=-1"); // Get ALL posts for this section

        $total = $query->post_count; // Calculate total number of posts

        if ($total > 0) { // If we find relevant posts

            $x = 0; // Setup Array numbers

            while($query->have_posts()): $query->next_post(); // Create new array containing Post IDs and Topic slugs

                $customTermSlug = \'unclassified\';
                $customTermName = \'Unclassified\';

                $new_terms = get_the_terms( $query->post->ID, \'topics\' );

                if ($new_terms) {

                    foreach ($new_terms as $term) {
                        $customTermSlug = $term->slug;
                        $customTermName = $term->name;
                        break;
                    };

                };

                $new_array[$x][customID] = $query->post->ID;
                $new_array[$x][customTermSlug] = $customTermSlug;
                $new_array[$x][customTermName] = $customTermName;

                $x++;

            endwhile;

            function subval_sort($a,$subkey) { // Sort array by Topic slug
                foreach($a as $k=>$v) {
                    $b[$k] = strtolower($v[$subkey]);
                }
                asort($b);
                foreach($b as $key=>$val) {
                    $c[] = $a[$key];
                }
                return $c;
            }
            $ordered_array = subval_sort($new_array, \'customTermSlug\');

            $filtered_array = array_slice($ordered_array, $offset, $posts_per_page); // Slice (filter) the array to remove all unneccessary items

            if ($filtered_array): ?>

            <section class="article-list">

            <?php foreach ($filtered_array as $item) { 

                $postID = $item[customID]; // Set up item variables
                $customTermName = $item[customTermName];

                <article class="clearfix">

                    <?php $post_array = get_post($postID); ?>

                    <?php if ($customTermName != $previousTermName) { ?>

                        <h3><?php echo $customTermName; ?></h3>

                    <?php } ?>          

                    <h4><?php echo $post_array->post_title; ?></h4>

                    <?php echo apply_filters(\'the_content\', $post_array->post_content); ?>

                    <?php $previousTermName = $customTermName; ?>

                </article>

            <?php } ?>

            </section>

            <div class="pager">

                <?php // Paginate WP using method http://wordpress.stackexchange.com/questions/43489/paginate-custom-post-type-page

                $big = 999999999; // need an unlikely integer 

                echo paginate_links( array(
                    \'base\' => str_replace( $big, \'%#%\', get_pagenum_link( $big ) ),
                    \'format\' => \'?paged=%#%\',
                    \'current\' => max( 1, get_query_var(\'paged\') ),
                    \'total\' => ceil($total / $posts_per_page),
                    \'prev_text\' => __(\'Previous | \'),
                    \'next_text\' => __(\' | Next\')
                )); ?> 

            </div>

            <?php endif; 

            wp_reset_query();

        } ?>

SO网友:Jonathan

只需查看term\\u taxonomy表,就可以正确地选择所有内容,但最后一部分:

WHERE $wpdb->posts.post_type = \'publications\'
AND $wpdb->terms.slug = %s
AND $wpdb->term_taxonomy.taxonomy = \'category\'
ORDER BY $wpdb->term_taxonomy.taxonomy = \'topics\' DESC
从post\\u类型的发布中,术语slug为%s,并且具有类别的分类法,但您永远不会选择分类法主题。如果查看该表,您有一列正在读取分类法。在此列中,您可以有类别或主题。这是两行(加上标题)的示例:

<tr>
    <td>term_taxonomy_id</td>
    <td>term_id</td>
    <td>taxonomy</td>
    <td>description</td>
    <td>parent</td>
    <td>count</td>
</tr>
<tr>
    <td>1</td>
    <td>1</td>
    <td>category</td>
    <td>This is the description for the category taxonomy</td>
    <td>0</td>
    <td>1</td>
</tr>
<tr>
    <td>1</td>
    <td>1</td>
    <td>topics</td>
    <td>This is the description for the topics taxonomy</td>
    <td>0</td>
    <td>1</td>
</tr>
(为了更容易表达,我把它放在了表格中)

尝试将主题放入select查询,然后按其排序:

WHERE $wpdb->posts.post_type = \'publications\'
AND $wpdb->terms.slug = %s
AND $wpdb->term_taxonomy.taxonomy = \'category\'
AND $wpdb->term_taxonomy.taxonomy = \'topics\'
ORDER BY $wpdb->term_taxonomy.taxonomy = \'topics\' DESC

相关推荐