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

clemrock · December 16, 2006, 10:30pm

results_1 = select first_name, user_id from table 1;

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:

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!

clemrock · December 17, 2006, 7:25am

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

<% for address in @person.addresses -%>
<%= "#{address.street}, #{address.city}, #{address.state},
#{address.zip} %>

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

<%= render :partial => ‘address’, :collection => person.addresses %>

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

Hope this gets you going in the right direction.

Aaron

clemrock · December 17, 2006, 11:05pm

Aaron,

Thanks for getting back to me on this. I’ve been reading about this
aproach and I’ve tried to implemented it in my project.

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

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

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

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

Most frustrating!

Aaron wrote:

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

<% for address in @person.addresses -%>
<%= "#{address.street}, #{address.city}, #{address.state},
#{address.zip} %>

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

<%= render :partial => ‘address’, :collection => person.addresses %>

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

Hope this gets you going in the right direction.

Aaron

clemrock · December 17, 2006, 11:19pm

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.

askegg wrote:

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.

clemrock · December 18, 2006, 2:47am

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…

clemrock · December 17, 2006, 11:13pm

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.

clemrock · December 18, 2006, 6:42am

askegg wrote:

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…

When I try this I then get another error message:
Unknown column ‘users.user_id’ in ‘on clause’:

I also tried this, which seemed more than logical:

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

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

and then I get this error: undefined method `loaded’

I’m ready to scream!

clemrock · December 18, 2006, 7:12am

askegg wrote:

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…

When I try this I then get another error message:
Unknown column ‘users.user_id’ in ‘on clause’:

I also tried this, which seemed more than logical:

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

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

and then I get this error: undefined method `loaded’

I’m ready to scream!

clemrock · December 18, 2006, 7:02am

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?

clemrock · December 18, 2006, 7:42am

askegg wrote:

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…

Again thanks so much for your help.

You are exactly right on your assumptions about the layout of the 3
tables.

Here’s the declarations of the 3 models to show you how complex this
site is:

==== Account =====================================

class Account < ActiveRecord::Base
include ActionView::Helpers::NumberHelper
belongs_to :user
has_one :form_of_payment, :dependent => :destroy
has_one :account_plan, :dependent => :destroy
has_many :entries, :dependent => :delete_all, :order => ‘created_at,
id’

has_many :spaces, :foreign_key => “user_id”

has_many :payments,
:class_name => Entry,
:conditions => “code = ‘payment’”,
:order => ‘created_at, id’
has_many :charges,
:class_name => Entry,
:conditions => “code = ‘charge’”,
:order => ‘created_at, id’
has_many :credits,
:class_name => Entry,
:conditions => “code = ‘credit’”,
:order => ‘created_at, id’
has_many :debits,
:class_name => Entry,
:conditions => “code = ‘debit’”,
:order => ‘created_at, id’

==== Spaces ======================================

belongs_to :user
belongs_to :account, :foreign_key => “user_id” #******

belongs_to :owner, :class_name => ‘User’, :foreign_key => ‘owner_id’
belongs_to :home_page, :class_name => ‘WikiPage’, :foreign_key =>
‘home_page_id’
has_many :folders, :dependent => :destroy
has_many :folder_items, :dependent => :destroy
has_many :box_sets, :dependent => :destroy
has_many :boxes, :dependent => :destroy
has_one :root_folder
has_one :trash_folder
has_one :articles_folder
has_one :wiki_pages_folder
has_one :public_category, :class_name => ‘Category’, :conditions =>
“title = ‘Public’”
has_one :uncategorized_category, :class_name => ‘Category’,
:conditions => “title = ‘Uncategorized’”
has_many :pages, :dependent => :destroy
has_many :articles,
:dependent => :destroy,
:conditions => “type = ‘Article’”
has_many :wiki_pages,
:dependent => :destroy,
:conditions => “type = ‘WikiPage’”
has_many :dailies, :dependent => :delete_all
has_many :categories, :dependent => :destroy
has_many :space_resources,
:dependent => :destroy,
:conditions => “type = ‘SpaceResource’”
has_many :attachments,
:dependent => :destroy,
:conditions => “type = ‘Attachment’”
has_many :space_files,
:dependent => :destroy,
:conditions => “type = ‘SpaceFile’”
has_many :searches, :dependent => :delete_all
has_many :page_semaphores, :dependent => :delete_all
has_many :memberships, :dependent => :destroy
has_many :tabs, :order => ‘position asc’, :dependent => :delete_all
has_many :preferences, :dependent => :delete_all
has_many :invites, :dependent => :delete_all
has_many :comments, :dependent => :destroy
has_many :trackbacks, :dependent => :destroy
has_many :events, :dependent => :destroy
has_many :activities, :dependent => :delete_all
has_many :bookmarks, :dependent => :delete_all
has_many :members, :class_name => ‘User’, :through => :memberships,
:source => :user
has_one :calendar, :dependent => :destroy
has_one :blog_roll, :dependent => :destroy
has_space_resource :logo_resource
has_space_resource :banner_resource
has_space_resource :stylesheet_resource
has_space_resource :favicon_resource
has_one :anonymous_membership, :class_name => ‘Membership’,
:conditions => ‘memberships.user_id = #{User.anonymous_user.id}’
has_one :prototype_membership, :class_name => ‘Membership’,
:conditions => ‘memberships.user_id = #{User.prototype_user.id}’

