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

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0