Rails 3 Execute Custom SQL Query Without a Model

Rails 3 execute custom sql query without a model

Maybe try this:

ActiveRecord::Base.establish_connection(...)
ActiveRecord::Base.connection.execute(...)

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.

Non model SQL query with parameters in Rails

Being mindful of SQL injection, you should probably use one of Rails' provided sanitization methods, rather than normal interpolation.

See the first answer here for a snippet that will let you sanitize and execute arbitrary SQL in an AR model: Ruby on Rails: How to sanitize a string for SQL when not using find?

If you include that, you should be able to do ModelName.execute_sql('select stuff where attribute = ? and other_attribute = ?', first_value, second_value'), just as you would in a normal ActiveRecord .where() method.

Custom SQL query without Corresponding Table

You can grab a db connection directly from ActiveRecord::Base, but it's not as useful as extending AR::Base, because helpful methods like sanitize_sql are protected.

class ComplexQueries < ActiveRecord::Base
  def self.my_query
    # Notice how you can, and should, still sanitize params here. 
    self.connection.execute(sanitize_sql(["select * from foo limit ?", 10]))
  end
end

results = ComplexQueries.my_query
results.each_hash{|h| puts h.inspect}

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.

Rails: Run raw sql query returning extra info and build models

Are you trying to do something like this:

ActiveRecord::Base.connection.execute("sql here").map do |hash|
  new_info = harvest_info(hash)
  Comment.new(hash.merge(new_info)) if some_requirement?
end

Convert a complex query in rails given a sql query

Other awnsers have provided the right SQL. So I only show how to execute raw SQL in Rails.

Rails supports not only active record, but also it allows executing raw SQL. It returns an array, each element in the array is a hash with all your selected columns as key and data as value. The returned array is just like which active record way returns.

Here is a sample:

# first establish connection, if not explicitly specify establish connection, it should use default configuration, which is config/database.yml
ActiveRecord::Base.establish_connection(
  :adapter => "mysql2",
  :host => "localhost",
  :port => 3306,
  :username => "myuser",
  :password => "mypass",
  :database => "somedatabase",
  :pool => 1,
  :timeout => 5000
)
sql = "select name, age from users where age < 30"
raw = ActiveRecord::Base.connection.execute(sql)
# raw is like [{"name" => "Tom", "age" => 28}, {"name" => "Bob", "age" => 26}]
raw.each(:as => :hash) do |row|
  puts row.inspect # row is hash, something like {"name" => "Tom", "age" => 28}
end

You can run rails runner 'puts ActiveRecord::Base.configurations.inspect' to check your default DB connection info.

RoR: Execute SQL in controller

The find_by_sql method by Active Record is the way to go.

def index
    @books = Book.find_by_sql('Select * from books')
end

reference: https://guides.rubyonrails.org/active_record_querying.html#finding-by-sql

Accessing join table (A table without model) in rails

There is no harm in creating a Model for a table in Rails. Though, if you wish to avoid it, you can use raw SQL queries for same.

sql = "SELECT * from posts where id = #{params[:id}"
result = ActiveRecord::Base.connection.execute(sql)
result.to_a

---

Related Topics