CODEWITHSUNDEEP
sqlruby-on-railsactiverecordsqlitedatabase-connection
Frâncio Rodrigues
Frâncio Rodrigues

Reputation: 2290

How to chain raw SQL queries in Rails OR how to return an ActiveRecord_Relation from a raw SQL query in Rails?

How can I run a raw sql query and get an ActiveRecord_Relation instance returned?

On a sqlite3 db and Rails 4.2, ActiveRecords::Base.exec_query(sql) returns an ActiveRecord_Result instance. ActiveRecords::Base.execute(sql) returns an array. This is troublesome because I cannot run subsequent "ActiveRecord queries" or raw sql queries on the returned objects.

More generally, I would like to know how to chain "complex" sql queries in Rails. By complex I mean I could not figure out a way to do it through the "ORM methods" given by ActiveRecords. And by chaining I mean I would like to break it down in several SQL queries, for performance reasons. Example: I just want to run the big heavy first SQL query once, store the result and then run lighter sql queries depending on the user behavior.

I am currently trying to optimize a database visualization through dataTables implemented with server-side-processing. The server has to run an unnecessarily complex SQL query every time the user interacts with the table because I could not break it down in simpler queries. I looked at the following questions but could not figure out a satisfying solution:

Rails raw SQL example

Convert Array#select to active record query in rails 4

Hash/Array to Active Record

Upvotes: 19

Views: 4996

Answers (1)

user3033467
user3033467

Reputation: 1178

This is a way to get an ActiveRecord_Relation from raw_sql.
It works best if you actually have a model matching the fields you're trying to retrieve, but as you can see with test_attribute, any data will be loaded.

# Just an example query, any query should be ok
query = <<-SQL
  SELECT *, TRUE AS test_attribute
  FROM users
  WHERE sign_in_count < 10
SQL
relation = User.select('*').from("(#{query}) AS users")

relation.class
# User::ActiveRecord_Relation

relation.first.test_attribute
# true

Upvotes: 26

Related Questions