Rails Raw SQL Example

Rails raw SQL example

You can do this:

sql = "Select * from ... your sql query here"
records_array = ActiveRecord::Base.connection.execute(sql)

records_array would then be the result of your sql query in an array which you can iterate through.

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

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

How do you manually execute SQL commands in Ruby On Rails using NuoDB

The working command I'm using to execute custom SQL statements is:

results = ActiveRecord::Base.connection.execute("foo")

with "foo" being the sql statement( i.e. "SELECT * FROM table").

This command will return a set of values as a hash and put them into the results variable.

So on my rails application_controller.rb I added this:

def execute_statement(sql)
  results = ActiveRecord::Base.connection.execute(sql)

  if results.present?
    return results
  else
    return nil
  end
end

Using execute_statement will return the records found and if there is none, it will return nil.

This way I can just call it anywhere on the rails application like for example:

records = execute_statement("select * from table")

"execute_statement" can also call NuoDB procedures, functions, and also Database Views.

Executing raw sql against multiple data bases

You may use ActiveRecord::Base.establish_connection to switch database connection. Code should be like this:

#database.yml
development:
  adapter: postgresql
  host: 127.0.0.1
  username: postgres
  password: postgres
  database: development_db

development_another_db:
  adapter: postgresql
  host: 127.0.0.1
  username: postgres
  password: postgres
  database: another_db


ActiveRecord::Base.establish_connection :development_another_db
sql = "Select * from ... your sql query here"
records_array = ActiveRecord::Base.connection.execute(sql)

ActiveRecord::Base.establish_connection :development
sql = "Another select"
records_array = ActiveRecord::Base.connection.execute(sql)

You may find details about establish_connection in Rails documentation.

How to use raw Postgres SQL query result in Ruby on Rails 5.2?

You can use a model that has these attributes and cast the results yourself.

records = ActiveRecord::Base.connection.exec_query(raw_sql).to_a
records.map { |attrs| YourModel.new(attrs) }

This would work because Rails allows you to initialize a model through a hash like

YourModel.new({ id: 1 })

Map will go over each element in the array and initialize them individually.

How do I do raw SQL in a Rails Controller?

ActiveRecord::Base.connection.execute('select * from dual')

Return hash from raw sql query

There are different ways you can execute a raw query in Rails (with ActiveRecord):

query = <<-SQL
  SELECT TO_CHAR(date::timestamptz, 'YYYY-MM-DD HH') AS formatted_date,
         SUM(price * quantity) AS total
  FROM table1s
  GROUP BY TO_CHAR(date::timestamptz, 'YYYY-MM-DD HH')
SQL

Table1.find_by_sql(query).to_h { |table| [table.formatted_date, table.total] }
# {"2020-01-01 12"=>30, "2020-01-01 10"=>45}

ActiveRecord::Base.connection.execute(query).values.to_h
# {"2020-01-01 12"=>30, "2020-01-01 10"=>45}

ActiveRecord::Base.connection.exec_query(query).rows.to_h
# {"2020-01-01 12"=>30, "2020-01-01 10"=>45}

You could give them a try and see how they perform. However, I must mention that the ActiveRecord version is much shorter, clear and easy to get:

Table1.group("TO_CHAR(date::timestamptz, 'YYYY-MM-DD HH')").sum('price*quantity')
# SELECT SUM(price*quantity) AS sum_priceallquantity, TO_CHAR(date, 'YYYY-MM-DD HH') AS to_char_date_yyyy_mm_dd_hh FROM "table1s" GROUP BY TO_CHAR(date, 'YYYY-MM-DD HH')
# {"2020-01-01 12"=>30, "2020-01-01 10"=>45}

---

Related Topics