CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-plugins
btelles
btelles

Reputation: 5420

What are alternatives to find_by_sql for computationaly-heavy queries?

Our company loves reports that calculate obscure metrics--metrics that cannot be calculated with ActiveRecord's finders (except find_by_sql) and where ruport's ruby-based capabilities are just too slow.

Is there a plugin or gem or db adapter out there that will do large calculations in the database layer? What's your solution to creating intricate reports?

Upvotes: 5

Views: 1108

Answers (5)

Tony Collen
Tony Collen

Reputation: 111

It sounds as if your tables could be normalized. At one place I worked, the amount of normalization we did was impacting our reporting needs, so we created some shadow tables that contained a bunch of the aggregate data, and did reporting against that.

I agree with Neil N's comment that the question is a little vague, but perhaps this gets you moving in the right direction?

Upvotes: 1

Pete Hodgson
Pete Hodgson

Reputation: 15855

You might want to look at using DataMapper or Sequel for your ORM if you're finding that ActiveRecord lacks the expressiveness you need for complex queries. Switching away from ActiveRecord wouldn't be a decision to take likely, but it might be worth investigating at least.

Upvotes: 0

Omar Qureshi
Omar Qureshi

Reputation: 9103

Although not database agnostic, our solution is plpgsql functions where it becomes really slow to use Ruby and ActiveRecord.

Upvotes: 2

Lee
Lee

Reputation: 916

Is there anything inherent about your reports that prevents the use of an SQL view or stored procedure?

In one particular project, a technique I often find useful is to create your SQL query (that may be quite complex) as a named view in the database, and then use 

 YourModel.connection.select_all(query)

to pull back the data. It's not an optimal approach; I'm keen to explore improvements to it.

Unfortunately, as you suggested, the support for doing computing complex database-based reports within rails seems fairly limited.

Upvotes: 1

Arrel
Arrel

Reputation: 13658

Thoughtbot's Squirrel plugin adds a lot of Ruby-ish functionality to ActiveRecord's find method, with multi-layered conditionals, ranges, and nested model associations:

www.thoughtbot.com/projects/squirrel/

Upvotes: 1

Related Questions