Firstly i added table postmeta to query
function add_join_clause_to_view_sql( $join, $wpq ) {
global $wpdb;
if( $wpq->query['orderby'] === 'price' || $wpq->query['orderby'] === 'price-desc') {
$post_meta_table_name = $wpdb->postmeta;
$post_table_name = $wpdb->posts;
$join .= "
LEFT JOIN
$post_meta_table_name
ON $post_meta_table_name.post_id = $post_table_name.id
";
}
return $join;
}
add_filter('posts_join', 'add_join_clause_to_view_sql', 10, 2);
Secondly i added meta_key in WHERE statement
function add_where_clause_to_view_sql( $where, $wpq ) {
global $wpdb;
if( $wpq->query['orderby'] === 'price' || $wpq->query['orderby'] === 'price-desc') {
$post_meta_table_name = $wpdb->postmeta;
$where .= "
AND
$post_meta_table_name.meta_key = '_wz_currency'
";
}
return $where;
}
add_filter( 'posts_where' , 'add_where_clause_to_view_sql', 10, 2 );
Thirdly i added a calculated field with condition
function filter_function_name_4359( $fields, $wpq ){
global $wpdb;
if($wpq->query['orderby'] === 'price' || $wpq->query['orderby'] === 'price-desc') {
$post_meta_table_name = $wpdb->postmeta;
$wc_product_meta_lookup = 'wc_product_meta_lookup';
$wz_currency_dollar = (int) get_option('wz_currency_dollar');
$wz_currency_euro = (int) get_option('wz_currency_euro');
$fields .= ", CAST(
CASE
WHEN $post_meta_table_name.meta_value="DOLL" THEN $wc_product_meta_lookup.min_price * $wz_currency_dollar
WHEN $post_meta_table_name.meta_value="EUR" THEN $wc_product_meta_lookup.min_price * $wz_currency_euro
WHEN $post_meta_table_name.meta_value="RUB" THEN $wc_product_meta_lookup.min_price END AS decimal(15,2)
) AS CalculatedPrice";
}
return $fields;
}
add_filter( 'posts_fields', 'filter_function_name_4359', 10, 2 );
And finally i added orderby statement with a calculate field
function orderByPriceTimesCurrency($orderby, $wpq) {
if( $wpq->query['orderby'] === 'price') {
$orderby = '
CalculatedPrice ASC
';
}
if( $wpq->query['orderby'] === 'price-desc') {
$orderby = '
CalculatedPrice DESC
';
}
return $orderby;
}
add_filter('posts_orderby_request', 'orderByPriceTimesCurrency', 10, 2);