回答編集履歴

1

追記

2020/10/14 06:12

投稿

KazuhiroHatano
KazuhiroHatano

スコア7819

test CHANGED
@@ -7,3 +7,113 @@
7
7
  ``CAST(REPLACE(meta_value,',','') AS DECIMAL)``した値で
8
8
 
9
9
  エイリアス名をつけてJOINしておいておき、それをorderbyに指定する
10
+
11
+
12
+
13
+
14
+
15
+ ---
16
+
17
+
18
+
19
+ 参考までに
20
+
21
+
22
+
23
+ ウチのプラグインの複数のフィールドの値を利用した計算結果を利用して
24
+
25
+ meta_queryを書けるようにするためのフィルタフックとメソッド
26
+
27
+
28
+
29
+ meta_queryで指定したエイリアス名をorderbyに使えるようにもなってます
30
+
31
+
32
+
33
+ ```php
34
+
35
+ add_filter('get_meta_sql',function($sql,$queries,$type,$primary_table,$primary_id_column,$context){
36
+
37
+ $meta_table=_get_meta_table($type);
38
+
39
+ foreach($queries as $key=>$query){
40
+
41
+ if(!is_array($query) || empty($query['compute'])){continue;}
42
+
43
+ if(empty($query['alias'])){error_log('compute meta_query must have alias');continue;}
44
+
45
+ if(empty($query['keys'])){error_log('compute meta_query must have keys');continue;}
46
+
47
+ $sql['join'].=
48
+
49
+ " LEFT JOIN (".
50
+
51
+ Catpow\util\sql\meta::select_computed_value($type,$meta_table,$query['keys'],$query['compute'],$query['where']??null).
52
+
53
+ ") AS {$query['alias']} ".
54
+
55
+ "ON ({$primary_table}.{$primary_id_column} = {$query['alias']}.{$type}_id) ";
56
+
57
+ }
58
+
59
+ return $sql;
60
+
61
+ },10,6);
62
+
63
+ add_filter('meta_query_find_compatible_table_alias',function($alias,$clause){
64
+
65
+ if(isset($clause['alias'])){return $clause['alias'];}
66
+
67
+ return $alias;
68
+
69
+ },10,2);
70
+
71
+ ```
72
+
73
+
74
+
75
+
76
+
77
+
78
+
79
+ ```php
80
+
81
+ namespace Catpow\util\sql;
82
+
83
+ class meta extends sql{
84
+
85
+ public static function select_computed_value($type,$table,$keys,$compute,$where=null){
86
+
87
+ $primary_alias=key($keys);
88
+
89
+ $primary_key=array_shift($keys);
90
+
91
+ if(is_numeric($primary_alias)){$primary_alias = $primary_key;}
92
+
93
+ $sql="SELECT {$primary_alias}.{$type}_id,({$compute}) AS meta_value FROM {$table} AS {$primary_alias} ";
94
+
95
+ foreach($keys as $alias=>$key){
96
+
97
+ if(is_numeric($alias)){$alias = $key;}
98
+
99
+ $sql.=
100
+
101
+ "INNER JOIN {$table} AS {$alias} ".
102
+
103
+ "ON {$primary_alias}.{$type}_id = {$alias}.{$type}_id ".
104
+
105
+ "AND {$alias}.meta_key = '{$key}' ";
106
+
107
+ }
108
+
109
+ $sql.="WHERE {$primary_alias}.meta_key = '{$primary_key}'";
110
+
111
+ if(isset($where)){$sql.=' AND '.$where;}
112
+
113
+ return $sql;
114
+
115
+ }
116
+
117
+ }
118
+
119
+ ```