==== users =======================================

belongs_to :last_space, :class_name => ‘Space’, :foreign_key =>
‘last_space_id’
has_one :account, :dependent => :destroy
has_many :spaces, :dependent => :destroy

has_many :owned_spaces, :class_name => ‘Space’, :foreign_key =>
‘owner_id’, :conditions => ‘closed_on IS NULL’
has_many :pages, :dependent => :destroy
has_many :comments, :dependent => :delete_all
has_many :resources, :dependent => :destroy
has_many :events, :dependent => :destroy
has_many :memberships, :dependent => :destroy
has_many :preferences, :dependent => :delete_all
has_many :page_semaphores, :dependent => :delete_all
has_many :activities

This gives you an idea of why my mind is blown. One questionable line
of code in the User class model is this:

has_one :account, :dependent => :destroy
has_many :spaces, :dependent => :destroy

I don’t know how this could effect the joining of the tables.

Argh!!!

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?

clemrock · December 18, 2006, 7:26am

On Dec 17, 3:19 pm, Clem R. [email protected] wrote:

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

clemrock · December 18, 2006, 8:41am

askegg wrote:

OK.

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

Really - these are the only linkages between the tables:

==== users ====================
id

==== accounts =================
user_id

==== spaces ===================
user_id

from accounts, I just need to display the first_name, last_name field
and from spaces, I just need to display the title field

Thanks again - you’ve been great

clemrock · December 18, 2006, 8:26am

OK.

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

clemrock · December 18, 2006, 11:48am

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

<%= user.first_name %><%= user.last_name %>

<%= space.title %>

<% end %>

clemrock · December 18, 2006, 10:03pm

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:

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

now - this works perfectly!
@accounts = Account.find(:all, :include => :spaces, :limit => 20)

So - We were both on the right track, it was just that there was
something from the outside that was killing it.

Thanks so much for your time - I learned a massive amount from these
posts!

Cheers!

askegg wrote:

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

<%= user.first_name %><%= user.last_name %>

<% user.spaces.each do |space| %>

<%= space.title %>

<% end %>

<% end %>

clemrock · December 18, 2006, 11:15pm

Ahhh - that would have been killing the “Account has_many :spaces”
relationship.

Glad to hear it was a productive exercise. My guess is that there will
be a lot of code you will now refactor as a result of this

Good luck with the application - it certainly look complex enough.

AS

clemrock · December 18, 2006, 11:21pm

Yeah - it was a huge amount of learning and I don’t feel so bad because
that would have been nearly impossible to debug unless you were a black
belt or you actually wrote the program like the guy I asked.

And he’s probably the best programmer I’ve ever met so the learning
curve on his code is going to be steep.

Thanks again!

Andrew S. wrote:

Ahhh - that would have been killing the “Account has_many :spaces”
relationship.

Glad to hear it was a productive exercise. My guess is that there will
be a lot of code you will now refactor as a result of this

Good luck with the application - it certainly look complex enough.

AS

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

==== Account =====================================

==== Spaces ======================================

==== users =======================================

load spaces for a single account

load all accounts and their related spaces

==== users ==================== id

==== accounts ================= user_id

==== spaces =================== user_id

this makes the above a kind of double up ?

this makes the above a kind of double up ?

==== users ====================
id

==== accounts =================
user_id

==== spaces ===================
user_id