Rails: selecting columns from multiple tables

Question

I am new to Rails. I have three tables a, b, c

b has 2 columns: b1 and b2

c has 2 columns: c1 and c2

a has 3 columns: a1, b1 (foreign key) and c1 (foreign key)

I want to get the distinct (b2, c2) pairs by giving a value for a1

I tried something like

a.find(:all, :joins => [:b, :c], :select => "b2, c2", :conditions => {:a => {:a1 => Time.now.midnight. ... Time.now}}, :group => "b2, c2")

The SQL that this produces works fine and I am able to see the results. But I think since I am doing a a.find, I am not able to retrieve b2, c2 from the result set.

How can I modify this so that I can get b2 and c2?

Kevin Bedell · Accepted Answer · 2012-05-07 22:07:17Z

4

If you modify your :select clause to read:

foo = a.find(:all, 
       :joins => [:b, :c], 
       :select => "distinct b.b2 as b2_value, c.c2 as c2_value", 
       :conditions => {:a => {:a1 => Time.now.midnight. ... Time.now}}, 
       :group => "b.b2, c.c2")

I believe you will retrieve an array of records. Then try the following:

b2_value = foo.first["b2_value"]

That should retrieve the value you are looking for.

Also, note that in the query I specified the table name with the columns wherever I used them -- I think it's better practice and will also avoid bad queries when you have duplicate column names (for example, created_at or id).

answered May 7, 2012 at 22:07

Kevin Bedell

13.5k10 gold badges80 silver badges116 bronze badges

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

Comments

Tom Meinlschmidt · Accepted Answer · 2012-05-07 21:56:02Z

0

try

a.find(:all, :include => [:b, :c], :select => "distinct b2, c2", :conditions ...)

answered May 7, 2012 at 21:56

Tom Meinlschmidt

2071 silver badge3 bronze badges

Collectives™ on Stack Overflow

Rails: selecting columns from multiple tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related