I''m trying to replicate a join in RoR that I can do in SQL very
simply.
Three tables/models that I have are: Users, Users_Activities,
Activities and Codes.
Users and Activities have appropriate has_many associations through
Users_Activities model, so it''s easy to do a join to get all
activities for a given user.
Codes, on the other hand, is a different story. It''s kind of a
"repository" of all codified data. For example, the Activity table
has a "Activity Type" field that is an integer field. To get the
actual Activity Type name, you have to look it up in the Codes table
like so (joining with user table to get all activities for user id
"1"):
select activities.*, codes.name from activities, users_activities,
codes
where users_activities.user_id = 1
and users_activities.activity_id = activities.id
and activities.activity_type = codes.id
I can do part of the above query if i don''t include the Codes table
like so:
Activity.all(:joins => :users_activities, :conditions =>
{''users_activities.user_id'' => "1"})
How do I also join Codes?
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
joins => [:users_activities, :codes] On Mar 30, 2:03 pm, khagimoto <kumi.hagim...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I''m trying to replicate a join in RoR that I can do in SQL very > simply. > > Three tables/models that I have are: Users, Users_Activities, > Activities and Codes. > > Users and Activities have appropriate has_many associations through > Users_Activities model, so it''s easy to do a join to get all > activities for a given user. > > Codes, on the other hand, is a different story. It''s kind of a > "repository" of all codified data. For example, the Activity table > has a "Activity Type" field that is an integer field. To get the > actual Activity Type name, you have to look it up in the Codes table > like so (joining with user table to get all activities for user id > "1"): > > select activities.*, codes.name from activities, users_activities, > codes > where users_activities.user_id = 1 > and users_activities.activity_id = activities.id > and activities.activity_type = codes.id > > I can do part of the above query if i don''t include the Codes table > like so: > > Activity.all(:joins => :users_activities, :conditions => > {''users_activities.user_id'' => "1"}) > > How do I also join Codes?-- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Tue, Mar 30, 2010 at 3:03 PM, khagimoto <kumi.hagimoto-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I''m trying to replicate a join in RoR that I can do in SQL very > simply. > > Three tables/models that I have are: Users, Users_Activities, > Activities and Codes. > > Users and Activities have appropriate has_many associations through > Users_Activities model, so it''s easy to do a join to get all > activities for a given user. > > Codes, on the other hand, is a different story. It''s kind of a > "repository" of all codified data. For example, the Activity table > has a "Activity Type" field that is an integer field. To get the > actual Activity Type name, you have to look it up in the Codes table > like so (joining with user table to get all activities for user id > "1"): > > select activities.*, codes.name from activities, users_activities, > codes > where users_activities.user_id = 1 > and users_activities.activity_id = activities.id > and activities.activity_type = codes.id > > I can do part of the above query if i don''t include the Codes table > like so: > > Activity.all(:joins => :users_activities, :conditions => > {''users_activities.user_id'' => "1"}) > > How do I also join Codes?class User < AcrtiveRecord::Base has_many :user_activities end class UserActivity < ActiveRecord::Base belongs_to :user belongs_to :code, :foreign_key => "activity_type" end Activity.find_all_by_user_id(1, :include => :code) This will return a collection of all of the activities for user#1 with attached instances of the code for each activity -- Rick DeNatale Blog: http://talklikeaduck.denhaven2.com/ Twitter: http://twitter.com/RickDeNatale WWR: http://www.workingwithrails.com/person/9021-rick-denatale LinkedIn: http://www.linkedin.com/in/rickdenatale -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Thanks, but this doesn''t seem to get me the fields from the Codes in
the ResultSet - and it also tries to do
activities.id = codes.id
when it should really be
activities.activity_type = codes.id
will keep looking - thanks though!
On Mar 30, 1:04 pm, Me <chabg...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> joins => [:users_activities, :codes]
>
> On Mar 30, 2:03 pm, khagimoto
<kumi.hagim...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > I''m trying to replicate a join in RoR that I can do in SQL
very
> > simply.
>
> > Three tables/models that I have are: Users, Users_Activities,
> > Activities and Codes.
>
> > Users and Activities have appropriate has_many associations through
> > Users_Activities model, so it''s easy to do a join to get all
> > activities for a given user.
>
> > Codes, on the other hand, is a different story. It''s kind of
a
> > "repository" of all codified data. For example, the
Activity table
> > has a "Activity Type" field that is an integer field. To
get the
> > actual Activity Type name, you have to look it up in the Codes table
> > like so (joining with user table to get all activities for user id
> > "1"):
>
> > select activities.*, codes.name from activities, users_activities,
> > codes
> > where users_activities.user_id = 1
> > and users_activities.activity_id = activities.id
> > and activities.activity_type = codes.id
>
> > I can do part of the above query if i don''t include the Codes
table
> > like so:
>
> > Activity.all(:joins => :users_activities, :conditions =>
> > {''users_activities.user_id'' => "1"})
>
> > How do I also join Codes?
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
Not sure if I understand this completely.. The "activity_type" field
is in Activity.
If I try to do as you suggested, it complains that
"Association named ''code'' was not found"
when it tries to execute the line Activity.find...
On Mar 30, 1:05 pm, Rick DeNatale
<rick.denat...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> On Tue, Mar 30, 2010 at 3:03 PM, khagimoto
<kumi.hagim...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > I''m trying to replicate a join in RoR that I can do in SQL
very
> > simply.
>
> > Three tables/models that I have are: Users, Users_Activities,
> > Activities and Codes.
>
> > Users and Activities have appropriate has_many associations through
> > Users_Activities model, so it''s easy to do a join to get all
> > activities for a given user.
>
> > Codes, on the other hand, is a different story. It''s kind of
a
> > "repository" of all codified data. For example, the
Activity table
> > has a "Activity Type" field that is an integer field. To
get the
> > actual Activity Type name, you have to look it up in the Codes table
> > like so (joining with user table to get all activities for user id
> > "1"):
>
> > select activities.*, codes.name from activities, users_activities,
> > codes
> > where users_activities.user_id = 1
> > and users_activities.activity_id = activities.id
> > and activities.activity_type = codes.id
>
> > I can do part of the above query if i don''t include the Codes
table
> > like so:
>
> > Activity.all(:joins => :users_activities, :conditions =>
> > {''users_activities.user_id'' => "1"})
>
> > How do I also join Codes?
>
> class User < AcrtiveRecord::Base
> has_many :user_activities
> end
>
> class UserActivity < ActiveRecord::Base
> belongs_to :user
> belongs_to :code, :foreign_key => "activity_type"
> end
>
> Activity.find_all_by_user_id(1, :include => :code)
>
> This will return a collection of all of the activities for user#1 with
> attached instances of the code for each activity
>
> --
> Rick DeNatale
>
> Blog:http://talklikeaduck.denhaven2.com/
> Twitter:http://twitter.com/RickDeNatale
> WWR:http://www.workingwithrails.com/person/9021-rick-denatale
> LinkedIn:http://www.linkedin.com/in/rickdenatale
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
On Tue, Mar 30, 2010 at 6:35 PM, khagimoto <kumi.hagimoto-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Not sure if I understand this completely.. The "activity_type" field > is in Activity. > > If I try to do as you suggested, it complains that > "Association named ''code'' was not found" > when it tries to execute the line Activity.find... >Did you add the association declaration: belongs_to :code, :foreign_key => "activity_type" to the Activity class? That says that the activities table has a foreign key field called activity_type which is the key of a record in the codes table. And it creates the association called code which the error message is complaining about.> On Mar 30, 1:05 pm, Rick DeNatale <rick.denat...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> On Tue, Mar 30, 2010 at 3:03 PM, khagimoto <kumi.hagim...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> > I''m trying to replicate a join in RoR that I can do in SQL very >> > simply. >> >> > Three tables/models that I have are: Users, Users_Activities, >> > Activities and Codes. >> >> > Users and Activities have appropriate has_many associations through >> > Users_Activities model, so it''s easy to do a join to get all >> > activities for a given user. >> >> > Codes, on the other hand, is a different story. It''s kind of a >> > "repository" of all codified data. For example, the Activity table >> > has a "Activity Type" field that is an integer field. To get the >> > actual Activity Type name, you have to look it up in the Codes table >> > like so (joining with user table to get all activities for user id >> > "1"): >> >> > select activities.*, codes.name from activities, users_activities, >> > codes >> > where users_activities.user_id = 1 >> > and users_activities.activity_id = activities.id >> > and activities.activity_type = codes.id >> >> > I can do part of the above query if i don''t include the Codes table >> > like so: >> >> > Activity.all(:joins => :users_activities, :conditions => >> > {''users_activities.user_id'' => "1"}) >> >> > How do I also join Codes? >> >> class User < AcrtiveRecord::Base >> has_many :user_activities >> end >> >> class UserActivity < ActiveRecord::Base >> belongs_to :user >> belongs_to :code, :foreign_key => "activity_type" >> end >> >> Activity.find_all_by_user_id(1, :include => :code) >> >> This will return a collection of all of the activities for user#1 with >> attached instances of the code for each activity >> >> -- >> Rick DeNatale >> >> Blog:http://talklikeaduck.denhaven2.com/ >> Twitter:http://twitter.com/RickDeNatale >> WWR:http://www.workingwithrails.com/person/9021-rick-denatale >> LinkedIn:http://www.linkedin.com/in/rickdenatale > > -- > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. > For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- Rick DeNatale Blog: http://talklikeaduck.denhaven2.com/ Twitter: http://twitter.com/RickDeNatale WWR: http://www.workingwithrails.com/person/9021-rick-denatale LinkedIn: http://www.linkedin.com/in/rickdenatale -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Ooooooooh.. ok - I moved that line from UserActivity class and it''s working now. Thanks so much for your help and explanation! On Mar 30, 4:21 pm, Rick DeNatale <rick.denat...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Tue, Mar 30, 2010 at 6:35 PM, khagimoto <kumi.hagim...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > Not sure if I understand this completely.. The "activity_type" field > > is in Activity. > > > If I try to do as you suggested, it complains that > > "Association named ''code'' was not found" > > when it tries to execute the line Activity.find... > > Did you add the association declaration: > > belongs_to :code, :foreign_key => "activity_type" > > to the Activity class? > > That says that the activities table has a foreign key field called > activity_type which is the key of a record in the codes table. > > And it creates the association called code which the error message is > complaining about. > > > > > On Mar 30, 1:05 pm, Rick DeNatale <rick.denat...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > >> On Tue, Mar 30, 2010 at 3:03 PM, khagimoto <kumi.hagim...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > >> > I''m trying to replicate a join in RoR that I can do in SQL very > >> > simply. > > >> > Three tables/models that I have are: Users, Users_Activities, > >> > Activities and Codes. > > >> > Users and Activities have appropriate has_many associations through > >> > Users_Activities model, so it''s easy to do a join to get all > >> > activities for a given user. > > >> > Codes, on the other hand, is a different story. It''s kind of a > >> > "repository" of all codified data. For example, the Activity table > >> > has a "Activity Type" field that is an integer field. To get the > >> > actual Activity Type name, you have to look it up in the Codes table > >> > like so (joining with user table to get all activities for user id > >> > "1"): > > >> > select activities.*, codes.name from activities, users_activities, > >> > codes > >> > where users_activities.user_id = 1 > >> > and users_activities.activity_id = activities.id > >> > and activities.activity_type = codes.id > > >> > I can do part of the above query if i don''t include the Codes table > >> > like so: > > >> > Activity.all(:joins => :users_activities, :conditions => > >> > {''users_activities.user_id'' => "1"}) > > >> > How do I also join Codes? > > >> class User < AcrtiveRecord::Base > >> has_many :user_activities > >> end > > >> class UserActivity < ActiveRecord::Base > >> belongs_to :user > >> belongs_to :code, :foreign_key => "activity_type" > >> end > > >> Activity.find_all_by_user_id(1, :include => :code) > > >> This will return a collection of all of the activities for user#1 with > >> attached instances of the code for each activity > > >> -- > >> Rick DeNatale > > >> Blog:http://talklikeaduck.denhaven2.com/ > >> Twitter:http://twitter.com/RickDeNatale > >> WWR:http://www.workingwithrails.com/person/9021-rick-denatale > >> LinkedIn:http://www.linkedin.com/in/rickdenatale > > > -- > > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > > To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > > For more options, visit this group athttp://groups.google.com/group/rubyonrails-talk?hl=en. > > -- > Rick DeNatale > > Blog:http://talklikeaduck.denhaven2.com/ > Twitter:http://twitter.com/RickDeNatale > WWR:http://www.workingwithrails.com/person/9021-rick-denatale > LinkedIn:http://www.linkedin.com/in/rickdenatale-- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.