What is the best way to update data in Laravel models?

Question

Laravel

The task is to make prices for goods with the possibility of converting depending on the exchange rate.
Data on the cost of goods and exchange rates are stored in different tables with a one-to-many relationship.
To store data on the cost of goods, the following fields have been created in the table:

• 'base_price' – Base price of the item, value in rubles, dollars, euros
  
• 'currency' – Type, in which currency the price is set: in rubles RUB, in euros EUR or dollars USD
  
• 'price' – converted price, taking into account the current exchange rate.
  

Currencies are updated daily according to the Central Bank. Since the exchange rates change daily, the prices set in the currency must be converted at least once a day.

At first glance, there is no problem, to get the data of goods where the currency type is not rubles, ( 'currency' != RUB ), and in the loop to pull the model, multiply the base price by the current rate and save updates.

But somehow it turns out not rationally: a huge number of queries to the database!

Asking the community for advice, is there any way to update model data in a more optimal way, with the least database access?

Answer 1

Thank you all for your help and information in solving the problem.
Given that this information may be useful to others, I will describe the solution I chose.
Indeed, the task of regularly updating prices depending on the exchange rate can be solved in different ways. For myself, I chose the way to update the data using raw database queries. Given that the exchange rate data is not updated often, only once a day, I think this will be the best, not resource-intensive way.
Product data, including their cost, is stored in the 'product' table, which also contains fields for determining the price of the product:

1. 'base_price' – Base price of the item, value in rubles, dollars, euros
   
2. 'currency' – Type in which currency the base price is set: in rubles RUB, in euros EUR or dollars USD
   
3. 'price' – converted price, taking into account the current exchange rate.
   

Fresh exchange rate data is obtained from the Central Bank of the Russian Federation API and stored in the 'currency' table.

In the controller, I get a collection and convert it into an array with the necessary data about the type of currency, denomination, and the value of the current rate.

$currency = Currency::select('currency', 'Nominal', 'value')->get()->keyBy('currency')->toArray();

Then, using RAW queries, I access the database to update each type of currency. For example, for products whose base price is set in EUR ('currency' = 'EUR'), the data will be updated with the following query:

$countUpdate = DB::update("UPDATE `product` SET `price` = (`base_price` * ?)  WHERE `currency`='EUR'", [ $currency['EUR']['value'] ]);

That is, from the product table ( "UPDATE `product` ) the product data is selected where the 'currency' field is the type of currency specified as ( WHERE `currency`='EUR' ), and the `price` field is set to values ​​where the base price data are multiplied by the sum of the current exchange rate ( `price` = (`base_price` * $currency['EUR']['value'] ).
Thus, four similar requests convert to rubles and update the prices of goods whose price is set in currency Using the MySQL ELT() and FIELD() functions, these queries can be combined into one.

Answer 2

1. Make a function that will receive the price value and currency type as input... and then multiply by the rate of this currency when outputting ...
2. The rate itself can be updated once in a while (at least once every 10 minutes) - not an expensive operation;
3. In the place where the price will be used, use the function from point 1 .
The operation of multiplication during the output will not spend a lot of resources ...

Answer 3

one update request per currency, like so:

update `products`
set `price` = (`base_price` * ?)
where `currency` = 'EUR';

Where ? - well.