How to merge column value?

Question

How to merge column value in MySQL query? I'm using WordPress for a while now, and I would like to sort data from the price, but each post have different type price (monthly & yearly). The price is stored in wp_postmeta. Here is what I've tried so far.

$mam_global_fields = "
    , price.meta_value AS villa_price,
    CASE currency.meta_value
        WHEN 'IDR' THEN price.meta_value * ". $currency_rate['IDR'] ." / 12
        WHEN 'USD' THEN price.meta_value * ". $currency_rate['USD'] ." / 12
        WHEN 'EUR' THEN price.meta_value * ". $currency_rate['EUR'] ." / 12
        WHEN 'AUD' THEN price.meta_value * ". $currency_rate['AUD'] ." / 12
    END AS price_in_usd,
    CASE currency_monthly.meta_value
        WHEN 'IDR' THEN price_monthly.meta_value * ". $currency_rate['IDR'] ."
        WHEN 'USD' THEN price_monthly.meta_value * ". $currency_rate['USD'] ."
        WHEN 'EUR' THEN price_monthly.meta_value * ". $currency_rate['EUR'] ."
        WHEN 'AUD' THEN price_monthly.meta_value * ". $currency_rate['AUD'] ."
    END AS price_in_usd_monthly,
    price.meta_key AS price_key,
    villa_code.meta_value AS v_codes
";

I join the wp_postmeta multiple times because I want to get the correct data for each post.

$mam_global_join = " 
    INNER JOIN " . $wpdb->postmeta . " AS currency_monthly ON (" . $wpdb->posts . ".ID = currency_monthly.post_id AND (currency_monthly.meta_key = 'monthly_currency'))
    INNER JOIN " . $wpdb->postmeta . " AS price_monthly ON (" . $wpdb->posts . ".ID = price_monthly.post_id AND (price_monthly.meta_key = 'monthly_price'))
    INNER JOIN " . $wpdb->postmeta . " AS currency ON (" . $wpdb->posts . ".ID = currency.post_id AND (currency.meta_key = 'currency'))
    INNER JOIN " . $wpdb->postmeta . " AS price ON (" . $wpdb->posts . ".ID = price.post_id AND (price.meta_key = 'price'))
    INNER JOIN " . $wpdb->postmeta . " AS villa_code ON (" . $wpdb->posts . ".ID = villa_code.post_id AND villa_code.meta_key = 'code')
";

For now it's ordering by 2 columns, but I want to only 1 column that's price_in_usd (but it still showing the wrong villas)

$mam_global_orderby = "price_in_usd, price_in_usd_monthly " . $sort[1];

So how to do this the correct way? because I want to make a sorting programs that will sort the price from both price (yearly or monthly), so if the post have yearly price then the price will be divided by 12. Please help.

I already tried using COALESCE() but I'm stuck, and those codes were the latest version.

EDIT:

Actually I had an idea, but don't know how to do it, I want to get the meta_value to a temporary price, so the yearly or monthly price will be in 1 column, I already looked in google but didn't getting the answer.

EDIT:

What I want, I want to merge the value from two different meta_key (monthly_currency & currency) to a single custom column, that I'll use for sorting, all price will be converted to USD before sorted it, so as you can see, there are CASE currency.meta_value in my code. So after knowing the currency, it'll check if the price if for monthly or yearly, if it's for yearly then it'll divided by 12. So I can sort it by the lowest/highest price, even if it's monthly or yearly rent. The problem I got is, I can't get the correct result, because the price type (yearly / monthly) so how to do it?

Your description of what you want is unclear. But you can ORDER BY a case expression that uses those two column names appropriately. — philipxy, Apr 24 '18 at 09:07
Possible duplicate of [Sort by minimum value of two columns](https://stackoverflow.com/questions/29600737/sort-by-minimum-value-of-two-columns) — philipxy, Apr 24 '18 at 09:20
@philipxy I already edited the question, and don't say it's duplicated, because it's not the same question as mine :) (you can see it on my question and codes) — Khrisna Gunanasurya, Apr 24 '18 at 10:40
Please read all the answers at the duplicate. You are ordering by a different function of multiple columns, but you still have a function of multiple columns. You just have to clarify what your function is. Please read & act on [mcve]. Give an example of table data that you want to order & give the value you want to order by for each row & give an expression that gives that value. Please don't edit by appending, just rewrite to a clear presentation; adding to something uncear does not make it clear. — philipxy, Apr 24 '18 at 17:55
@philipxy yeah but if it's a different column, WordPress using `wp_postmeta` so it have a lot of data in postmeta, in my case, there are 2 different key in 1 column, `meta_key => price` and `meta_key => price_monthly` — Khrisna Gunanasurya, Apr 25 '18 at 06:13
@philipxy and each post only have 1 price, if the post using the `price` instead `price_monthly`, `price_monthly` will be a null — Khrisna Gunanasurya, Apr 25 '18 at 06:14
Unfortunately I cannot understand your description of what you want. But I hope you will act on my earlier comments. (Please don't clarify in comments, clarify by editing.) — philipxy, Apr 25 '18 at 18:54

How to merge column value?

0 Answers0