CODEWITHSUNDEEP
ruby-on-rails
PaulMurrayCbr
PaulMurrayCbr

Reputation: 1260

How do I do raw SQL in a Rails Controller?

Yes, I know it's not recomended, but I would like to do raw SQL in a controller just so see how its done.

class FooController < ApplicationController
  def foo
    boz = 'd'
    connection.select_rows('select * from dual').each do |r|
      boz = r[0]
    end

    [boz: boz]
  end
end

And then my view will hopefully render 'boz is "X"!'.

Of course, this fails because connection is not defined. I have tried ActiveRecord::connection, but connection seems to be an instance method rather than a constant. Obviously, I need the right connection object for the current transaction.

How to I get it? The ruby documentation is a little daunting.

Upvotes: 0

Views: 920

Answers (3)

j_mcnally
j_mcnally

Reputation: 6968

You can also do this if you are looking for a specific model.

Client.find_by_sql("SELECT * FROM clients")

Would return the same as

Client.all

If you don't want it to map to a specific model you can access a direct instance of the ActiveRecord database driver via, ActiveRecord::Base.connection

See http://apidock.com/rails/ActiveRecord/Base/connection for specific documentation of possible methods.

To do a simple raw sql call u can use .execute

Another option is to leverage the Mysql2 gem directly, this is usually not useful inside of rails but want to give you options, this assumes you are using mysql, however other adapters can be used directly as well.

client = Mysql2::Client.new(YAML.load(File.read(Rails.root.join(*%w[config database.yml])))[Rails.env])
results = client.query("SELECT * FROM clients")
results.each(:symbolize_keys => true) do |row|
  # Do something with a row
end

Upvotes: 0

PaulMurrayCbr
PaulMurrayCbr

Reputation: 1260

Found it - ActiveRecord::Base.connection

... but I see someone beat me to it. Ta.

Upvotes: 0

aromero
aromero

Reputation: 25761

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

Upvotes: 1

Related Questions