TransWikia.com

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

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?

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

Correct answer by kostikovmu on January 21, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP