Hi, How would I implement a system where on the bridging table used for a "has and belongs to many" relationship between two tables there are extra attributes (like a text field). I am looking for a way in my view that I can easily access these extra attributes. I have looked at the tutorial at http://wiki.rubyonrails.com/rails/pages/CheckboxHABTM This was great. I just need to go one step further and have a text box as well for each check box, and have the contents of the text box saved in the bridging table. Thanks for any suggestions, Richard -- Posted via http://www.ruby-forum.com/.
I''m struggling with the same issue as well. Although it''s been brought up several times on the mailing list, I haven''t yet seen an elegant solution that fits all cases. I''m using Postgres as the DB - one approach I''m considering is to use updateable views, or rather simulate updateable views using a combination of a CREATE VIEW and multiple CREATE RULEs. Then I could just create a model to link to the view, and (hopefully) drive the model as per normal to insert/delete/update rows in the view, which would then (via Postgres rules) make the appropriate changes to the relevant underlying tables. For a reference on this that''s specific to Postgres, look at: - http://www.postgresql.org/docs/8.0/interactive/sql-createview.html (info on CREATE VIEW) - http://www.postgresql.org/docs/8.0/interactive/sql-createrule.html (info on CREATE RULE) If you read through both these links, you''ll find info on how to build updateable views. I haven''t tried this yet (I''m pondering the "elegance" of the solution, as I''m fairly confident I can get it to work OK), but if I don''t hear of a better solution I''ll probably go with this. Good luck Regards Dave M. On 11/29/05, Richard <richard.mcgrath-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi, > > How would I implement a system where on the bridging table used for a > "has and belongs to many" relationship between two tables there are > extra attributes (like a text field). > > I am looking for a way in my view that I can easily access these extra > attributes. > > I have looked at the tutorial at > http://wiki.rubyonrails.com/rails/pages/CheckboxHABTM > > This was great. I just need to go one step further and have a text box > as well for each check box, and have the contents of the text box saved > in the bridging table. > > Thanks for any suggestions, > Richard > > > > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
is this what you are trying to do? <% @foo.bars.each do |bar| %> <%= bar.extra_field_from_join_table %> <% end %> On 11/29/05, Richard <richard.mcgrath-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Hi, > > How would I implement a system where on the bridging table used for a > "has and belongs to many" relationship between two tables there are > extra attributes (like a text field). > > I am looking for a way in my view that I can easily access these extra > attributes. > > I have looked at the tutorial at > http://wiki.rubyonrails.com/rails/pages/CheckboxHABTM > > This was great. I just need to go one step further and have a text box > as well for each check box, and have the contents of the text box saved > in the bridging table. > > Thanks for any suggestions, > Richard > > > > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > 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
Doesn''t work for me - I get an error along the lines of ''extra_field_from_join_table'' is a missing method... If it works for you, could you please share how you''ve got your models defined? Dave M. On 12/1/05, Chris Hall <christopher.k.hall-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> is this what you are trying to do? > > <% @foo.bars.each do |bar| %> > <%= bar.extra_field_from_join_table %> > <% end %> >
ok, using the authors/books example
class Author < ActiveRecord::Base
has_and_belongs_to_many :books
end
class Book < ActiveRecord::Base
has_and_belongs_to_many :authors
end
join table is authors_books with an extra field called "read_at" which
is a
datetime column
book = Book.create(:title => "The Life of Foobars")
bob = Author.new(:name => "bob")
bill = Author.new(:name => "bill)
book.authors.push_with_attributes(bob, :read_at => Time.now)
book.authors.push_with_attributes(bill, :read_at => Time.now)
so now we''ve created the associations between books and authors
if you do:
book = Book.find(1)
book.authors.each { |a| puts a.read_at }
you get what i described in my previous reply...but if you do:
book = Book.find(1, :include => :authors)
book.authors.each { |a| puts a.read_at }
you get NoMethodError as read_at was not included in the author objects
the generated SQL from the first example is:
SELECT * FROM books WHERE (books.id = 1) LIMIT 1
SELECT * FROM authors LEFT JOIN authors_books ON authors.id
authors_books.author_id WHERE (authors_books.book_id = 1 )
and the second example is:
SELECT authors.id AS t1_r0, authors.name AS t1_r1, books.id AS t0_r0,
books.title AS t0_r1 FROM books LEFT OUTER JOIN authors_books ON
authors_books.book_id = books.id LEFT OUTER JOIN authors ON
authors_books.author_id = authors.id WHERE (books.id = 1)
so i guess if you want to access the extra fields, you''ll have to go
with
the first example.
I''ll ask on the list if this is the expected behavior.
On 11/30/05, David Mitchell
<monch1962-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:>
> Doesn''t work for me - I get an error along the lines of
> ''extra_field_from_join_table'' is a missing method...
>
> If it works for you, could you please share how you''ve got your
models
> defined?
Dave M.>
> On 12/1/05, Chris Hall
<christopher.k.hall-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > is this what you are trying to do?
> >
> > <% @foo.bars.each do |bar| %>
> > <%= bar.extra_field_from_join_table %>
> > <% end %>
> >
> _______________________________________________
> 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
just found in documentation for eager loading: It''s currently not possible to use eager loading on multiple associations from the same table. Eager loading will also not pull additional attributes on join tables, so "rich associations" with has_and_belongs_to_many<http://api.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html#M000467>is not a good fit for eager loading. On 11/30/05, Chris Hall <christopher.k.hall-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > ok, using the authors/books example > > class Author < ActiveRecord::Base > has_and_belongs_to_many :books > end > > class Book < ActiveRecord::Base > has_and_belongs_to_many :authors > end > > join table is authors_books with an extra field called "read_at" which is > a datetime column > > book = Book.create(:title => "The Life of Foobars") > bob = Author.new(:name => "bob") > bill = Author.new(:name => "bill) > > book.authors.push_with_attributes(bob, :read_at => Time.now) > book.authors.push_with_attributes(bill, :read_at => Time.now) > > so now we''ve created the associations between books and authors > > if you do: > > book = Book.find(1) > book.authors.each { |a| puts a.read_at } > > you get what i described in my previous reply...but if you do: > > book = Book.find(1, :include => :authors) > book.authors.each { |a| puts a.read_at } > > you get NoMethodError as read_at was not included in the author objects > > the generated SQL from the first example is: > > SELECT * FROM books WHERE (books.id = 1) LIMIT 1 > SELECT * FROM authors LEFT JOIN authors_books ON authors.id > authors_books.author_id WHERE (authors_books.book_id = 1 ) > > and the second example is: > > SELECT authors.id AS t1_r0, authors.name AS t1_r1, books.id AS t0_r0, > books.title AS t0_r1 FROM books LEFT OUTER JOIN authors_books ON > authors_books.book_id = books.id LEFT OUTER JOIN authors ON > authors_books.author_id = authors.id WHERE (books.id = 1) > > so i guess if you want to access the extra fields, you''ll have to go with > the first example. > > I''ll ask on the list if this is the expected behavior. > > On 11/30/05, David Mitchell <monch1962-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > Doesn''t work for me - I get an error along the lines of > > ''extra_field_from_join_table'' is a missing method... > > > > If it works for you, could you please share how you''ve got your models > > defined? > > Dave M. > > > > On 12/1/05, Chris Hall <christopher.k.hall-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > is this what you are trying to do? > > > > > > <% @foo.bars.each do |bar| %> > > > <%= bar.extra_field_from_join_table %> > > > <% end %> > > > > > _______________________________________________ > > 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
Thanks Chris, This seems to do what I was needing. Now I''ll try to turn it into a wiki document so others can work out how to do the same thing ... Regards Dave M. On 12/1/05, Chris Hall <christopher.k.hall-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> just found in documentation for eager loading: > > It''s currently not possible to use eager loading on multiple associations > from the same table. Eager loading will also not pull additional attributes > on join tables, so "rich associations" with has_and_belongs_to_many is not a > good fit for eager loading. > > > On 11/30/05, Chris Hall <christopher.k.hall-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > ok, using the authors/books example > > > > class Author < ActiveRecord::Base > > has_and_belongs_to_many :books > > end > > > > class Book < ActiveRecord::Base > > has_and_belongs_to_many :authors > > end > > > > join table is authors_books with an extra field called "read_at" which is > a datetime column > > > > book = Book.create(:title => "The Life of Foobars") > > bob = Author.new(:name => "bob") > > bill = Author.new(:name => "bill) > > > > book.authors.push_with_attributes(bob, :read_at => > Time.now) > > book.authors.push_with_attributes(bill, :read_at => > Time.now) > > > > so now we''ve created the associations between books and authors > > > > if you do: > > > > book = Book.find(1) > > book.authors.each { |a| puts a.read_at } > > > > you get what i described in my previous reply...but if you do: > > > > book = Book.find(1, :include => :authors) > > book.authors.each { |a| puts a.read_at } > > > > you get NoMethodError as read_at was not included in the author objects > > > > the generated SQL from the first example is: > > > > SELECT * FROM books WHERE (books.id = 1) LIMIT 1 > > SELECT * FROM authors LEFT JOIN authors_books ON authors.id > authors_books.author_id WHERE (authors_books.book_id = 1 ) > > > > and the second example is: > > > > SELECT authors.id AS t1_r0, authors.name AS t1_r1, books.id AS t0_r0, > books.title AS t0_r1 FROM books LEFT OUTER JOIN authors_books ON > authors_books.book_id = books.id LEFT OUTER JOIN authors ON > authors_books.author_id = authors.id WHERE ( books.id = 1) > > > > so i guess if you want to access the extra fields, you''ll have to go with > the first example. > > > > I''ll ask on the list if this is the expected behavior. > > > > > > > > On 11/30/05, David Mitchell <monch1962-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org > wrote: > > > Doesn''t work for me - I get an error along the lines of > > > ''extra_field_from_join_table'' is a missing method... > > > > > > If it works for you, could you please share how you''ve got your models > defined? > > > > > Dave M. > > > > > > On 12/1/05, Chris Hall <christopher.k.hall-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > is this what you are trying to do? > > > > > > > > <% @foo.bars.each do |bar| %> > > > > <%= bar.extra_field_from_join_table %> > > > > <% end %> > > > > > > > _______________________________________________ > > > 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 > > >
monch1962 wrote:> Thanks Chris, > > This seems to do what I was needing. > > Now I''ll try to turn it into a wiki document so others can work out > how to do the same thing ... > > Regards > > Dave M.Just wanted to quickly let you know that there are a few shortcomings of this method. In many cases, I''ve found it better to just create another model for the join table when there are additional fields. (in fact, in all cases in my app) One is the eager loading that you''ve already mentioned. Another is that when you do an ''include'' on these relationships, you lose the additional fields. (at least in my experience -- no one posted that this is a bug or that it isn''t) See: http://www.ruby-forum.com/topic/2763#1636 Cheers, Jake -- Posted via http://www.ruby-forum.com/.
I''ve also thought about eager loading the join table in habtm but without a seperate ActiveRecord model for the join table where would the additional fields be eager loaded too? I think habtm is just not intended to do this. - Peter _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On 11/28/05, Richard <richard.mcgrath-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi, > > How would I implement a system where on the bridging table used for a > "has and belongs to many" relationship between two tables there are > extra attributes (like a text field). > > I am looking for a way in my view that I can easily access these extra > attributes. > > I have looked at the tutorial at > http://wiki.rubyonrails.com/rails/pages/CheckboxHABTM > > This was great. I just need to go one step further and have a text box > as well for each check box, and have the contents of the text box saved > in the bridging table.Can''t you do something like class User < AR has_many :registrations end class Registration < AR belongs_to :user belongs_to :event end class Event < AR has_many :registrations end Where a user can register for one or more events. And then your Registration model can have as much stuff as it needs (i.e. time registered and other registration-specific data).
On 12/2/05, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Can''t you do something like > > class User < AR > has_many :registrations > end > > class Registration < AR > belongs_to :user > belongs_to :event > end > > class Event < AR > has_many :registrations > end > > Where a user can register for one or more events. And then your > Registration model can have as much stuff as it needs (i.e. time > registered and other registration-specific data).Imagine that Users contains 200 people working in an office building, and Events is a list of office functions (e.g. Xmas party), for argument''s sake. On the one Web page, for a specific Event, I want to list all the people that have registered for that event, with the date/time they registered (from the Registrations table), *as well as* all the people that haven''t registered, with a link that can be clicked to register them. (Ignore the fact that I''d probably want some cool Ajax-y stuff to auto-update the page when someone registers, and any requirement for security). This strikes me as a pretty typical, not-exceptionally-complex requirement for a (hypothetical but somewhat useful) application. I would expect Rails, with its focus on simplicity, speed & agility, to eat this sort of requirement for breakfast... Unfortunately, if I don''t use the HABTM-based approach that Chris outlined earlier in this thread with his Authors/Books example to retrieve data from the Registrations table, I can''t think of another way of doing this without resorting to ridiculous amounts of code that will probably be nigh on impossible to support 6 months down the track. Doing this with 4 has_many and belongs_to relationships (i.e. Users<->Registrations and Events<->Registrations) in place of 1 HABTM relationship introduces another level of complexity to the code that just makes it more difficult to support. With the current version of Rails, Chris'' approach works just fine in this scenario, and the code required in the view is fairly straightforward to write and maintain. Peter''s right in that it just seems like HABTM isn''t "meant to" support this level of complexity; I''m concerned that a later Rails update could break it. If anyone thinks I''m missing something, you''re almost certainly right - please speak up. Regards Dave M.
On 11/30/05, Chris Hall <christopher.k.hall-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> It''s currently not possible to use eager loading on multiple associations > from the same table. Eager loading will also not pull additional attributes > on join tables, so "rich associations" with has_and_belongs_to_many is not a > good fit for eager loading.There''s a patch that fixes both issues at http://dev.rubyonrails.org/ticket/1562. This can be turned into a plugin using the patch to plugin script I posted earlier.
Can someone please give us an experience report using this patch? thanks On 12/2/05, Jeremy Evans <jeremyevans0-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 11/30/05, Chris Hall <christopher.k.hall-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > It''s currently not possible to use eager loading on multiple associations > > from the same table. Eager loading will also not pull additional attributes > > on join tables, so "rich associations" with has_and_belongs_to_many is not a > > good fit for eager loading. > > There''s a patch that fixes both issues at > http://dev.rubyonrails.org/ticket/1562. This can be turned into a > plugin using the patch to plugin script I posted earlier. > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
> With the current version of Rails, Chris'' approach works just fine in > this scenario, and the code required in the view is fairly > straightforward to write and maintain. Peter''s right in that it just > seems like HABTM isn''t "meant to" support this level of complexity; > I''m concerned that a later Rails update could break it.HABTM is indeed not well suited for complexity. It''s pushing back because it wants you to discover the implicit model object that''s missing from the equation. In the author/book example that model is Authorship. So you would have: class Author has_many :authorships end class Book has_many :authorships end class Authorship belongs_to :author belongs_to :book end Now the domain is explicit, but we''re still in a bit of trouble. Since this pretty domain will force us to write SQL by hand to get performant access. So you''d probably have: class Author has_many :authorships def books find_by_sql( "SELECT books.* " + "FROM books, authors, authorships " + "WHERE authorships.book_id = books.id AND + " "authorships.author_id = #{id}" ) end end And you would create new relationships by doing b = Book.create :title => "Agile Web Development with Rails" david = Author.create :name => "David Heinemeier Hansson" dave = Author.create :name => "Dave Thomas" Authorship.create([ { :book => b, :author => david }, { :book => b, :author => dave } ]) Now this is actually a lot less painful than one could imagine. But its still not painless enough. So we''re currently working on allowing: class Author has_many :authorships has_many :books, :through => :authorships end This would expose both the join model (authorship) and allow convenient access to the model on the other side of that join model. One could even imagine: class Author has_many :authorships has_many :books, :through => :authorships has_many :agents, :through => :authorships end class Authorship belongs_to :agent belongs_to :author belongs_to :book end So if I was having pushback from HABTM today, I would try to discover which implicit domain model I hadn''t revealed yet. Then I''d spend the few minutes making the manual accessors for that join model. And I would then look forward to the day where I could remove my manual access as :through materializes. -- David Heinemeier Hansson http://www.loudthinking.com -- Broadcasting Brain http://www.basecamphq.com -- Online project management http://www.backpackit.com -- Personal information manager http://www.rubyonrails.com -- Web-application framework
> In the author/book example that model is > Authorship. So you would have:To follow up on that example. Would you be able to do?: david.books << awrd Answer: No. Because if you only want to have the join model represent the join, then you don''t need an explicit join model and HABTM is fine. You would instead do something like: david.authorships.create :book => awrd, :published_on => Date.new(2005, 9, 1) That would be a sensible use of join models since there are additional attributes. And you would now be able to do both: david.books.first # => awrd david.authorships.first.book # => awrd -- David Heinemeier Hansson http://www.loudthinking.com -- Broadcasting Brain http://www.basecamphq.com -- Online project management http://www.backpackit.com -- Personal information manager http://www.rubyonrails.com -- Web-application framework
On 12/2/05, David Heinemeier Hansson <david.heinemeier-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> So if I was having pushback from HABTM today, I would try to discover > which implicit domain model I hadn''t revealed yet. Then I''d spend the > few minutes making the manual accessors for that join model. And I > would then look forward to the day where I could remove my manual > access as :through materializes.Is that a 1.0 feature or post 1.0 feature? I have something similar, where I have Users, Registrations, Roles, and TrainingEvents, where Users has_many Registrations and TrainingEvents has_many Registrations and Roles has_many Registrations, and Registrations belongs_to each of the others (Users Register for a Training Event where they can be trained for a specific Role). And each Registration has an associated ''program number'' (just a user-submitted string). And, right now, I have something like class TrainingEvent < AR def register_a_user user, program_number, role # Logic here that makes sure that a user can register for this event and this role registration = Registration.new registration.program_number = program_number registration.role = role registration.user = user registration.training_event = self registration.save end end Seems to work ok. Is that the best way of doing it? Joe
> Is that a 1.0 feature or post 1.0 feature?Post 1.0. I''m actually working on it right now, but 1.0 has been in feature freeze for a good while now. So this would be a 1.1 feature.> Seems to work ok. Is that the best way of doing it?That seems reasonable. -- David Heinemeier Hansson http://www.loudthinking.com -- Broadcasting Brain http://www.basecamphq.com -- Online project management http://www.backpackit.com -- Personal information manager http://www.rubyonrails.com -- Web-application framework
Hi David, Will :through be able to associate more than just one join table away in the model structure? Something like the last two associtaions in A in this example. class A < ActiveRecord::base has_many :as_bs has_many :bs, :through => :as_b has_many :bs_cs, :through => [:as_b, :bs] has_many :cs, :through => [:as_b, :b, :bs_c] end class AsB < ActiveRecord::base belongs_to :a belongs_to :b end class B < ActiveRecord::base has_many :as_bs has_many :bs_cs end class BsC < ActiveRecord::base belongs_to :b belongs_to :c end class C < ActiveRecord::base has_many: bs_cs end Thanks, Peter _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails