WP_Queryでカンマが入った数値のカスタムフィールドをカンマなしでソートしたい

Question

いつもお世話になっております。最近wordpressもphpも皆様のおかげでだいぶ慣れたきました。

今回、お知恵を拝借したいのは、要約した箇条書きにすると以下のようになります。

1. 顧客のサーバーにカスタム投稿タイプで商品が登録されている
2. その商品にはカスタムフィールドに値段が設定されているが、他の処理で必要なためカンマ区切りの金額になっている
3. WP_Queryで値段でソートして全件取得したところ、カンマ前までの数値でソートされており、12,200 12,800 12,300のように値段順にきちんとソートされない
4. 商品数が多いのと他の処理ではカンマが必要なためデータの変更は出来ない
5. どうにかしてカンマが入ってても12,200 12,300 12,800のようにソートしたい

追記３　ついに成功・解決したコードと方法論

いただいたアドバイスとグーグル先生に聞きまくってついに希望通りの結果が得られましたので記載します。

クエリーを投げてる部分、このままでは値段順に並ばない。

$args = array(
    'post_type' => 'product', // 取得する投稿タイプのスラッグ,
	'posts_per_page' => 18,
	'paged' => $paged,
	'orderby' => 'meta_value_num',
	'order' => 'DESC',
	'meta_key' => '値段',
	'post_status' => 'publish',
			);
	$wp_query = new WP_Query( $args );

アドバイスをいただいてposts_requestをフックしてSQLを書き換えるコード

add_filter('posts_request', function ($query) {
	global $template;

	if( basename($template) != "search.php" ) {
		return $query;
	}
	
	//echo "<br>" . $query . "<br>";

	$pos3 = mb_strrpos( $query, "L" );	
	$queryLimit = mb_substr( $query, $pos3 );
	
	//echo "<br>" . $queryLimit . "<br>";	
	
	//echo "<br>" . "enter overwite query" . "<br>";
	$query = 
		"
		SELECT SQL_CALC_FOUND_ROWS wp_posts.* 
		FROM wp_posts 
		INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
		INNER JOIN (
			SELECT meta_id, post_id, meta_key, CAST(REPLACE(meta_value,',','') AS DECIMAL) AS meta_value_no_comma  
			FROM wp_postmeta 
			WHERE  wp_postmeta.meta_key = '値段' 
			ORDER BY `meta_value_no_comma` DESC ) AS wp_postmeta2 ON ( wp_posts.ID = wp_postmeta2.post_id) 
		WHERE 1=1 AND ( wp_postmeta.meta_key = '値段' ) AND wp_posts.post_type = 'product' AND ((wp_posts.post_status = 'publish'))
		GROUP BY wp_posts.ID 
		ORDER BY `meta_value_no_comma` DESC " . $queryLimit;
	
	//echo "<br>" . $query . "<br>";	
	
	return $query;
});

これで思い通りの結果を得られました。キモとなったのは別名に対して ` バッククオートで囲めば別名で使用できるということでした。

SELECT SQL_CALC_FOUND_ROWS wp_posts.* 
FROM wp_posts 
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
INNER JOIN ( SELECT meta_id, post_id, meta_key, CAST(REPLACE(meta_value,',','') AS DECIMAL) AS meta_value_no_comma 
   FROM wp_postmeta 
   WHERE wp_postmeta.meta_key = '値段' 
   ORDER BY `meta_value_no_comma` DESC ) AS wp_postmeta2 
   ON ( wp_posts.ID = wp_postmeta2.post_id) 
WHERE 1=1 AND ( wp_postmeta.meta_key = '値段' ) 
AND wp_posts.post_type = 'product' 
AND ((wp_posts.post_status = 'publish')) 
GROUP BY wp_posts.ID 
ORDER BY `meta_value_no_comma` DESC 
LIMIT 0, 18
```参考になったリンクも記載しておきます

