Topic: Help w/ pulling data from 2 tables and displaying the results

Hello,

I'm brand new to ruby and I'm having trouble pulling data from one
table (table_1), looping through the results and pulling data from
another table (table_2) and displaying the results of table_2 in one
field.

the pseudo code would be as follows:

result_1 = select first_name from table_1
loop results_1
{
      puts "<tr><td> results_1.first_name<td>"

      results_2 = select table_2.info from table_2 where table_1.user_id = table_2.user_id;
        puts "<td>"
        loop results_2
        {
             puts "<li>table_2.info
        }
        puts "</td>"
}


The display would look like this for 1 row:

joe | -info 1
-info 2
-info 3

Help - I'm desparate!
clem_c_rock is online now Report Post Edit/Delete Message

Re: Help w/ pulling data from 2 tables and displaying the results

Here's what I'm trying right now:

The 2 tables I have are accounts and spaces.   The accounts table will
have many spaces connecting the 2 tables by the foreign key user_id.

so I tried my 2 model declarations as follow:

class Account < ActiveRecord::Base
    has_many :spaces
end

class Space < ActiveRecord::Base
  belongs_to :account
end


Then I try to call the 2 tables w/ this:

@accounts = Account.find(:all, :include => :spaces )

Then I get this error:

Unknown column 'spaces.account_id' in 'on clause':

Somehow - it was getting the idea that account_id was the foreign key so
I did this:

class Account < ActiveRecord::Base
     has_many :spaces, :class_name => 'Account', :foreign_key =>
"user_id"
end

class Space < ActiveRecord::Base
    belongs_to :account, :class_name => 'Space', :foreign_key =>
"user_id"
end


and now I get this error: undefined method `loaded'

Most frustrating!

Last edited by clem_c_rock (2006-12-17 18:12:21)

Re: Help w/ pulling data from 2 tables and displaying the results

Something isn't right in how you're declaring your relationships.  You have two tables, and spaces is a child of account.  So you should have a column in spaces called "account_id".  You can call it something else and specify the key, but let's assume you're using the Rails naming standard.

You declare your two classes like this:

class Account < ActiveRecord::Base
  has_many :spaces
end

class Space < ActiveRecord::Base
  belongs_to :account
end


If you want to use user_id as the foreign key instead of account_id (probably not the best idea), but it's doable, you need to do the following:

class Account < ActiveRecord::Base
  has_many :spaces, :foreign_key => "user_id"
end

class Space < ActiveRecord::Base
  belongs_to :account, :foreign_key => "user_id"
end


In your second set of code, it looks like you have the class names backward when mapping the associations.  You can leave them out entirely since they can be inferred from the relationship name. (Or you can leave them but you need to swap them.)

Re: Help w/ pulling data from 2 tables and displaying the results

yeah - I've tried this section of code:

class Account < ActiveRecord::Base
    has_many :spaces, :foreign_key => "user_id"
end
 
class Space < ActiveRecord::Base
      belongs_to :account, :foreign_key => "user_id"
end

It completely seems logical but when I try to call the databases in this matter:

@accounts = Account.find(:all, :include => :spaces )

I get this error:   undefined method `loaded'

I just don't get it

Re: Help w/ pulling data from 2 tables and displaying the results

Hmm.  If you have the association set up right I think all you need to do to get to the fields in spaces is:

@accounts = Account.find(:all)
spacesFieldValue = @accounts[<n>].spaces[<m>].<fieldname>

where <xxx> are replaced by real values.

Re: Help w/ pulling data from 2 tables and displaying the results

Ok - I finally buckled down and asked a couple of questions and there
was a method in the accounts model that was killing this whole concept:
Problem solved!

The project I'm building a little addition to is huge and extremely complex so there's a lot of code that could overide or clash w/ code I'm trying to develop.

That was the case here.   In the Account model there was this method that was overiding my join attempts.

def spaces
    spaces = Space.find :all, :conditions => ['owner_id = ?',
self.user_id]
end

Now, once I got rid of that method - this works perfectly!
@accounts = Account.find(:all, :include => :spaces, :limit => 20)

I got dumped right into the fire w/ this new project - it pays to ask a few simple questions.   I tore a lot of hair out trying to figure it out, but also learned a great deal trying to find the answer.

posts!

Cheers!