7

I am developing a rental marketplace application with Rails 5. It has a quite simple structure with users, products and orders, with the products belonging to users, who actually create them.

In order to let the users manage a "live" stock of their products, a StockMovement model/table has been created, with reference to the user, and the stock change (positive/negative) and a date. This way, we can determine the stock of a certain product for a specific date, in terms of "what the user is prepared to offer". With a simple query to a single table/model we manage to get the "sum" of the stocks, which happens to be the stock of the product.

Apart from that, we need to consider when an order is placed and confirmed for a certain product, resulting in the need of substracting an amount of that item from the stock for a period of time.

Currently, we calculate the stock with the query in the StockMovement model, and manually joining the amounts affected by orders, using a custom select clause in the StockMovement model, which is beginning to feel overkill already (we're not even beta).

My question is, how would you implement this the Rails way? I've always had trouble with such situations where, theoretically, at least with relational db logic in mind (we use Postgres), the optimal thing is to calculate everything on the fly using queries with joins and calculated fields, but when it comes to implementing it with ActiveRecord, there is no way of referencing a calculated column in table B in a query to table A, unless you re-define that calculation in a select statement in model A, which is what I'm trying to avoid.

So far, my options as I currently see are:

1- Keep things the way they are: repeat the calculation logic in the select statement in order to access "foreign calculated fields"

2- Create a record in the StockMovement table every time an order is confirmed and handle all stock from there (not desirable IMHO, as it needs to be carefully updated every single time something is modified in the orders)

3- The potential magic (and right) solution I haven't been able to think of...

Thank you!

Martí Gascó
  • 385
  • 2
  • 11
  • Could you post the table structure that your already created? Ideally with few records. – s1mpl3 Mar 08 '17 at 02:23
  • More information is needed. Can you provide: your current SQL queries, your model code for StockMovement and other application models (Stock, Order, etc. - whatever you are using). Code like this could be a start:`User.first.stock_movements.where(created_at: 10.days.ago..Date.today, stock_id: 10).sum(:change) - Order.where(stock_id: 10, checkout_date: Date.today).count`. Again, I'm guessing at the column and model names. If you can provide more information - code, sample data, sample expected output, we can give you a complete answer. – marksiemers Mar 09 '17 at 08:05
  • based on your description, it's perfectly acceptable to move these calculations to SQL, e.g. triggers. Too many people overlook SQL's native capabilities :) Another approach might be using a cache of calculated values following saves, e.g. elasticsearch, redis, although IMO native SQL triggers would probably be cleaner. – engineerDave Mar 10 '17 at 17:22
  • @martí-gascó - Any follow-up on this, or are you satisfied with the given answers? – marksiemers Mar 10 '17 at 21:36
  • @engineerDave - I completely agree that using the SQL DB here is the best choice. In the spirit of the original question about doing this the "Rails way" - I would want to stay DB agnostic. Once you get to triggers and calculated fields AFAIK, ActiveRecord cannot remain agnostic. martí-gascó - Using an after_save callback to cache the calculation would still be the "Rails way" and would help performance for sure. http://guides.rubyonrails.org/active_record_callbacks.html – marksiemers Mar 10 '17 at 21:43
  • @marksiemers I hear re: "the rails way" but the issue, as I've found it, is that you really can't/shouldn't rely on the AR callbacks to work/fire properly. They exist in that weird layer between the app and the database and could cause concurrency issues, validation issues, or even make testing much more difficult. I think something like this is really meant for the SQL layer, especially since its essentially calculated on the fly. A gem that may help, if SQL knowledge is an issue, is [hair_trigger](https://github.com/jenseng/hair_trigger). Of course, YMMV and callbacks are simple to implement – engineerDave Mar 13 '17 at 19:53

2 Answers2

4

Personally, I think mixing SQL with ActiveRecord is the Rails way. It is one of my favorite things about AR: you can inject raw SQL when you need it. You can even wrap it up in encapsulated, re-usable methods by using scopes. For instance you could define a scope for adding that select column.

You could also create a database view called current_stocks with one record per product. Have that view query stock_movements and orders and compute the current stock, then just use it whenever you need that value. You can even make a readonly ActiveRecord class backed by the view, so that you can still use regular associations, instance methods, etc. Again, Rails is offering you a way to make advanced SQL features play nice with the rest of your app.

Eventually you are going to have performance problems if you're computing the stock on-the-fly every time, so you could either (1) make that a materialized view and refresh it periodically in the background or (2) add a current_stock column on products and keep it up-to-date. I would probably go with 2.

Or you could (3) stick with on-the-fly computation but add a starting_stocks table that gives you a "stock as of Sunday at midnight" value, so that you never have to compute too far into the past. I think this is probably the best approach of all, and you can postpone implementing it until you actually have problems.

If you really want to make a deep dive, you might want to read about temporal databases. Two good books are Developing Time-Oriented Database Applications in SQL by Richard Snodgrass and Bitemporal Data by Tom Johnston. The former is also available as a free PDF from the author's website. I'm guessing that is overkill for you right now, but still it's a great thing to know about.

Paul A Jungwirth
  • 23,504
  • 14
  • 74
  • 93
  • I really like first proposed solution of the current_stocks view which queries the other tables, I even thought of it before, but my main issue is that I'm interested in the stock value for a particular date, not only the current one. Eg: when someone wants to rent one of the products for three days the upcoming month: I need to know the stock for each one of the affected days (I've got the multiple day part quite covered with array comparisons), but the stock value for a given date is what I'm struggling with. This is also a concern if storing stock in the product table, it'd only considr tdy – Martí Gascó Mar 07 '17 at 00:10
  • If you use the `starting_stocks` approach then you can do an on-the-fly computation for any days you like, but without the unlimited performance hit. In fact if you rename it `stock_counts` then you could add a `day` column, keep a history of counts for each product, and even be able to compute the count on any day in the past. – Paul A Jungwirth Mar 07 '17 at 17:59
2

Consider caching the total as you go along, and saving that back to the database. It'll be far more efficient than having to sum all historic records.

Rails has the concept of a counter_cache (more info: http://guides.rubyonrails.org/association_basics.html#options-for-belongs-to-counter-cache), but that only counts records - not totals.

Luckily there is also the counter_culture gem, which does allow you to count totals (see https://github.com/magnusvk/counter_culture#totaling-instead-of-counting)

I don't know if you already have a Stock object, but having a Stock that has_many StockMovements definitely feels like the optimal approach to me. This way you still have all of the granular movements; your querying is fast; and your counters are kept up-to-date and accurate by the nature of database transactions.

Edit: I've just read your comment "I'm interested in the stock value for a particular date". This doesn't necessarily invalidate this approach, but means that you potentially want to extend it to include something like a StockPosition record - which could be created daily - that records the position on any given day. Whether or not this approach makes sense depends entirely on how you'll be querying the data, and there are multiple approaches you could take here e.g. querying a single StockPosition for a day; summing all historic records up to a set date; or subtracting the sum of records back to a particular date from the current Stock total (can be more efficient than querying all historic data, if the dates that you lookup tend to be quite close to the present day).

gwcodes
  • 5,632
  • 1
  • 11
  • 20