Hi all. New to the list. Hope to be able to contribute something to the community soon, for now I have a begginer''s question. I found the examples on the Rails wiki for many-to-many model relationships very useful. However, I can''t get my head around "the rails way" to do the next step: link that resulting many-to-many relationship to one more join table. My database schema is relatively simple. I have ''users'' and ''movies''. The join table ''movies_users'' represents a particular user''s collection of movies (or an owner list of a particular movie). So, my model User ''has_and_belongs_to_many :movies'' and my model Movie ''has_and_belongs_to_many :users''. The next step, is for a user to lend a movie from his own collection to another user. I gave my earlier join table a unique id so that I could reference it in a new join table which I called (using what I assume is rails naming convention): movies_users_users. I added some other fields to this join table to represent checkout date, checkin date, etc. From my understanding so far, it doesn''t seem AR''s syntax that I used for the earlier join can handle a join with additional properties attached to it. So, although I can perfectly describe in my database schema what it is I want to model, I am completely stumped on how best to map this into the AR DSL. Coming from a background where we didn''t have these fancy models, I''m quick to start writing a new model to encapsulate all of my SQL queries... But I''m enjoying the Rails way so much, I''d like to figure out the best way to do it with AR. Thanks in advance... Scott Hughes http://blog.globalreset.org/
Hi Scott, I don''t know that there is an easy way to do a many_to_many_to_many in RoR. There is a thread from Jul 8th that you may find useful, titled "Automating relationships". Here is the jist of it in case you can''t find it. Someone had a question about simplifying their User and Feed many to many relationship. Doug Alcorn recommended the following: -------------------------------------- Sometimes it''s much simpler to make these intermediate classes that are one to many. Rather than: Users <-> Feeds you get: Users <- UserFeeds -> Feeds While this second set of relationships seems more complicated, it''s actually easier to keep up with. This is particularly true when you have attributes on the join object. class UserFeeds < ActiveRecord::Base belongs_to :feed belongs_to :user end class User < ActiveRecord::Base has_many :feeds end Then just do @user.feeds to get the list of feeds. ------------------------------------- So, especially since you have another relationship off of your join table, it may be easier to do the above. Also, wouldn''t the association off of your join table be a one to one, a UserMovie belongs to a Borrower? Good luck, Tom On 9/7/05, Scott Hughes (list) <shughes_list-7gqf30rib8jFPXo3spvXtA@public.gmane.org> wrote:> > Hi all. New to the list. Hope to be able to contribute something to > the community soon, for now I have a begginer''s question. > > I found the examples on the Rails wiki for many-to-many model > relationships very useful. However, I can''t get my head around "the > rails way" to do the next step: link that resulting many-to-many > relationship to one more join table. My database schema is relatively > simple. I have ''users'' and ''movies''. The join table ''movies_users'' > represents a particular user''s collection of movies (or an owner list of > a particular movie). So, my model User ''has_and_belongs_to_many > :movies'' and my model Movie ''has_and_belongs_to_many :users''. > > The next step, is for a user to lend a movie from his own collection to > another user. I gave my earlier join table a unique id so that I could > reference it in a new join table which I called (using what I assume is > rails naming convention): movies_users_users. I added some other fields > to this join table to represent checkout date, checkin date, etc. From > my understanding so far, it doesn''t seem AR''s syntax that I used for the > earlier join can handle a join with additional properties attached to it. > > So, although I can perfectly describe in my database schema what it is I > want to model, I am completely stumped on how best to map this into the > AR DSL. Coming from a background where we didn''t have these fancy > models, I''m quick to start writing a new model to encapsulate all of my > SQL queries... But I''m enjoying the Rails way so much, I''d like to > figure out the best way to do it with AR. > > Thanks in advance... > > Scott Hughes > http://blog.globalreset.org/ > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Scott Hughes (list)
2005-Sep-07 18:25 UTC
Re: ActiveRecord question - (many-to-many)-to-many?
Tom Davies wrote:>Doug Alcorn recommended the following: > >-------------------------------------- >Sometimes it''s much simpler to make these intermediate classes that >are one to many. Rather than: > >Users <-> Feeds > >you get: > >Users <- UserFeeds -> Feeds > >While this second set of relationships seems more complicated, it''s >actually easier to keep up with. This is particularly true when you >have attributes on the join object. > >class UserFeeds < ActiveRecord::Base >belongs_to :feed >belongs_to :user >end > >class User < ActiveRecord::Base >has_many :feeds >end > >Then just do @user.feeds to get the list of feeds. >------------------------------------- > >So, especially since you have another relationship off of your join table, >it may be easier to do the above. > > >That''s brilliant! I didn''t realize that I could create the ''join'' model like that and still get the @user.movies functionality that I wanted. That does help... Still not sure how I would access the additional properites of the ''join'' table, but I''ll look for the the referenced thread in the archives to see if the answer is there..>Also, wouldn''t the association off of your join table be a one to one, a >UserMovie belongs to a Borrower? > > >But a borrower could be borrowing lots of diferent movies. Borrower has many UserMovies... In fact, the main purpose of the app is to help me keep tabs on my deadbeat friends who borrow multiple movies at a time, and fail to return them in a timely manner. :) Thanks for your help, Scott Hughes http://blog.globalreset.org/>Good luck, > >Tom > >On 9/7/05, Scott Hughes (list) <shughes_list-7gqf30rib8jFPXo3spvXtA@public.gmane.org> wrote: > > >>Hi all. New to the list. Hope to be able to contribute something to >>the community soon, for now I have a begginer''s question. >> >>I found the examples on the Rails wiki for many-to-many model >>relationships very useful. However, I can''t get my head around "the >>rails way" to do the next step: link that resulting many-to-many >>relationship to one more join table. My database schema is relatively >>simple. I have ''users'' and ''movies''. The join table ''movies_users'' >>represents a particular user''s collection of movies (or an owner list of >>a particular movie). So, my model User ''has_and_belongs_to_many >>:movies'' and my model Movie ''has_and_belongs_to_many :users''. >> >>The next step, is for a user to lend a movie from his own collection to >>another user. I gave my earlier join table a unique id so that I could >>reference it in a new join table which I called (using what I assume is >>rails naming convention): movies_users_users. I added some other fields >>to this join table to represent checkout date, checkin date, etc. From >>my understanding so far, it doesn''t seem AR''s syntax that I used for the >>earlier join can handle a join with additional properties attached to it. >> >>So, although I can perfectly describe in my database schema what it is I >>want to model, I am completely stumped on how best to map this into the >>AR DSL. Coming from a background where we didn''t have these fancy >>models, I''m quick to start writing a new model to encapsulate all of my >>SQL queries... But I''m enjoying the Rails way so much, I''d like to >>figure out the best way to do it with AR. >> >>Thanks in advance... >> >>Scott Hughes >>http://blog.globalreset.org/ >>_______________________________________________ >>Rails mailing list >>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>http://lists.rubyonrails.org/mailman/listinfo/rails >> >> >> > > > >------------------------------------------------------------------------ > >_______________________________________________ >Rails mailing list >Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >http://lists.rubyonrails.org/mailman/listinfo/rails > >
I''m not sure why you used many-to-many in the first place.
Why not just have four tables:
Owners
as now...plus
has_many :movies...
Movies
as now...plus
has_many :copies...
Copies
belongs_to :owner...
belongs_to :movie...
plus format, condition, etc.
has_many :lendings...
Lendings
belongs_to :copy
belongs_to :owner
plus date borrowed, due date, returned flag, condition etc.
Has and belongs to many is more for history/context-free relationships;
here, I''d think you''d want the relationships to be first
class, and
saved.
Hi Scott, You may not find anything more useful in that thread... so here is a bit more: I think you would want something like: Users has_many :movies Movies has_many :users UserMovies belongs_to :user belongs_to :movie belongs_to :borrower Borrower has_many :usermovies # not sure on the symbol name here Alternatively, you could make a User also be a borrower and put the has_many :usermovies in the Users model. Good Luck. Tom On 9/7/05, Scott Hughes (list) <shughes_list-7gqf30rib8jFPXo3spvXtA@public.gmane.org> wrote:> > Tom Davies wrote: > > >Doug Alcorn recommended the following: > > > >-------------------------------------- > >Sometimes it''s much simpler to make these intermediate classes that > >are one to many. Rather than: > > > >Users <-> Feeds > > > >you get: > > > >Users <- UserFeeds -> Feeds > > > >While this second set of relationships seems more complicated, it''s > >actually easier to keep up with. This is particularly true when you > >have attributes on the join object. > > > >class UserFeeds < ActiveRecord::Base > >belongs_to :feed > >belongs_to :user > >end > > > >class User < ActiveRecord::Base > >has_many :feeds > >end > > > >Then just do @user.feeds to get the list of feeds. > >------------------------------------- > > > >So, especially since you have another relationship off of your join > table, > >it may be easier to do the above. > > > > > > > That''s brilliant! I didn''t realize that I could create the ''join'' model > like that and still get the @user.movies functionality that I wanted. > That does help... Still not sure how I would access the additional > properites of the ''join'' table, but I''ll look for the the referenced > thread in the archives to see if the answer is there.. > > >Also, wouldn''t the association off of your join table be a one to one, a > >UserMovie belongs to a Borrower? > > > > > > > But a borrower could be borrowing lots of diferent movies. Borrower has > many UserMovies... In fact, the main purpose of the app is to help me > keep tabs on my deadbeat friends who borrow multiple movies at a time, > and fail to return them in a timely manner. :) > > Thanks for your help, > > Scott Hughes > http://blog.globalreset.org/ > > > >Good luck, > > > >Tom > > > >On 9/7/05, Scott Hughes (list) <shughes_list-7gqf30rib8jFPXo3spvXtA@public.gmane.org> wrote: > > > > > >>Hi all. New to the list. Hope to be able to contribute something to > >>the community soon, for now I have a begginer''s question. > >> > >>I found the examples on the Rails wiki for many-to-many model > >>relationships very useful. However, I can''t get my head around "the > >>rails way" to do the next step: link that resulting many-to-many > >>relationship to one more join table. My database schema is relatively > >>simple. I have ''users'' and ''movies''. The join table ''movies_users'' > >>represents a particular user''s collection of movies (or an owner list of > >>a particular movie). So, my model User ''has_and_belongs_to_many > >>:movies'' and my model Movie ''has_and_belongs_to_many :users''. > >> > >>The next step, is for a user to lend a movie from his own collection to > >>another user. I gave my earlier join table a unique id so that I could > >>reference it in a new join table which I called (using what I assume is > >>rails naming convention): movies_users_users. I added some other fields > >>to this join table to represent checkout date, checkin date, etc. From > >>my understanding so far, it doesn''t seem AR''s syntax that I used for the > >>earlier join can handle a join with additional properties attached to > it. > >> > >>So, although I can perfectly describe in my database schema what it is I > >>want to model, I am completely stumped on how best to map this into the > >>AR DSL. Coming from a background where we didn''t have these fancy > >>models, I''m quick to start writing a new model to encapsulate all of my > >>SQL queries... But I''m enjoying the Rails way so much, I''d like to > >>figure out the best way to do it with AR. > >> > >>Thanks in advance... > >> > >>Scott Hughes > >>http://blog.globalreset.org/ > >>_______________________________________________ > >>Rails mailing list > >>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > >>http://lists.rubyonrails.org/mailman/listinfo/rails > >> > >> > >> > > > > > > > >------------------------------------------------------------------------ > > > >_______________________________________________ > >Rails mailing list > >Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > >http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On 08/09/2005, at 3:16 AM, Tom Davies wrote:> class UserFeeds < ActiveRecord::Base > belongs_to :feed > belongs_to :user > end > > class User < ActiveRecord::Base > has_many :feeds > endI assume you mean "has_many :user_feeds"? The way you have it implies a feed_id on the Users table.> Then just do @user.feeds to get the list of feeds.And this should be "@user.user_feeds"? This area of rails has always puzzled me. If I break my many-to-many relationship out into a concrete table like this, how do I query through that relationship without writing my own SQL (or having Rails generate painfully inefficient SQL)? Pete Yandell
Hi Pete, That was just a cut and paste of the original post, but I believe you are correct. I am not sure I follow what you mean about breaking up your many to many. In either case, you would just use a simple join table. The idea to this approach is really to use the relationship (join table) as the starting point. Once you have the relationship table, you can query for either users or feeds quite easily using the find methods. This becomes more useful if you have additional properties linked off the join table as you can access them directly. If there are no attributes off of the join table, this may not be the preferred approach. I think it is possible to do this will efficient sql as well... but you can always look at the generated sql in the logs to be sure it is up to your standards and make modifications accordingly. The associations rdoc page also has some good tips for performance. Tom On 9/7/05, Pete Yandell <pete-AylieETRJAdBDgjK7y7TUQ@public.gmane.org> wrote:> > > On 08/09/2005, at 3:16 AM, Tom Davies wrote: > > > class UserFeeds < ActiveRecord::Base > > belongs_to :feed > > belongs_to :user > > end > > > > class User < ActiveRecord::Base > > has_many :feeds > > end > > I assume you mean "has_many :user_feeds"? The way you have it implies > a feed_id on the Users table. > > > Then just do @user.feeds to get the list of feeds. > > And this should be "@user.user_feeds"? > > > This area of rails has always puzzled me. If I break my many-to-many > relationship out into a concrete table like this, how do I query > through that relationship without writing my own SQL (or having Rails > generate painfully inefficient SQL)? > > > Pete Yandell > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Scott Hughes (List)
2005-Sep-08 13:46 UTC
Re: ActiveRecord question - (many-to-many)-to-many?
On Sep 7, 2005, at 7:32 PM, Pete Yandell wrote:> > On 08/09/2005, at 3:16 AM, Tom Davies wrote: > > >> class UserFeeds < ActiveRecord::Base >> belongs_to :feed >> belongs_to :user >> end >> >> class User < ActiveRecord::Base >> has_many :feeds >> end >> > > I assume you mean "has_many :user_feeds"? The way you have it > implies a feed_id on the Users table. > > >> Then just do @user.feeds to get the list of feeds. >> > > And this should be "@user.user_feeds"? > > > This area of rails has always puzzled me. If I break my many-to- > many relationship out into a concrete table like this, how do I > query through that relationship without writing my own SQL (or > having Rails generate painfully inefficient SQL)? > >Pete, I hit this same roadblock last night when I promoted my join table up to a regular model table with additional properties. I quickly hit a very large BigO(N^2) problem, as I tried to iterate over my join table''s properties and say something specific about a given movie. So, ended up trying to support both the has_many association (with my new joining model) and the has_and_belongs_to_many association so I could bypass that model for efficiency. class Movie < ActiveRecord::Base has_and_belongs_to_many :users, :join_table => "copies", :finder_sql => ''SELECT DISTINCT u.*, c.id AS copy_id '' + ''FROM user u, copies c '' + ''WHERE u.id = c.user_id AND c.movie_id = #{id} '' + ''ORDER BY u.username'' has_many :copies .... class User < ActiveRecord::Base has_and_belongs_to_many :movies, :join_table => "copies", :finder_sql => ''SELECT DISTINCT m.*, c.id AS copy_id '' + ''FROM movies m, copies c '' + ''WHERE m.id = c.movie_id AND c.user_id = #{id} '' + ''ORDER BY m.title'' has_many :copies .... class Copy < ActiveRecord::Base belongs_to :user belongs_to :movie def self.get_movies(user_id) find(:all, :include => :movie :conditions => ["user_id = ?", user_id]) end def self.get_owners(movie_id) find(:all, :include => :user :conditions => ["movie_id = ?", movie_id]) end def self.get_copy(movie_id, user_id) find(:first, :conditions => ["movie_id = ? and user_id = ?", movie_id, user_id]) end end I did end up writing my own sql though, so I could use the idiom: user.movies.include? movie