thr3ads.net - Rails - Help w/ pulling data from 2 tables and displaying the result [Dec 2006]

If this information is useful, please help other people find it:
Share via:

Clem Rock

2006-Dec-16 21:30 UTC

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

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:

[code]
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>"
}
[/code]

The display would look like this for 1 row:

joe | -info 1
      -info 2
      -info 3

Help - I''m desparate!

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Aaron

2006-Dec-17 06:23 UTC

head link

Re: 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


On Dec 16, 2:30 pm, Clem Rock
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:> 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:
>
> [code]
> 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>"}[/code]
>
> The display would look like this for 1 row:
>
> joe | -info 1
>       -info 2
>       -info 3
>
> Help - I''m desparate!
> 
> --
> Posted viahttp://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Clem Rock

2006-Dec-17 22:05 UTC

head link

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

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:

[code]
class Account < ActiveRecord::Base

     has_many :spaces

end

class Space < ActiveRecord::Base
  belongs_to :account
end
[/code]

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

[code]
@accounts = Account.find(:all, :include => :spaces )
[/code]

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:

[code]
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
[/code]

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

[code]
@accounts = Account.find(:all, :include => :spaces )
[/code]

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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

askegg

2006-Dec-17 22:11 UTC

head link

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

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.


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Clem Rock

2006-Dec-17 22:19 UTC

head link

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

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.

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

askegg

2006-Dec-18 01:46 UTC

head link

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

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


On Dec 18, 9:19 am, Clem Rock
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
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.
>
> 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.--
> Posted viahttp://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Clem Rock

2006-Dec-18 05:42 UTC

head link

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

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:

[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
[/code]

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

I''m ready to scream!



-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

askegg

2006-Dec-18 05:59 UTC

head link

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

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?


On Dec 18, 4:42 pm, Clem Rock
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:> 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:
>
> [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
> [/code]
>
> and then I get this error: undefined method `loaded''
>
> I''m ready to scream!
> 
> --
> Posted viahttp://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Clem Rock

2006-Dec-18 06:12 UTC

head link

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

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:

[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
[/code]

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

I''m ready to scream!


-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Aaron

2006-Dec-18 06:25 UTC

head link

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

On Dec 17, 3:19 pm, Clem Rock
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
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 Accountmodels 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


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Clem Rock

2006-Dec-18 06:42 UTC

head link

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

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?

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

askegg

2006-Dec-18 07:25 UTC

head link

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

OK.

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


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Clem Rock

2006-Dec-18 07:41 UTC

head link

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

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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

askegg

2006-Dec-18 10:46 UTC

head link

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

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


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Clem Rock

2006-Dec-18 21:03 UTC

head link

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

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| %>
>     <tr> <td> <%= user.first_name %><%= user.last_name
%> </td>
>      <ul>
>       <% user.spaces.each do |space| %>
>        <li><%= space.title %></li>
>       <% end %>
>      </ul>
>   <% end %>

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Andrew Skegg

2006-Dec-18 22:15 UTC

head link

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

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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Clem Rock

2006-Dec-18 22:21 UTC

head link

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

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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Rails - Dec 2006 - Help w/ pulling data from 2 tables and displaying the result

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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