Displaying data from multiple tables in Rails

Question

I have multiple tables that are related by primary key-foreign key relationship. Also, I have models that are independent of each other on top of these tables. Now, I need a view that displays the data from multiple tables. How should I do this? Should I create a dummy model with attributes from each of the table? If so, how and where do I perform the query to these multiple tables. Any code snippets will be of great help.

To be more clear, here is an example. Assume these are the following tables.

Table1: pk, attr1, attr2, attr3, attr4, attr_fk_table2, attr_fk_table3, attr_fk_table4

Table2: pk, attr1, attr2, attr3, attr4,attr5

Table3: pk, attr1, attr2, attr3

Table4: pk, attr1, attr2, attr3

Also the models of tables 1,2,3,4 are independent. I mean to say there is no has_one or belongs_to relation between them at the model level.

Now I need a view with the following attributes

Table1:attr1, Table1:attr2, Table2:attr5, Table3:attr3, Table4: attr2

How can I do this?

Thanks

Wilson Freitas · Accepted Answer · 2011-04-20 19:11:31Z

1

Use find_by_sql:

sql = %{
  select
    t1.attr1, t1.attr2, t2.attr5, t3.attr3, t4.attr2
  from
    Table1 t1, Table2 t2, Table3 t3, Table4 t4
  where
    t1.attr_fk_table2 = t2.pk
    and t1.attr_fk_table3 = t3.pk
    and t1.attr_fk_table4 = t4.pk
}

result = find_by_sql(sql)

answered Apr 20, 2011 at 19:11

Wilson Freitas

5834 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rajaramyadhav Over a year ago

Hi. Thanks for your reply. Each row returned by this will be an array with all the attributes in them. But I need objects of some class so that I can paginate them. Do you have any idea on this?

Wilson Freitas Over a year ago

I don't use Rails pagination myself. I like to do that using grid components like jqGrid. Google for 'rails paginate_by_sql'. Maybe this is what you want.

tadman · Accepted Answer · 2011-04-20 19:13:27Z

0

It sounds like what you need to do is write a query that aggregates the data into the correct structure, then simply fetch it:

ActiveRecord::Base.connection.select_rows("
  SELECT table1.attr1, table1.attr2, table2.attr5, table3.attr3, table4.attr2
   FROM table1
   LEFT JOIN table2 ON table1.attr_fk_table2=table2.pk
   LEFT JOIN table3 ON table1.attr_fk_table3=table3.pk
   LEFT JOIN table4 ON table1.attr_fk_table4=table4.pk
").each do |row|
  # ...
end

You'll get the data back as a series of rows where item 0 is table1.attr and so forth.

answered Apr 20, 2011 at 19:13

tadman

212k23 gold badges237 silver badges266 bronze badges

2 Comments

rajaramyadhav Over a year ago

Hi. Thanks for your reply. Each row returned by this will be an array with all the attributes in them. But I need objects of some class so that I can paginate them. Do you have any idea on this?

tadman Over a year ago

You may be able to create a database VIEW that maps these into the columns you require using the same sort of select statement, then use that instead of a regular table. Rails is not always happy with finding VIEW-type tables in its schema, but it can be coached to do it.

Collectives™ on Stack Overflow

Displaying data from multiple tables in Rails

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related