Does the Rails ORM limit the ability to perform aggregations?

Question

I am concerned whether Rails can handle the types of complex aggregations that are needed for a financial application and particularly whether the ORM can handle these effectively. In a financial application that I am thinking of using this for, there is a need to do lots of reporting of detailed financial data, aggregated in various ways. Without support in the Rails ORM, I would need to write direct SQL. But I am concerned that once I start this, other parts of Rails may not work as well and that as a result I may end up mostly using Rails for its routes and very little else. Is this a valid concern or am I worrying needlessly?

Answer 1

This question illustrates a RoR issue that's pretty widely discussed - its relative discomfort with difficult database mapping requirements. (it's really the ActiveRecord pattern where the difficulties lie.) It likes to decompose complex queries into simpler ones that fit more closely to the AR model, which you probably already know is a relatively light abstraction over tables, with simple relationships based on e.g. one-to-many type assertions.

So I'd say you'll be more comfortable if you accept SQL duty yourself, and then let RoR handle the non-persistence parts.

This isn't a quandary limited to RoR. Most any Object-Relational Modeling tool presents the same questions.

(Footnote: I almost used the abbreviation ORM, but there's another ORM that coincidentally handles specifically these types of conceptual database design and abstration issues quite nicely: Object Role Modeling.)

Answer 2

It's possible that Rails is not an appropriate platform for this application. OR you could consider using ActiveRecord against views in your database. Aggregate your data in your views, then using the 'rails_sql_views' gem you can treat them like ordinary models. (I have never used this, so I don't know how well this works in practice.)

Link: rails_sql_views

Edit: On further inspection, you might not even need any that gem, or any other special setup to simply query against views.

Answer 3

Limitations of ActiveRecord are one of the reasons I had trouble using Rails in a scientific environment. You might want to check out alternative Ruby ORM's that make it a bit easier to work with a legacy database:

• Sequel
• DataMapper

Ultimately though ORM's by design take you away from SQL so it's possible that none of them are a good fit.

Answer 4

The reason I like active record is that it allows you to poke through the abstraction. I have yet to run into a situation I couldn't handle with AR. I'm sure there may be some extremely esoteric examples, but a better question would be to give an example of the type of query you want to do and let someone show you how to do it in AR.