[MySQL の ORDER BY 句でカラムの別名が使えない](https://techblog.recochoku.jp/2723)

[MySqlのデベロッパーページの別名の使用方法](https://dev.mysql.com/doc/refman/5.6/ja/problems-with-alias.html)

---

### 追記２　さらにアドバイスをいただいて試したこと
KazuhiroHatano様がしきりにJOINして別名とおっしゃていたのが何となく引っかかっていて、progateではwhereにしかサブクエリを書くものがなかったので
whereにしか書けないのかと、思い込んでいましたが、再度のアドバイスでも同じことをおっしゃってらしたので、
もしや・・・と思い。グーグルで```SQL JOIN サブクエリ```で検索かけたらHITして**JOINでも使えるじゃん！**と分かり。
**SQLを書き直しました。が、しかし、今度は別のエラーメッセージが表示されるようになりました。**

再度書き直したSQL
```SQL
SELECT SQL_CALC_FOUND_ROWS wp_posts.* 
FROM wp_posts 
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
INNER JOIN (
    SELECT post_id, meta_key, CAST(REPLACE(meta_value,',','') AS DECIMAL)  
    FROM wp_postmeta 
    WHERE 1=1 AND ( wp_postmeta.meta_key = '値段' ) AND wp_posts.post_type = 'product' AND ((wp_posts.post_status = 'publish'))
    )  AS wp_postmeta2
WHERE 1=1 AND ( wp_postmeta.meta_key = '値段' ) AND wp_posts.post_type = 'product' AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID 
ORDER BY wp_postmeta2.meta_value+0 DESC LIMIT 0, 18"

新たに出てきたエラーメッセージはこちらです。

Unknown column 'wp_posts.post_type' in 'where clause'

申し訳ありませんが、再度アドバイスをいただけたら幸いです。

追記　アドバイスをいただいてから試したこと

function.phpに以下の記述を追加

add_filter('posts_request', function ($query) {
	global $template;
	$original_sql = "SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND ( wp_postmeta.meta_key = '値段' ) AND wp_posts.post_type = 'product' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 DESC LIMIT 0, 18";

	if( basename($template) != "search.php" ) {
		return $query;
	}
	
	echo "<br>" . $query . "<br>";

	$pos1 = mb_strrpos( $query, "L" );
	$original_sql = mb_substr( $original_sql, 0, $pos1-6 );

	$pos2 = mb_strrpos( $query, "L" );
	$queryLimit = mb_substr( $query, $pos2 );

	$query = mb_substr( $query, 0, $pos2 );
	
	echo "<br>" . str_replace(PHP_EOL, '', str_replace(' ', '', $original_sql)) . "<br>";
	echo "<br>" . str_replace(PHP_EOL, '', str_replace(' ', '', $query)) . "<br>";

	echo "<br>" . $queryLimit . "<br>";	
	
	if (str_replace(PHP_EOL, '', str_replace(' ', '', $original_sql)) == str_replace(PHP_EOL, '', str_replace(' ', '', $query))) {

		echo "<br>" . "enter overwite query" . "<br>";
		$query = 
		"
		SELECT SQL_CALC_FOUND_ROWS wp_posts.*, wp_postmeta.non_comma_value  
		FROM wp_posts 
		INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
		WHERE 1=1 AND ( wp_postmeta.meta_key = '値段' ) AND wp_posts.post_type = 'product' AND ((wp_posts.post_status = 'publish')) AND (
			SELECT *, CAST(REPLACE(meta_value,',','') AS DECIMAL) AS non_comma_value 
			FROM wp_postmeta
			WHERE wp_posts.ID = wp_postmeta.post_id
		)
		GROUP BY wp_posts.ID 
		ORDER BY non_comma_value+0 DESC 
		" . $queryLimit;
	}

	echo "<br>" . $query . "<br>";	
	
	return $query;
});

上記のコードで変更前のSQLをDUMPしたものが下記で

SELECT SQL_CALC_FOUND_ROWS wp_posts.*
 FROM wp_posts
 INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND ( wp_postmeta.meta_key = '値段' ) AND wp_posts.post_type = 'product' AND ((wp_posts.post_status = 'publish'))
 GROUP BY wp_posts.ID
 ORDER BY wp_postmeta.meta_value+0 DESC
 LIMIT 0, 18

私のコードで修正したSQLが以下になります。

SELECT SQL_CALC_FOUND_ROWS wp_posts.*, wp_postmeta.non_comma_value
   FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND ( wp_postmeta.meta_key = '値段' ) 
   AND wp_posts.post_type = 'product'
   AND ((wp_posts.post_status = 'publish'))
   AND ( SELECT *, CAST(REPLACE(meta_value,',','') AS DECIMAL) AS non_comma_value
 FROM wp_postmeta WHERE wp_posts.ID = wp_postmeta.post_id )
 GROUP BY wp_posts.ID
 ORDER BY non_comma_value+0 DESC
 LIMIT 0, 18

が、このSQLを実行する前にSQL構文チェッカーで問題なしと出たので、実行してみたところ以下のエラーメッセージが出て
ここからどこをどうやって直せばいいのか分かりません。

Unknown column 'wp_postmeta.non_comma_value' in 'field list'

直すべきところをアドバイスいただけたら幸いですm(_ _)m

やったこと、試したこと

・まずカスタムフィールドでソートする方法が判らず、検索条件を変えて色々グーグル先生に教わって
やっと、なんとかカスタムフィールドで値段順にソートできるようになった。
・しかし詳細を見てみると上記の通り正しくソートされていないことが判明
・custom_WP_Queryみたいなのを作って対応できないかグーグル先生に2時間聞いてみたが、答えが見つからなかった。
・再度、検索条件を変えてグーグル先生に質問すること2時間。解決策がHITせず、こちらに来たしだいです

以下が検索に使っているコードです。これをどうすれば思い通りの結果が得られるか、お知恵を貸していただけませんでしょうか。

<?php
	$args = array(
			'post_type' => 'product', // 取得する投稿タイプのスラッグ,
			'posts_per_page' => 18,
			'paged' => $paged,
			'orderby' => 'meta_value_num',
			'order' => 'DESC',
			'meta_key' => '値段',
			'post_status' => 'publish',
		);
	$wp_query = new WP_Query( $args );
?>

以上となります。なにとぞよろしくお願いします。

Answer 1

手順だけ

get_meta_sqlで当該のフィールドを
CAST(REPLACE(meta_value,',','') AS DECIMAL)した値で
エイリアス名をつけてJOINしておいておき、それをorderbyに指定する

---

参考までに

ウチのプラグインの複数のフィールドの値を利用した計算結果を利用して
meta_queryを書けるようにするためのフィルタフックとメソッド

meta_queryで指定したエイリアス名をorderbyに使えるようにもなってます

add_filter('get_meta_sql',function($sql,$queries,$type,$primary_table,$primary_id_column,$context){
	$meta_table=_get_meta_table($type);
	foreach($queries as $key=>$query){
		if(!is_array($query) || empty($query['compute'])){continue;}
		if(empty($query['alias'])){error_log('compute meta_query must have alias');continue;}
		if(empty($query['keys'])){error_log('compute meta_query must have keys');continue;}
		$sql['join'].=
			" LEFT JOIN (".
			Catpow\util\sql\meta::select_computed_value($type,$meta_table,$query['keys'],$query['compute'],$query['where']??null).
			") AS {$query['alias']} ".
			"ON ({$primary_table}.{$primary_id_column} = {$query['alias']}.{$type}_id) ";
	}
	return $sql;
},10,6);
add_filter('meta_query_find_compatible_table_alias',function($alias,$clause){
	if(isset($clause['alias'])){return $clause['alias'];}
	return $alias;
},10,2);

namespace Catpow\util\sql;
class meta extends sql{
	public static function select_computed_value($type,$table,$keys,$compute,$where=null){
		$primary_alias=key($keys);
		$primary_key=array_shift($keys);
		if(is_numeric($primary_alias)){$primary_alias = $primary_key;}
		$sql="SELECT {$primary_alias}.{$type}_id,({$compute}) AS meta_value FROM {$table} AS {$primary_alias} ";
		foreach($keys as $alias=>$key){
			if(is_numeric($alias)){$alias = $key;}
			$sql.=
				"INNER JOIN {$table} AS {$alias} ".
				"ON {$primary_alias}.{$type}_id = {$alias}.{$type}_id ".
				"AND {$alias}.meta_key = '{$key}' ";
		}
		$sql.="WHERE {$primary_alias}.meta_key = '{$primary_key}'";
		if(isset($where)){$sql.=' AND '.$where;}
		return $sql;
	}
}

Comments

[nekora]

レスありがとうございます。function.phpでフックして、WHERE句とJOIN句をVAR_DUMP()
したり、SQL全文を確認したくて、posts_requestフックでSQL全文をVAR_DUMP()したりしてます。
ただ、当方DB系を苦手としており、いままで避けてきましたが、そういうわけには行かなくなりましたので
progateで急いでSQLについて勉強しているところです。
ですので、回答とベストアンサーの選択に少々お時間をいただくことになると思いますがご容赦ください。

[nekora]

大変お待たせしましたSQLの学習を終えて質問文の追記のようにSQLを組んで実行してみたのですが。
エラーになってしまいます。
大変心苦しいのですが、これのどこをどう直せばいいのか、ご教示いただけると幸いです。

[KazuhiroHatano]

やり方は一つじゃないので、ウチのやり方のヒント的な

値自体のエイリアスはnon_comma_valueとかでなくmeta_valueとして
サブクエリの結果を擬似的なpostmetaテーブルとしてJOINしてエイリアス名をつける

その上でサブクエリにつけたエイリアス名でorderby句が生成されるようにする

meta_query_find_compatible_table_alias

https://developer.wordpress.org/reference/hooks/meta_query_find_compatible_table_alias/

[KazuhiroHatano]

ここら辺になってくるとググるとかするよりソースコード見たほうが早いです

https://core.trac.wordpress.org/browser/tags/5.5.1/src/wp-includes/class-wp-meta-query.php#L552

[nekora]

なかなか返答ができなかった奴に、見捨てずに、新たなアドバイスを頂きありがとうございます。
しかし、今度は別のエラーが出てしまいました。SQL編集前は認識していたカラムがSQLを編集したら
カラムを認識しなくなってしまいました。

大変心苦しいのですが最最度、SQLのレビューとアドバイスを頂けたら幸いです。

[KazuhiroHatano]

サブクエリでは計算結果の値をmeta_valueとする（AS meta_value）
"擬似的なpostmetaテーブル”を作るイメージ

この擬似的なpostmetaテーブルをJOINしてorderbyに使います

---

ここから先はSQL文を直書きするなら関係なし

あとはこの擬似テーブルにエイリアス名をつけて
orderbyで使えるようにしたいわけだけど
通常の処理でJOINされてないのでWPのマニュアルにあるようにmeta_queryのパラメータで
キー値を設定しておけばorderbyで使えるエイリアス名がつけれる、というわけにはいかない

なのでmeta_query_find_compatible_table_aliasフィルタフックを使って
任意のエイリアス名をorderbyの指定に使えるようにします

[nekora]

色々とアドバイス有難うございました。
お陰様で、ついに思い通りの結果を得ることができました。
KazuhiroHatano様のアドバイスのおかげです。ベストアンサー＆高評価付けさせていただきます。