Pure SQL queries in Rails view?

Question

I am asked to display some sort of data in my Rails App view with pure SQL query without help of ActiveRecord. This is done for the Application owner to be able to implement some third-party reporting tool (Pentaho or something).

I am bad in SQL and I am not really sure if that is even possible to do something similar. Does anyone have any suggestions?

Try not to use SQL in the view itself, the view is not the place for it. Do the look-up in the model if possible, or a service object dedicated to the task. It's very easy to accidentally be doing a SQL look-up in the view though, due to the ease of use of models and ActiveRecord lazy loading. — A Fader Darkly
– A Fader Darkly, Commented Aug 12, 2014 at 12:10

A Fader Darkly · Accepted Answer · 2014-08-12 14:26:17Z

1

If you must drop down to pure SQL, you can make life more pleasant by using 'find by sql':

bundy = MyModel.find_by_sql("SELECT my_models.id as id, my_articles.title as title from my_models, my_articles WHERE foo = 3 AND ... ...")

or similar. This will give you familiar objects which you can access using dot notation as you'd expect. The elements in the SELECT clause are available, as long as you use 'as' with compound parameters to give them a handle:

puts bundy.id
puts bundy.title

To find out what all the results are for a row, you can use 'attributes':

bundy.first.attributes

Also you can construct your queries using ActiveRecord, then call 'to_sql' to give you the raw SQL you're using.

sql = MyModel.joins(:my_article).where(id: [1,2,3,4,5]).to_sql

for example.

Then you could call:

MyModel.find_by_sql(sql)

Better, though, may be to just use ActiveRecord, then pass the result of 'to_sql' into whatever the reporting tool is that you need to use with it. Then your code maintains its portability and maintainability.

edited Aug 12, 2014 at 14:26

answered Aug 12, 2014 at 12:08

A Fader Darkly

3,6761 gold badge26 silver badges30 bronze badges

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

8 Comments

alv_721 Over a year ago

Another, follow-up question I can not figure out - Is there a way to get sql displayed for Rails sum method? When I use .to_sql on it, I get an error undefined method "to_sql" for :Fixnum.

A Fader Darkly Over a year ago

You're doing something that turns an ActiveRecord relation object into a result - in this case a number. Ruby is correctly telling you that numbers don't support .to_sql. I'll need to see your code, but you'll probably have to do the sum in the ActiveRecord query itself, then read it out as a value. This will probably be more efficient.

alv_721 Over a year ago

Let's say I have many shops which has_many departments which has_many reports (report is monthly income record). What I need to have - is displaying: 1) Report :amount per given month for each Department of given Shop; 2) total for Shop Report :amount per given month.

alv_721 Over a year ago

I have done this easily via AR - I just iterate through reports records with given month in it (:report_date column). But I still need to get those queries sql-like (for the internal tool I mentioned before). I manage to get sql query for array of repports - reports = @shop.reports.where("month = ?", Date.today.month).to_sql, but I have no idea how to get sql query for counting their :amount sum

A Fader Darkly Over a year ago

sum = @shop.reports.select("SUM(amount) as total_amount").where("month = ?", Date.today.month).first.total_amount then sql = @shop.reports.select("SUM(amount) as total_amount").where("month = ?", Date.today.month).to_sql

|

Collectives™ on Stack Overflow

Pure SQL queries in Rails view?

1 Answer 1

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Related