Translating SQL Query to ActiveRecord and Rendering as a Table

Question

I am trying to translate a raw SQL query in my model to use ActiveRecord Query Interface. I think that I translated the query correctly, but I cannot transform it to an array in order to render it.

Here is my model:

class Pgdb < ActiveRecord::Base

  self.abstract_class = true
  self.table_name = 'test'

  if Rails.env.development?
    establish_connection :pg_development
  end

  def self.getInfo(name)
    get = Pgdb.where(city: "New York")
    get_a = get.to_a
    get_a
  end
end

The raw SQL query, which I was able to render, was:

get = connection.query("SELECT * FROM test WHERE city = "New York")

As the above code indicates, I am accessing an external PostgreSQL database and have tried to convert the ActiveRecord object to an array using #to_a but this isn't working. When I try to render it in my view:

<% @info.each do |row| %>
  <tr>
    <% row.each do |element| %>
      <td><%= element %></td>
    <% end %>
  </tr>
<% end %>

I get an error: undefined method 'each' for #<Pgdb:0x007fd37af79040>. I have tried using to_a on the object in different places in the code but nothing has worked.

Controller

def index
end

def new
  @thing = Thing.new
end

def create
  @info = Pgdb.getInfo(@thing.something)
  render :index
end

Answer 1

The correct way to connect rails to an external database is using the config/database.yml file:

# SQLite version 3.x
#   gem install sqlite3
#
#   Ensure the SQLite 3 gem is defined in your Gemfile
#   gem 'sqlite3'
#
defaults: &defaults
  adapter: postgresql
  encoding: utf8
  template: template0

# used for test & development
local:
  host: localhost
  username: j_random_user # change this!
  password: p4ssword # change this!

development:
  <<: *defaults
  <<: *local
  database: my_app_development

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *defaults
  <<: *local
  database: my_app_test

production:
  <<: *defaults
  host: "my_app_something.rds.amazonaws.com" # change this!
  username: my_amazon_db_user # change this!
  password: my_amazon_user # change this!
  reconnect: true
  port: 3306

You may want to use a local postgres database for development and mirror the production database with pgbackups.

But your main issue is that your are doing pretty much everything wrong when it comes to creating a rails application. That looks like a PHP example where some clueless soul is reinventing an database manager for the 1000th time.

So here is a fast Rails MVC & ActiveRecord crash course:

Models reflect the objects in your domain. Lets say we have a pet store app.

We of course need a Pet model:

$ rails g model Pet name:string
$ rake db:migrate

This creates a pets table in the database and a Pet class. Note that the table name is plural and the model name is singular.

# app/models/pet.rb
class Pet < ActiveRecord::Base

end 

We can then access the pets by:

Pet.all
Pet.find(1) # find pet where id is 1
# ... etc

And we can create pets by:

pet = Pet.create(name: "Spot")

All of this is covered in most basic rails tutorials.

Connecting without a model.

ActiveRecord::Base.establish_connection
con = ActiveRecord::Base.connection
res = con.execute('SELECT * FROM foo')

Although using ActiveRecord does not really make sense if you are not actually using a model per table and following the MVC conventions somewhat. It's possible, but it gives you no real benefits at all.

Likewise doing Rails without MVC is doable but what's the point?

Using a legacy database

let's say you have a legacy database written by someone who though using Apps Hungarian for database columns (shrug) was cool:

persons:
  intPersonID: Integer, Autoincrement, Primary Key 

And you want to map this to a Rails model

 class User < ActiveRecord::Base
   self.table_name 'persons'
   self.primary_key 'intPersonID'
 end

Or in your case:

class Test < ActiveRecord::Base
  self.table_name 'test' # since it's not "tests"
end

Test.where(city: "New York")

Answer 2

You are receiving the error undefined method 'each' for an instance of Pgdb because your code is trying to iterate over the instance's data attributes in this line:

<% row.each do |element| %>

ActiveRecord instances are not collections of attributes that you can iterate over. Instead, they are objects that respond to messages named for their attributes. In other words, you can do this:

p = Pgdb.first
p.city # because the underlying table has a `city` attribute

but you can't do this:

p.each { |attribute| puts attribute }

However, ActiveRecord provides the attributes accessor for just this very thing. The attributes method returns a hash that you can iterate over with the each method. Therefore, you can do this:

p.attributes.each { |key, value| puts "#{key}: #{value}" }

In your view, you can substitute the inner loop with:

<% row.attributes.each do |key, value| %>
  <td><%= "#{key}: #{value}" %></td>
<% end %>

And this should render the attributes for your instance of Pgdb.

By the way, it is not necessary to cast the result of where into an Array in Pgdb::getInfo. The where query returns an ActiveRecord::Relation object that responds to each, as well as other Enumerable messages like map and select, similarly to an array. In your code, you are successfully iterating over the result set in

<% @info.each do |row| %>

This will work whether you use to_a or not in getInfo. There are good reasons not to cast your result set to an array. For one, ActiveRecord::Relation objects have other capabilities, like scoping, which you may often need to use.

Hope that helps. Happy coding!