# 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?

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

``````

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

``````

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