0

How would I setup Rails to do the following:

I have a model class Trade:

symbol of type String
price of type Float
currency of type String
date of type Date

I then have a lookup table CrossRates

curr_a of type String
curr_b of type String
date of type Date
rate of type Float

The table CrossRates have many rows like this example:

EUR USD 2015-03-03 1.3593

Let's say I have a report of trades, where I want to use the right CrossRate for the date of the trade. Assume that the Trade is made in currency USD, and I want the report in EUR.

How should I setup the relationships between the models?

Do I have to read in the Crossrate manually for each trade? I'd like to avoid custom SQL (executed with @result = ActiveRecord::Base.connection.execute(sql)) since I would prefer to express the rows in the report as:

trade.price * xrate

but I'm not sure how to get the "xrate". I can pre-load the whole table in a hash and look it up with 

crossrates["EUR"]["USD"]["2015-03-03].rate

but it seems like an awkward solution....

EDIT:

This query does what I want, but it's not exactly "Railsy"...

SELECT
  t.SYMBOL as 'symbol',
  DATE(trade_time) as 'date',
  price*-qty*p.contract_size,
  p.currency,
  cr.ratio
FROM
  alpha.trades t, products p, crossrates cr
WHERE
  t.symbol = p.symbol  and t.dt = "2015-03-03" and cr.dt = "2015-03-03" and
  cr.currency_a = p.currency and cr.currency_b = "SEK"
ORDER BY
  SYMBOL, DATE(trade_time)

How can I express the above using Models instead?

I think the accepted answer on this question below is close to what I want, but I don't understand the finer details...

Advanced SQL in Rails

class User < AR
  has_many :friends

  def self.who_knows(*friend_names)
    joins((1..friend_names.length).map{ |n| 
    "INNER JOIN friends AS f#{n} ON users.id = f#{n}.user_id AND f#{n}.name =    ?" }.join(" "),
    *friend_names)
  })
 end
end

Which you then can call like this:

@users = User.who_knows("Joe", "Jack")
Improve this question
5
  • How's the date of the trade created in cross_rates table ?. Describe the flow in order to let you know whats best :) How are both tables associated if not then how are you keeping/storing/creating cross rates ? Commented Apr 12, 2015 at 14:25
  • I don't have a good answer for you, I don't think you can create associations in Rails on multiple keys at once, which is really what you'd want to do. But I just want to point out that you probably want to store the price as cents in integers, not as dollars in floats. spin.atomicobject.com/2014/08/14/currency-rounding-errors Commented Apr 12, 2015 at 14:27
  • Let's assume for this discussion that the Crossrates table is populated from an external source, I don't think it is relevant to the actual question. Commented Apr 12, 2015 at 15:35
  • don't really get the issue here. is this about performance? an n+1 query? general data modeling? Commented Apr 12, 2015 at 15:50
  • Its about two things. Performance, both CPU and Memory (assume many rows, say 1 per currency-combo and date, or even time, for many years back). Also, since I'm using Rails, I'm trying DRY etc, I was maybe expecting not having to write Java-code in Ruby...... Commented Apr 12, 2015 at 19:06

1 Answer 1

Reset to default
0

One approach is to not join the two tables, treat them as separate models.

In the trade model, you have the currency of the trade and a desired cross_rate.

In your Trade model, include the following method.

def get_cross_rate_report_for cross_rate
  xrate = CrossRate.where("curr_a == ? and curr_b == ?", currency, cross_rate).first().rate
  r = trade.price * xrate
  # add pretty print code here
end

Note that I have not tested this, the key is the where condition that enables you to filter results by two columns and return a CrossRate model, on which you then request the rate. Refer to here

For faster lookups, I would add indexes to the CrossRate table. Look here for guidance and sample code (ctrl-f and search 'index').

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

That would work, but would cause performance problems with one extra query for each row, wouldnt it? I think I would want the "join" query I mention in my question, but I don't want the result as arrays of String objects, instead an array of Trade objects where each object points to a Crossrate object.
The result of the query is a CrossRate model with the details for this specific currency and the cross_rate currency. You can do with that model what you wish. Here I have used it to get trade.price * rate and implied you can create the string you desired in your op. This way you get one row per trade query. Compare this to a join, where you will generate a temporary table with many rows per trade query (one per currency your app is tracking), against which a query still needs to be done to get the row you want. A join may still be faster, but for the effort I would want a case for optimizing.
In other words a join will not get you an array of Trade objects each pointing to one CrossRates object. You will get an array of Trade objects pointing to an array of CrossRates objects. For example a Trade in the currency USD will return from a join table, some tens (more?) of CrossRate models, each representing a currency in your CrossRates join table. Against which you still have to perform a query to get the CrossRate model you want. You have the right models set up, but you are looking for a relationship that isn't there. CrossRates are not dependent on Trades. Trades use CrossRates.
I decided to go with this solution, even though it doesnt feel right doing lots (potentially thousands) of unnecessary queries. But in absence of better solution it will have to do.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.