在排序查询时忽略开头的冠词(如‘a’、‘an’或‘the’)?

时间:2016-02-07 作者:rpbtz

我目前正在尝试输出一个音乐标题列表,并希望排序忽略(但仍显示)标题的初始文章。

例如,如果我有一个乐队列表,它将按字母顺序显示在WordPress中,如下所示:

黑色安息日,我想让它按字母顺序显示,而忽略首字母“The”,如下所示:

披头士乐队黑色安息日乐队齐柏林飞艇乐队粉红弗洛伊德滚石乐队我在a blog entry from last year, 这表明以下代码functions.php:

function wpcf_create_temp_column($fields) {
  global $wpdb;
  $matches = \'The\';
  $has_the = " CASE 
      WHEN $wpdb->posts.post_title regexp( \'^($matches)[[:space:]]\' )
        THEN trim(substr($wpdb->posts.post_title from 4)) 
      ELSE $wpdb->posts.post_title 
        END AS title2";
  if ($has_the) {
    $fields .= ( preg_match( \'/^(\\s+)?,/\', $has_the ) ) ? $has_the : ", $has_the";
  }
  return $fields;
}

function wpcf_sort_by_temp_column ($orderby) {
  $custom_orderby = " UPPER(title2) ASC";
  if ($custom_orderby) {
    $orderby = $custom_orderby;
  }
  return $orderby;
}
然后将查询包装为add_filter 之前和remove_filter 之后

我已尝试过此操作,但在我的网站上不断出现以下错误:

WordPress数据库错误:[“order子句”中的未知列“title2”]

选择wp\\U帖子。*来自wp\\U POST,其中1=1和wp\\U POST。post\\U类型=“发布”和(wp\\U posts.post\\U状态=“发布”或wp\\U posts.post\\U状态=“私有”)按上级(标题2)ASC排序

我不会撒谎,我对WordPress的php部分还很陌生,所以我不确定为什么会出现这个错误。我可以看出这与“title2”列有关,但我的理解是,第一个函数应该处理这个问题。此外,如果有更聪明的方法,我洗耳恭听。我一直在谷歌上搜索这个网站,但我还没有找到很多解决方案。

如果有任何帮助,我使用过滤器的代码如下所示:

<?php 
    $args_post = array(\'post_type\' => \'release\', \'orderby\' => \'title\', \'order\' => \'ASC\', \'posts_per_page\' => -1, );

    add_filter(\'post_fields\', \'wpcf_create_temp_column\'); /* remove initial \'The\' from post titles */
    add_filter(\'posts_orderby\', \'wpcf_sort_by_temp_column\');

    $loop = new WP_Query($args_post);

    remove_filter(\'post_fields\', \'wpcf_create_temp_column\');
    remove_filter(\'posts_orderby\', \'wpcf_sort_by_temp_column\');

        while ($loop->have_posts() ) : $loop->the_post();
?>

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

问题出在这里,我认为有一个输入错误:

筛选器的名称为posts_fieldspost_fields.

这可以解释为什么title2 字段未知,因为它的定义未添加到生成的SQL字符串中。

备选方案-单个过滤器

我们可以将其重写为仅使用单个过滤器:

add_filter( \'posts_orderby\', function( $orderby, \\WP_Query $q )
{
    // Do nothing
    if( \'_custom\' !== $q->get( \'orderby\' ) )
        return $orderby;

    global $wpdb;

    $matches = \'The\';   // REGEXP is not case sensitive here

    // Custom ordering (SQL)
    return sprintf( 
        " 
        CASE 
            WHEN {$wpdb->posts}.post_title REGEXP( \'^($matches)[[:space:]]+\' )
                THEN TRIM( SUBSTR( {$wpdb->posts}.post_title FROM %d )) 
            ELSE {$wpdb->posts}.post_title 
        END %s
        ",
        strlen( $matches ) + 1,
        \'ASC\' === strtoupper( $q->get( \'order\' ) ) ? \'ASC\' : \'DESC\'     
    );

}, 10, 2 );
现在,您可以使用激活自定义订购_custom orderby参数:

$args_post = array
    \'post_type\'      => \'release\', 
    \'orderby\'        => \'_custom\',    // Activate the custom ordering 
    \'order\'          => \'ASC\', 
    \'posts_per_page\' => -1, 
);

$loop = new WP_Query($args_post);

while ($loop->have_posts() ) : $loop->the_post();
备选方案-递归TRIM()让我们通过以下方式实现递归思想Pascal Birchler, commented here:

add_filter( \'posts_orderby\', function( $orderby, \\WP_Query $q )
{
    if( \'_custom\' !== $q->get( \'orderby\' ) )
        return $orderby;

    global $wpdb;

    // Adjust this to your needs:
    $matches = [ \'the \', \'an \', \'a \' ];

    return sprintf( 
        " %s %s ",
        wpse_sql( $matches, " LOWER( {$wpdb->posts}.post_title) " ),
        \'ASC\' === strtoupper( $q->get( \'order\' ) ) ? \'ASC\' : \'DESC\'     
    );

}, 10, 2 );
例如,我们可以将递归函数构造为:

function wpse_sql( &$matches, $sql )
{
    if( empty( $matches ) || ! is_array( $matches ) )
        return $sql;

    $sql = sprintf( " TRIM( LEADING \'%s\' FROM ( %s ) ) ", $matches[0], $sql );
    array_shift( $matches );    
    return wpse_sql( $matches, $sql );
}
这意味着

$matches = [ \'the \', \'an \', \'a \' ];
echo wpse_sql( $matches, " LOWER( {$wpdb->posts}.post_title) " );
将生成:

TRIM( LEADING \'a \' FROM ( 
    TRIM( LEADING \'an \' FROM ( 
        TRIM( LEADING \'the \' FROM ( 
            LOWER( wp_posts.post_title) 
        ) )
    ) )
) )
备选方案-MariaDB通常我喜欢使用MariaDB而不是MySQL。这样就容易多了,因为MariaDB 10.0.5supports REGEXP_REPLACE:

/**
 * Ignore (the,an,a) in post title ordering
 *
 * @uses MariaDB 10.0.5+
 */
add_filter( \'posts_orderby\', function( $orderby, \\WP_Query $q )
{
    if( \'_custom\' !== $q->get( \'orderby\' ) )
        return $orderby;

    global $wpdb;
    return sprintf( 
        " REGEXP_REPLACE( {$wpdb->posts}.post_title, \'^(the|a|an)[[:space:]]+\', \'\' ) %s",
        \'ASC\' === strtoupper( $q->get( \'order\' ) ) ? \'ASC\' : \'DESC\'     
    );
}, 10, 2 );

SO网友:majick

一种更简单的方法可能是在需要的帖子上(在帖子写作屏幕的标题下)浏览并更改permalink slug,然后将其用于排序,而不是标题。

即使用post_namepost_title 用于排序。。。

这也意味着,如果在永久链接结构中使用%postname%,您的永久链接可能会有所不同,这可能是一个额外的好处。

例如,给出http://example.com/rolling-stones/http://example.com/the-rolling-stones/

EDIT: 用于更新现有slug的代码,从中删除不需要的前缀post_name

global $wpdb;
$posttype = \'release\';
$stripprefixes = array(\'a-\',\'an-\',\'the-\');

$results = $wpdb->get_results("SELECT ID, post_name FROM ".$wpdb->prefix."posts" WHERE post_type = \'".$posttype."\' AND post_status = \'publish\');
if (count($results) > 0) {
    foreach ($results as $result) {
        $postid = $result->ID;
        $postslug = $result->post_name;
        foreach ($stripprefixes as $stripprefix) {
            $checkprefix = strtolower(substr($postslug,0,strlen($stripprefix));
            if ($checkprefix == $stripprefix) {
                $newslug = substr($postslug,strlen($stripprefix),strlen($postslug));
                // echo $newslug; // debug point
                $query = $wpdb->prepare("UPDATE ".$wpdb->prefix."posts SET post_name = \'%s\' WHERE ID = \'%d\'", $newslug, $postid);
                $wpdb->query($query);
            }
        }
    }
}

SO网友:Pieter Goosen

编辑我对代码做了一些改进。所有代码块都会相应更新。在跳转到ORIGINAL ANSWER, 我已经设置了用于以下操作的代码

自定义帖子类型->release

自定义分类法->game

确保根据您的需要进行设置

原始答案

除了其他答案和@birgire指出的拼写错误之外,还有另一种方法。

首先,我们将标题设置为隐藏的自定义字段,但我们将首先删除以下单词the 我们想要排除的。在此之前,我们需要首先创建一个helper函数,以便从术语名称和帖子标题中删除禁用的单词

/**
 * Function get_name_banned_removed()
 *
 * A helper function to handle removing banned words
 * 
 * @param string $tring  String to remove banned words from
 * @param array  $banned Array of banned words to remove
 * @return string $string
 */
function get_name_banned_removed( $string = \'\', $banned = [] )
{
    // Make sure we have a $string to handle
    if ( !$string )
        return $string;

    // Sanitize the string
    $string = filter_var( $string, FILTER_SANITIZE_STRING );

    // Make sure we have an array of banned words
    if (    !$banned
         || !is_array( $banned )
    )
        return $string; 

    // Make sure that all banned words is lowercase
    $banned = array_map( \'strtolower\', $banned );

    // Trim the string and explode into an array, remove banned words and implode
    $text          = trim( $string );
    $text          = strtolower( $text );
    $text_exploded = explode( \' \', $text );

    if ( in_array( $text_exploded[0], $banned ) )
        unset( $text_exploded[0] );

    $text_as_string = implode( \' \', $text_exploded );

    return $string = $text_as_string;
}
现在我们已经讨论了这一点,让我们看看设置自定义字段的代码片段。你必须remove 只要加载一次任何页面,就可以完全删除此代码。如果你有一个拥有大量帖子的大型网站,你可以设置posts_per_page 到某物100 并运行脚本几次,直到所有帖子的自定义字段都设置为所有帖子

add_action( \'wp\', function ()
{
    add_filter( \'posts_fields\', function ( $fields, \\WP_Query $q ) 
    {
        global $wpdb;

        remove_filter( current_filter(), __FUNCTION__ );

        // Only target a query where the new custom_query parameter is set with a value of custom_meta_1
        if ( \'custom_meta_1\' === $q->get( \'custom_query\' ) ) {
            // Only get the ID and post title fields to reduce server load
            $fields = "$wpdb->posts.ID, $wpdb->posts.post_title";
        }

        return $fields;
    }, 10, 2);

    $args = [
        \'post_type\'        => \'release\',       // Set according to needs
        \'posts_per_page\'   => -1,              // Set to execute smaller chucks per page load if necessary
        \'suppress_filters\' => false,           // Allow the posts_fields filter
        \'custom_query\'     => \'custom_meta_1\', // New parameter to allow that our filter only target this query
        \'meta_query\'       => [
            [
                \'key\'      => \'_custom_sort_post_title\', // Make it a hidden custom field
                \'compare\'  => \'NOT EXISTS\'
            ]
        ]
    ];
    $q = get_posts( $args );

    // Make sure we have posts before we continue, if not, bail
    if ( !$q ) 
        return;

    foreach ( $q as $p ) {
        $new_post_title = strtolower( $p->post_title );

        if ( function_exists( \'get_name_banned_removed\' ) )
            $new_post_title = get_name_banned_removed( $new_post_title, [\'the\'] );

        // Set our custom field value
        add_post_meta( 
            $p->ID,                    // Post ID
            \'_custom_sort_post_title\', // Custom field name
            $new_post_title            // Custom field value
        );  
    } //endforeach $q
});
既然自定义字段已设置为所有帖子,并且上面的代码已删除,那么我们需要确保将此自定义字段设置为所有新帖子,或者在更新帖子标题时设置。为此,我们将使用transition_post_status 钩以下代码可以进入插件(,我推荐)或functions.php

add_action( \'transition_post_status\', function ( $new_status, $old_status, $post )
{
    // Make sure we only run this for the release post type
    if ( \'release\' !== $post->post_type )
        return;

    $text = strtolower( $post->post_title );   

    if ( function_exists( \'get_name_banned_removed\' ) )
        $text = get_name_banned_removed( $text, [\'the\'] );

    // Set our custom field value
    update_post_meta( 
        $post->ID,                 // Post ID
        \'_custom_sort_post_title\', // Custom field name
        $text                      // Custom field value
    );
}, 10, 3 );
查询帖子您可以正常运行查询,无需任何自定义过滤器。您可以按如下方式查询和排序您的帖子

$args_post = [
    \'post_type\'      => \'release\', 
    \'orderby\'        => \'meta_value\', 
    \'meta_key\'       => \'_custom_sort_post_title\',
    \'order\'          => \'ASC\', 
    \'posts_per_page\' => -1, 
];
$loop = new WP_Query( $args );

SO网友:Yedidel Elhayany

当仅按此字段排序时,birgire的答案很有效。我做了一些修改,以使它在按多个字段排序时正常工作(我不确定当标题排序是主要排序时它是否正常工作):

add_filter( \'posts_orderby\', function( $orderby, \\WP_Query $q )
{
// Do nothing
if( \'_custom\' !== $q->get( \'orderby\' ) && !isset($q->get( \'orderby\' )[\'_custom\']) )
    return $orderby;

global $wpdb;

$matches = \'The\';   // REGEXP is not case sensitive here

// Custom ordering (SQL)
if (is_array($q->get( \'orderby\' ))) {
    return sprintf( 
        " $orderby, 
        CASE 
            WHEN {$wpdb->posts}.post_title REGEXP( \'^($matches)[[:space:]]+\' )
                THEN TRIM( SUBSTR( {$wpdb->posts}.post_title FROM %d )) 
            ELSE {$wpdb->posts}.post_title 
        END %s
        ",
        strlen( $matches ) + 1,
        \'ASC\' === strtoupper( $q->get( \'orderby\' )[\'_custom\'] ) ? \'ASC\' : \'DESC\'     
    );
}
else {
    return sprintf( 
        "
        CASE 
            WHEN {$wpdb->posts}.post_title REGEXP( \'^($matches)[[:space:]]+\' )
                THEN TRIM( SUBSTR( {$wpdb->posts}.post_title FROM %d )) 
            ELSE {$wpdb->posts}.post_title 
        END %s
        ",
        strlen( $matches ) + 1,
        \'ASC\' === strtoupper( $q->get( \'order\' ) ) ? \'ASC\' : \'DESC\'     
    );
}

}, 10, 2 );

相关推荐

WooCommerce sort by SKU

所以我试过了https://gist.github.com/bekarice/1883b7e678ec89cc8f4d 在我的网站上。只要所有产品都有SKU,效果就很好。如果产品没有SKU,则根本不会显示。也就是说,是否有其他方法可以按SKU排序,但不要求产品实际具有SKU?