WordPress Development Asked by kostikovmu on January 21, 2021
I have options: wz_currency_dollar, wz_currency_euro. And post meta _wz_currency. In the admin panel price of product defined in dollar or euro and select corresponding post meta. In the front-end price recalculate for html by next formula:
if (get_post_meta($product->get_id(), '_wz_currency', true) == 'DOLL') {
$price = ( (int) $product->get_price() * (int) get_option('wz_currency_dollar') ) . get_woocommerce_currency_symbol();
} else if (get_post_meta($product->get_id(), '_wz_currency', true) == 'EUR') {
$price = ( (int) $product->get_price() * (int) get_option('wz_currency_euro') ) . get_woocommerce_currency_symbol();
}
If i sorting products by price turns out unexpected result. For simple example:
product_1 200 (price=10 * wz_currency_euro=20)
product_2 400 (price=20 * wz_currency_euro=20)
product_3 180 (price=30 * wz_currency_dollar=6)
But i expected:
product_3 180 (price=30 * wz_currency_dollar=6)
product_1 200 (price=10 * wz_currency_euro=20)
product_2 400 (price=20 * wz_currency_euro=20)
How i can change sorting?
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);
Correct answer by kostikovmu on January 21, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP