I want to update a column in a Supply model that updates with its stock based on a sum of a subset. The thing is that actually hits two queries to make it works.
total_stock = History.sum(:quantity) # first query
Supply.update_columns(stock: total_stock) # second query
Then, I tried to write a subquery so:
total_stock_subquery = mysubset.select(Arel.sql('SUM(quantity)')).to_sql
Supply.update_columns(stock: total_stock_subquery) # it should be only one query
But, ActiveRecord will replace the total_stock_subquery variable as a string inside the resulting sql statement, then query fails.
I tried to wrap the total_stock_subquery parameter inside an Arel.sql thinking that ActiveRecord will detect that the parameter is not a string, but Arel.sql, and would escape this as raw sql, but it didnt work.
Supply.update_columns(stock: Arel.sql(total_stock_subquery)) # dont work too
Then, do you know how can I pass an sql subquery as parameter for a column inside .update_column, o maybe how to tell ActiveRecord that some parameter should be treated as pure sql not as string?
I know I could write this update as raw sql but I think it would be off Rails style.
EDIT: Table definitions
History
- id: integer
- supply_id: reference
- quantity: decimal(9,2)
Supply
- id: integer
- product: string
- stock: decimal(9,2)
class History
belongs_to: supply
end
class Supply
has_many: histories
end
We use the History model to track in and out (positive for in, negative for out) of supplies, then the sum of the histories will be the current stock.
Of course I can implement an after_save on History model to update the stock when some quantity changes it, but for this example, Im implementing a "rebase" or "recalculate" method for all supplies stock, then I need to make the sum for each Supply.