0

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?

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.