CODEWITHSUNDEEP
sqlactiverecordruby-on-rails-4
alv_721
alv_721

Reputation: 325

Pure SQL queries in Rails view?

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?

Upvotes: 0

Views: 395

Answers (1)

A Fader Darkly
A Fader Darkly

Reputation: 3636

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.

Upvotes: 1

Related Questions