Translating SQL query to ActiveRecord and rendering as a table
I am trying to translate the original SQL query in my model to use the ActiveRecord query interface. I think I have translated the request correctly, but I cannot convert it to an array in order to display 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 I was able to display was:
get = connection.query("SELECT * FROM test WHERE city = "New York")
As stated above, I am accessing an external PostgreSQL database and tried to convert the ActiveRecord object to an array using #to_a
, but that doesn't work. When I try to do this in my opinion:
<% @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 tried using to_a
for the object in various places in the code, but nothing worked.
controller
def index
end
def new
@thing = Thing.new
end
def create
@info = Pgdb.getInfo(@thing.something)
render :index
end
source to share
You are getting an error undefined method 'each'
for an instance Pgdb
because your code is trying to iterate over the data attributes of the instance on this line:
<% row.each do |element| %>
ActiveRecord instances are not iterable sets of attributes. Instead, they are objects that respond to messages named by their attributes. In other words, you can do this:
p = Pgdb.first
p.city # because the underlying table has a `city` attribute
but you cannot do this:
p.each { |attribute| puts attribute }
However, ActiveRecord provides an attributes
accessory for just that. The method attributes
returns a hash that can be iterated over using the method each
. Therefore, you can do this:
p.attributes.each { |key, value| puts "#{key}: #{value}" }
In your opinion, you can replace the inner loop with:
<% row.attributes.each do |key, value| %>
<td><%= "#{key}: #{value}" %></td>
<% end %>
And that should render the attributes for your instance Pgdb
.
By the way, no need to result where
in Array
at Pgdb::getInfo
. The request where
returns the object ActiveRecord::Relation
that responds to each
, as well as other messages Enumerable
such as map
and select
, similar to an array. In your code, you successfully re-set the results in
<% @info.each do |row| %>
This will work if you are using to_a
or not in getInfo
. There are good reasons not to cast your result set to an array. First, objects ActiveRecord::Relation
have other capabilities, such as scope, that you often need to use.
Hope it helps. Happy coding!
source to share
The correct way to connect rails to an external database is with a file config/database.yml
:
# 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 can use the local postgres database for development and mirror the production database with pgbackups.
But the main problem is that you are doing almost everything wrong when it comes to creating a rails application. It looks like a PHP example where some ignorant soul reinvents the database manager for the 1000th time.
So here's a quick course on Rails MVC and ActiveRecord:
Models reflect objects in your domain. Let's say we have an animal app.
Of course we need a pet model:
$ rails g model Pet name:string
$ rake db:migrate
This creates a table pets
in the database and a class Pet
. Note that the table name is plural and the model name is unique.
# app/models/pet.rb
class Pet < ActiveRecord::Base
end
Then we can access the pets:
Pet.all
Pet.find(1) # find pet where id is 1
# ... etc
And we can create pets:
pet = Pet.create(name: "Spot")
This is all covered in most basic rail guides .
Connection without model.
ActiveRecord::Base.establish_connection
con = ActiveRecord::Base.connection
res = con.execute('SELECT * FROM foo')
While using ActiveRecord doesn't really make sense unless you actually use a per-table model and follow MVC conventions somewhat. It is possible, but it does not give you any real benefit.
Likewise, 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 the database columns (shrug) was cool:
persons:
intPersonID: Integer, Autoincrement, Primary Key
And you want to map this to the 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 not "tests"
end
Test.where(city: "New York")
source to share