Convert executed SQL result to a list of Model object

Question

I'm wondering that is it possible to convert the executed query result to a list of models.

I'm using Ruby with ActiveRecord and need to execute custom SQL query to join two or many tables. The code looks like below:

connection = ActiveRecord::Base.connection
sql = "select T1.f1, T2.f2 from T1 left join T2 on T1.id = T2.id"
@result = connection.execute(sql)

In Ruby code, I defined a models to manage the executed SQL result:

class Model
  property :f1, :f2
end

Is there any way to convert @result to list of Model object? so I can deal with each item in the list as following

@result.each do |item|
  puts item.f1
  puts item.f2
end

Indispensable

Answer 1

Have your model inherit ActiveRecord::Base

class Model < ActiveRecord::Base
end

Then you can just do it like this

connection = ActiveRecord::Base.connection
sql = "select T1.f1, T2.f2 from T1 left join T2 on T1.id = T2.id"
@result = Model.find_by_sql(sql)

puts @result.f1

Harding

Thanks for your comment but the statement result.f1 doesn't work because result is not the list of type Model.Could you explain more? – Indispensable

@Indispensable Sorry, edited the answer. Try again with this, forgot to remove execute and put find_by_sql instead. – Harding

It doesn't work. this error raised "ActiveRecord::StatementInvalid - PG::UndefinedTable: ERROR: relation "models" does not exist". It means I have to define a Models talbe/view in database. I want to execute my custom sql query and convert the result to Model type which I can manipulate easier! – Indispensable

@Indispensable You have to change your class Model to the name of your table, that is how ActiveRecord works. – Harding

Sorry for late reply. It works properly :). All things I have to do is declare a Models table in database with no columns like this: CREATE TABLE models (). Then I can use the Model.find_by_sql in my ruby code. Thanks a lot for your help! – Indispensable

the @result show collection. Then the corrent is one position of this collection @result.first.f1 – Augusto

Answer 2

What I've done for now looks like this:

ActiveRecord::Base.connection_pool.with_connection do
 connection = ActiveRecord::Base.connection
 sql = "SELECT a.topic_seq, a.topic_id, a.topic_user_seq, a.topic_title, a.topic_header, t.tag_name AS topic_tag_name, to_char(a.topic_createtime::timestamp with time zone, 'Day, YYYY Mon DD'::text) AS topic_createtime FROM topics a LEFT JOIN tags t ON a.topic_tag = t.tag_seq;"
 results = connection.execute(sql)
 results.each do |row|
  obj = Model.new(row['topic_seq', row['topic_id'], row['topic_user_seq']...], 
  puts obj.topic_seq
  puts obj.topic_id
  ...
 end
end

I'm just wondering is there another way to cast a row to Model object more easily instead of using the Model constructor?

Indispensable

Answer 3

Create one generic model named example "GenericObject":

 class GenericObject < ActiveRecord::Base    
 end

Execute sql and set table_name:

sql = "select T1.f1, T2.f2 from T1 left join T2 on T1.id = T2.id"
go = GenericObject
go.table_name = "T1" #set this for do not show message "doesn't exist: SHOW FULL FIELDS FROM"
result = go.find_by_sql(sql)

Get the result:

p result
# [{"f1": 1, "f2": "x"}, {"f1": 2, "f2": "y"}]
p result.first.f1
# 1

Augusto