How sort products by calculate value? ( custom post meta, price, option)

Question

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?

kostikovmu · Accepted Answer

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);

How sort products by calculate value? ( custom post meta, price, option)

One Answer

Add your own answers!

Ask a Question