How can I ask ActiveRecord to make it''s "has_many" code to work on some arbitrary relationship between two tables? ie neither field is a primary key The situation is that I am writing a Postfix management application and one of the tables to manage is the "alias" table. My primary key in the "mailbox" table is "id", but the alias table is just a mapping between two text email address fields. It needs to stay like this for performance reasons and also because applications querying the DB are not able to do table joins, finally it''s also because you can setup aliases for things which aren''t in your database So I would like to be able to navigate to my "mailbox" object and then navigate down into the "aliases" collection from that object and find any related aliases and also have all the nice methods that come as a result of using the "has_many" macro Any suggestions? (Or am I misunderstanding exactly what has_many actually does?) Thanks Ed W
Charles Leeds
2005-Dec-13 02:36 UTC
Re: How to use arbitrary relationships in Activerecord
Yeah, you could use a find_sql in the parent class and one in the child class. Here is some example code. Maybe not perfect, but the basic idea. Or you could probably link the two tables together with some definitions in the models, but I''m not real good at that. class Mailbox < ActiveRecord::Base has_many :aliases, :class_name => "Alias", :finder_sql => ''SELECT * FROM aliases a, mailboxes, m WHERE a.email_address = m.email_address'' end mymailbox = Mailbox.find_by_email("railsrocks-Ijf5AnXplyJl57MIdRCFDg@public.gmane.org") mymailbox.aliases.each do |myalias| puts myalias.name end --------------- class View < ActiveRecord::Base has_and_belongs_to_many :sessions has_many :resulting_hosts, :class_name => "Result", :finder_sql => ''SELECT DISTINCT r.host FROM results r '' + ''WHERE r.session_id IN '' + ''(SELECT s.session_id FROM sessions_views s '' + ''WHERE s.view_id = #{id} )'' end Overriding Defaults class Call < ActiveRecord::Base set_table_name "[HeatAdmin].[CallLog]" set_primary_key "CallID" has_many :assignments end Ed W wrote:> How can I ask ActiveRecord to make it''s "has_many" code to work on > some arbitrary relationship between two tables? ie neither field is a > primary key > > The situation is that I am writing a Postfix management application > and one of the tables to manage is the "alias" table. My primary key > in the "mailbox" table is "id", but the alias table is just a mapping > between two text email address fields. It needs to stay like this for > performance reasons and also because applications querying the DB are > not able to do table joins, finally it''s also because you can setup > aliases for things which aren''t in your database > > So I would like to be able to navigate to my "mailbox" object and then > navigate down into the "aliases" collection from that object and find > any related aliases and also have all the nice methods that come as a > result of using the "has_many" macro > > Any suggestions? (Or am I misunderstanding exactly what has_many > actually does?) > > Thanks > > Ed W > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > >
Charles Leeds wrote:> Yeah, you could use a find_sql in the parent class and one in the > child class. Here is some example code. Maybe not perfect, but the > basic idea. Or you could probably link the two tables together with > some definitions in the models, but I''m not real good at that. > > class Mailbox < ActiveRecord::Base > has_many :aliases, :class_name => "Alias", :finder_sql => > ''SELECT * FROM aliases a, mailboxes, m WHERE a.email_address = > m.email_address'' > end > > mymailbox = Mailbox.find_by_email("railsrocks-Ijf5AnXplyJl57MIdRCFDg@public.gmane.org") > mymailbox.aliases.each do |myalias| > puts myalias.name > end >This is excellent (and obvious now it''s written down). Thanks What happens during "saving" though? Is it possible to append new objects to mymailbox.aliases and then save them back to the database correctly? This was one thing I wasn''t sure about when deviating from the standard relationships? Thanks very much for taking the time to respond Ed W
Reasonably Related Threads
- Slow reading of large dovecot-uidlist files
- [LLVMdev] Problem Adding New Pass to Alias Analysis Group
- [LLVMdev] Problem Adding New Pass to Alias Analysis Group
- [LLVMdev] Problem Adding New Pass to Alias Analysis Group
- [LLVMdev] What's the Alias Analysis does clang use ?