There is a table:

  • product
    • -price
    • -currency

The user drives in the price and currency, when displaying records on the page, the currency is recalculated according to the current exchange rate. The problem is that the rate can change every day, and there is no possibility to store the price for each currency in the table, as there will be many entries in the table. How, in this case, to implement sorting or sampling with a condition on the price in a certain currency?

  • Create a virtual column with a conversion to one of the currencies and sort by it. - Andrey
  • @Andrey, The course can change, you have to go through all the records every time, and recount this column - Roman Maltsev
  • If it doesn’t load the server very much (or for you it’s not critical) then why not recount each time ?! - Andrey
  • @Andrey, well, I think that it’s wrong to recalculate each time in terms of architecture. And the server load is directly proportional to the number of records, if there are 1000 of them, this is still tolerable, and if more than 300K, it is already critical. - Roman Maltsev

2 answers 2

Create a second table with exchange rates. Keep it only relevant data (today's course) for simplicity. Select and sort:

SELECT товары.* FROM товары JOIN курсы_валют ON товары.тип_валюты = курсы_валют.тип_валюты ORDER BY товары.цена * курсы_валют.курс

Table "goods"

1 boots 500 USD

2 pants 300 EUR

3 cap 100 RUB

Table "currency" (From id you can be, make PRIMARY by type of currency)

1 USD 65

2 EUR 73

3 RUB 1

This is for withdrawal in rubles. Now we need to accept currency from the client.

First we get the selected currency

 SELECT курс FROM курсы_валют

This will speed up the process. Denote the received course $ x

 SELECT товары.*, товары.цена / $x AS вот_эта_цена FROM товары JOIN курсы_валют ON товары.тип_валюты = курсы_валют.тип_валюты ORDER BY товары.цена * курсы_валют.курс

In the final sample, each item will have a price added in the correct currency.

    In the request you need to bring to the sum to one currency through the branch operator:

     SELECT ID, Cost, Currency, IF(Currency='RUR', Cost, IF(Currency='EUR', Cost*34.4, IF(Currency='USD', Cost*25.5, 'Unknown') ) ) rub_cost from table_name order bu rub_cost asc limit 10,20;
    • presented how your request would look like with more currencies, hair began to move ... - Bald
    • @Bald, I see nothing wrong with that. In my case there will be about 5 currencies. And even if there are a couple of dozen of them, then what's wrong with that, the request from this will only get longer, I think mysql will handle it quite quickly. Or do you have a better solution? - Roman Maltsev
    • @RomanMaltsev and make a view out of it - rjhdby
    • what I don't like about your request is that the exchange rate is sewn up in the body, and the branch operator complicates the reading of the request ... you need to think about it better - Bald
    • @Bald, I agree that the decision is not ideal, but I still do not have the best ideas. Perhaps it would be more correct to store the bag for each course in a separate cell, and when changing course, update these values ​​for each kroon? - Roman Maltsev