How to filter objects in tables by the Price field, the currency is different, but the price must be more than 50 dollars?

Question

MySQL

There is a table with objects. Each object has a price and currency field. Something like this:
1 Car 40 Euro
2 House 100 Pounds
How to filter objects that are more expensive than 50 dollars, taking into account the Euro/Dollar or Other Currency/Dollar exchange rate?

Answer 1

Table with rates with a reference dollar (all rates are the ratio of currency to dollar).

| ID      | WALLET   | VALUE  |
| 1       | EUR      | 1.1192 |
| 2       | RUR      | 0.0151 |

The tablet, most likely, is better for keeping in a cache. And then the task is trivial.

condition = None
price = 50  # так же?
for course in Course.objects.all():
    if condition is None:
        condition = Q(wallet=course.wallet) & Q(price__gt=price/course.value)
    else:
        condition |= Q(wallet=course.wallet) & Q(price__gt=price/course.value)
return Product.objects.filter(condition)

Answer 2

1) There should be a plate with currencies with the following fields: ID, name, rate (in relation to the base currency - dollar, for example)
2) Make a request in which you convert all currencies to one (dollar) and make a condition on this field

Answer 3

make a table with rates, then join your table with a table with rates - and make a selection using this join. I want to note right away that this will not work VERY quickly. And indexes here not the fact that will rescue. If this selection is supposed to be done several times a day, I would highly recommend making a pre-calculated table with the price reduced to the dollar (or updating the field in the main table). So 1 time spend time on the calculation - further selections will be fast, here the index will work exactly.