在WooCommerce中标题由字母和数字组成时自定义排序产品

Question

So, I did some digging but wasn't able to find the correct way to do this.

I have some products on WooCommerce (Wordpress), but their title is composed of Letters mixed with numbers. I need those products to be print like this (e.g.):

FNC21000-C
FNC24500-C
FNC232500-C

But, of course, Wordpress gives me:

FNC21000-C
FNC232500-C
FNC24500-C

Is there anyway to achieve this?

I think there's something I might do on WP_Query but I'm really a newbie with PHP, and I know some of the possible fixes are on PHP, some others on MySQL... and the fact that the title is BOTH string and integer, makes me feel clueless on how to write this...

Does anyone has some suggestions?

Below, class-wc-query.php, which I BELIEVE to be the file I need to edit:

/**
 * Returns an array of arguments for ordering products based on the selected values.
 *
 * @access public
 * @return array
 */
public function get_catalog_ordering_args( $orderby = '', $order = '' ) {
    // Get ordering from query string unless defined
    if ( ! $orderby ) {
        $orderby_value = isset( $_GET['orderby'] ) ? wc_clean( $_GET['orderby'] ) : apply_filters( 'woocommerce_default_catalog_orderby', get_option( 'woocommerce_default_catalog_orderby' ) );

        // Get order + orderby args from string
        $orderby_value = explode( '-', $orderby_value );
        $orderby       = esc_attr( $orderby_value[0] );
        $order         = ! empty( $orderby_value[1] ) ? $orderby_value[1] : $order;
    }

    $orderby = strtolower( $orderby );
    $order   = strtoupper( $order );
    $args    = array();

    // default - menu_order
    $args['orderby'] = "strlen('title') => 'ASC', 'title' => 'ASC'";
    //      $args['orderby']  = 'title';
    $args['order']    = $order == 'DESC' ? 'DESC' : 'ASC';
    $args['meta_key'] = '';

    switch ( $orderby ) {
        case 'rand' :
            $args['orderby']  = 'rand';
        break;
        case 'date' :
            $args['orderby']  = 'date ID';
            $args['order']    = $order == 'ASC' ? 'ASC' : 'DESC';
        break;
        case 'price' :
            $args['orderby']  = "meta_value_num ID";
            $args['order']    = $order == 'DESC' ? 'DESC' : 'ASC';
            $args['meta_key'] = '_price';
        break;
        case 'popularity' :
            $args['meta_key'] = 'total_sales';

            // Sorting handled later though a hook
            add_filter( 'posts_clauses', array( $this, 'order_by_popularity_post_clauses' ) );
        break;
        case 'rating' :
            // Sorting handled later though a hook
            add_filter( 'posts_clauses', array( $this, 'order_by_rating_post_clauses' ) );
        break;
        case 'title' :
        $args['orderby'] = "strlen('title') => 'ASC', 'title' => 'ASC'";
//$args['orderby']  = 'meta_value meta_value_num title';
            $args['order']    = $order == 'DESC' ? 'DESC' : 'ASC';
        break;
    }

    return apply_filters( 'woocommerce_get_catalog_ordering_args', $args );
}

Answer 1

In PHP, simply call natsort().

Code: (Demo)

$array=['FND100000-C','FNC24500-C','FNC232500-C','FMC30000-C','FNC21000-C'];
natsort($array);
var_export($array);

Output:

array (
  3 => 'FMC30000-C',
  4 => 'FNC21000-C',
  1 => 'FNC24500-C',
  2 => 'FNC232500-C',
  0 => 'FND100000-C',
)

In MYSQL, as long as the format is...

• 3 letters then
• a series of numbers then
• a hyphen then
• one or more characters

...you can order by calling SUBSTRING() and SUBSTRING_INDEX()

Table & Query: (Demo)

CREATE TABLE `test` (
  `PrdID` varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `test` (`PrdID`) VALUES
('FMC30000-C'),
('FNC21000-B'),
('FNC21000-C'),
('FNC232500-C'),
('FNC24500-C'),
('FND100000-C');

ALTER TABLE `test`
  ADD PRIMARY KEY (`PrdID`);

SELECT PrdID,
       SUBSTRING(PrdID,1,3),
       SUBSTRING(SUBSTRING_INDEX(PrdID,'-',1),4),
       SUBSTRING_INDEX(PrdID,'-',-1)
FROM `test`
ORDER BY
    SUBSTRING(PrdID,1,3),
    CAST(SUBSTRING(SUBSTRING_INDEX(PrdID,'-',1),4) AS UNSIGNED),
    SUBSTRING_INDEX(PrdID,'-',-1)

Output:

PrdID       | SUBSTRING(PrdID,1,3) | SUBSTRING(SUBSTRING_INDEX(PrdID,'-',1),4) | SUBSTRING_INDEX(PrdID,'-',-1)
----------------------------------------------------------------------------------------------------------------
FMC30000-C  |         FMC          |                  30000                    |               C
FNC21000-B  |         FNC          |                  21000                    |               B
FNC21000-C  |         FNC          |                  21000                    |               C
FNC24500-C  |         FNC          |                  24500                    |               C
FNC232500-C |         FNC          |                  232500                   |               C
FND100000-C |         FND          |                  100000                   |               C

---

Here's a shot in the dark from a non-WordPress developer...

$args['orderby'] = "substr('title',0,3) => 'ASC', substr(strstr('title','-',true),3) => 'ASC', substr('title',strpos('title','-')+1) => 'ASC'";

Demo

After looking at: https://github.com/woocommerce/woocommerce/blob/master/includes/class-wc-query.php it looks like the orderby components are just space delimited, not comma delimited. Try this:

$args['orderby'] = "substr('title',0,3) substr(strstr('title','-',true),3) substr('title',strpos('title','-')+1)";
$args['order'] = $order==='DESC'?'DESC':'ASC';