Ror: Execute SQL in Controller

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

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.

Run mysql query from a controller in ruby on rails

You can execute raw SQL through ActiveRecord::Base.connection but I rarely recommend it and certainly not in this case, but for edification purposes

def set_dcs
  sql= <<SQL 
      select employees.nombre, employees.apellido_paterno, employees.apellido_materno 
      from employees, activities, field_tests
      where activities.field_test_id=field_tests.id and employees.id=activities.dcs_id and field_tests.id=id
SQL 
  ActiveRecord::Base.connection.exec_query(sql)
end

I was not sure what the trailing id is in reference to and it will raise a SQL error due to it's ambiguity. I am going to assume it is a parameter and the primary search condition where as the rest are table joins.

That being said since you are using rails these can be true associations and joins resulting in far more readable controller code e.g. Model Definitions

class Employee < ActiveRecord::Base
   has_many :activities, foreign_key: :dcs_id
   has_many :field_tests, through: :activities
end 
class FieldTest < ActiveRecord::Base
   has_many :activities
end
class Activity < ActiveRecord::Base
   belongs_to :employee, foreign_key: :dcs_id
   belongs_to :field_test
end

Then the controller is simply

 Employee.
  select("employees.nombre, employees.apellido_paterno, employees.apellido_materno").
  joins(:field_tests).where(field_tests: {id: SOME_ID}) 

the resulting SQL will be similar to

 SELECT 
   employees.nombre, 
   employees.apellido_paterno, 
   employees.apellido_materno
 FROM 
   employees
   INNER JOIN activities ON employees.id = activities.dcs_id 
   INNER JOIN field_tests ON activities.field_test_id = field_tests.id
 WHERE 
   field_tests.id = SOME_ID

And this will return a collection of Employee Objects rather than an ActiveRecord::Result (returned from ActiveRecord::Base.connection.exec_query) which is more similar to an Array than anything else.

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 do I do raw SQL in a Rails Controller?

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

SQL Query in Rails 5 controller

Note: It is bad to perform SQL operations from controller.

There is provision to run raw SQL queries with ActiveRecord.

Here, it will be like:

sql = "CREATE TABLE test(ID INT NOT NULL,PRIMARY KEY (ID))"
ActiveRecord::Base.connection.execute(sql)

execute sql query and loop through result set in rails

You get this error because what you get in @jstree is a raw DB adapter result. If you want to query for some objects by issuing raw SQL queries then use find_by_sql. Example:

Client.find_by_sql("SELECT * FROM clients INNER JOIN orders ON clients.id = orders.client_id ORDER clients.created_at desc")

---

Related Topics