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

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

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.

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....

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?

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

OK.

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

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 %>