SQL query with the search for the minimum value and the calculation of the fields?

Question

SQL

There is such a table wp_postmeta


I want to find the minimum price for posts by the field 'meta_key'='_price'. Everything is simple here, but here is the difficulty:
in the price field there are both ruble prices and euro prices for different containers. I use the 'meta_key'='convert_to_rub' records with the same post_id as a sign of identification what price (euros or rubles). To convert euro prices on the fly in PHP, I use the exchange rate.

Now here's a question that I myself can't do: how to form a query to find the minimum price for the field 'meta_key'='_price', 'convert_to_rub'=0 and
'meta_key'='_price' * euro_rate, 'convert_to_rub'=1 .
I suppose that a complex query is needed here, since you can’t get the min for the calculated field and the non-calculated one at the same time. It may be necessary to make two separate requests and then in PHP find the minimum of the two.

Answer 1

Use nested queries
SELECT Id, MIN(Price) FROM
(
SELECT post_id AS Id, meta_value AS Price FROM wp_postmeta WHERE meta_key = '_price' AND post_id IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'convert_to_rub' AND meta_value = 0)
UNION
SELECT post_id AS Id, meta_value * {CURRENCY_RATE} AS Price FROM wp_postmeta WHERE meta_key = '_price' AND post_id IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'convert_to_rub' AND meta_value = 1)
)
GROUP BY Id