Help w/ pulling data from 2 tables and displaying the result

rubyonrails-talk
1

Clem,

You will likely find it easier and more efficient to offload some of the work the the models and controller. It looks like what you want to do is print out data from two table that have a beongs_to/has_many relationship. Here is a basic example:

Models: class Person < ActiveRecord::Base   has_many :addresses # addresses is plural b/c their can be more than one end

class Address < ActiveRecord::Base   belongs_to :person # person is singular b/c their can be only one end

Controller: class PersonController < ApplicationController   def list     # use :include to load all the people and their addresses with one database query (join)     @people = Person.find(:all, :include => :addresses)   end end

View: <% for person in @people -%>   <%= person.name %><br />   <% for address in @person.addresses -%>     <%= "#{address.street}, #{address.city}, #{address.state}, #{address.zip} %><br />   <% end -%> <% end -%>

You could also use partials to construct the view making you code more reusable (you can call the same partial from somewhere else).

View with partials: <%= render :partial => 'person', :collection => @people %>

_person partial: <%= person.name %><br /> <%= render :partial => 'address', :collection => person.addresses %>

_address partial: <%= "#{address.street}, #{address.city}, #{address.state}, #{address.zip} %><br />

Hope this gets you going in the right direction.

Aaron

2

Firstly, why are you refering to the user_id in the foreign key assignments for accounts and spaces?

Second, why not just create the "account_id" column in your "spaces" table? Rails can then "magically" work out the rest.

3

OK.

In the first instance you have described a relationship that would make rails expect to see "account_id" in the spaces table, but the attempted fix brings "user_id" into the mix. What is the schema of the database for these 2 (3?) models?

The above example seems to suggest:

class Account < ActiveRecord::Base      has_many :spaces, :through => :user end

or similar....

4

I am not sure I am following this or completely understanding how it's all stuck together. Can you post the schema for these tables from you database? The models might come in handy to....

Your examples seem to suggest the following (psudeo code):

Account Table---   id, :int   name, :string   .... end

Spaces Table --   id, :int   name, :string   account_id, :int end

By specifying the foreign key the way you have, changes the spaces table to:

Spaces Table --   id, :int   name, :string   user_id, :int end

BUT, your mention of user_id leads me to suspect the following:

Account Table---   id, :int   name, :string   user_id, :int   .... end

Spaces Table --   id, :int   name, :string   user_id, :int end

UserTable---   id, :int   name, :string   .... end

In which case the models would actually be:

class Account < ActiveRecord::Base   belongs_to :user   has_many :spaces, :through => :user end

class Space < ActiveRecord::Base   belongs_to :user end

class User < ActiveRecord::Base   has_many :accounts   has_many :spaces end

UNLESS "accounts" really means "users" and the tables/class name are actually different.

See why we need the models and schema?

5

This is a huge project I am building an addition to and I don't want to start modifying database tables to fit the need of one tiny module especially when we have the natural linkage of user_id in both the accounts and spaces table.

In a has_many/belongs_to relationship rails expects the belongs_to table to contain a foreign key that matches the primary key of the has_many table. That's why it came up with an error looking for account_id.

From reading your entry is sounds like both the Space and Account

models reference User but do not reference each other directly. Maybe something like this will work:

class User < ActiveRecord::Base   has_many :spaces   has_many :accounts end

class Space < ActiveRecord::Base   belongs_to :user   has_many :accounts, :through => :user end

class Account < ActiveRecord::Base   belongs_to :user   has_many :spaces, :through => :user end

Then: # load spaces for a single account @account.spaces

# load all accounts and their related spaces Account.find(:all, :include => :spaces)

(warning: I've never tried using a through table with has_many relationships in this direction. From the API it looks like this should work.)

Aaron

6

OK.

What do the relevant parts of the database tables look like?

7

Hmmm.

If I am reading this right, the relationship between Accounts and Spaces is through Users, so: class Account < ActiveRecord::Base   belongs_to :user   has_many :spaces, :through => :users

Spaces belongs to User and Account (but through User as there is no account_id field), so: class Spaces < ActiveRecord::Base   belongs_to :user   belongs_to :account, :through => :user # Not sure how this inforces only 1 account.   belongs_to :owner, :class_name => 'User', :foreign_key => 'owner_id' # this makes the above a kind of double up ?

Users have one Account and many Spaces (both direct and through Accounts), so: class User < ActiveRecord::Base   belongs_to :last_space, :class_name => 'Space', :foreign_key => 'last_space_id' ###??? Belongs_to or Has_many? Man, this gets confusing....   has_one :account, :dependent => :destroy   has_many :spaces, :dependent => :destroy   has_many :owned_spaces, :through => :accounts, :class_name => 'Space', :foreign_key => 'owner_id', :conditions => 'closed_on IS NULL' ### Not sure about this..

Wow - I need to sit down, have a stiff drink and try to draw this one!

In any case, given the models you should be able to do this:

UserController   def list     @users = User.find(:all)   end

UserView   <% @users.each do |user| %>     <tr> <td> <%= user.first_name %><%= user.last_name %> </td>      <ul>       <% user.spaces.each do |space| %>        <li><%= space.title %></li>       <% end %>      </ul>   <% end %>