On Sunday, December 20, 2015 at 9:03:34 PM UTC-8, John Manuel wrote: > > I think I may be misunderstanding how to properly use *one_through_one*, > so I'd like to ask some advice about it. > > *Here is summary of my situation/database:* > * A database originally created and used by another application has 2 > unrelated tables which I want to relate in a new application I'm building. > Here is a simplified migration for the two tables: > create_table :accounts do > primary_key :id > citext :email, null: false, index: true, unique: true > ... > DateTime :updated_at, null: false > DateTime :created_at, null: false > end > > > create_table :people do > primary_key :id > String :last_name, null: false, default: '' > String :first_name, null: false, default: '' > String :middle_name, size: 40 > jsonb :address_fields, default: nil > jsonb :phone_fields, default: nil > TrueClass :publish, null: false, default: true > ... > DateTime :updated_at, null: false > DateTime :created_at, null: false > end > * You can see from the tables that they are not related (no foreign keys > or references to each other). > * I figure the best option is to add a new table other applications will > ignore that I can use in this new application that needs to relate these - > like this: > create_table :accounts_people do > foreign_key :account_id, :accounts, index: true, unique: true, > null: false > foreign_key :person_id, :people, index: true, unique: true, > null: false > primary_key [:account_id, :person_id] > index [:account_id, :person_id], unique: true > end >
Note that as account_id and person_id are part of the primary key, you don't need to use the null: false option for them. You don't need a unique index that is the same as the primary key, as the primary key will create an implicit index. > * With that I should have a clean way to relate accounts and people - it's > just a regular join table, but not many-to-many. Each account can be > associated with 0 or 1 people, and each person can only be related to 0 or > 1 accounts. I would use create_join_table(), but that only makes the > primary_key unique, not each foreign key (which is required to ensure only > 0 or 1 associations). > * I thought the Sequel models for this would be very simple (again, > simplified): > class Account < Sequel::Model > one_through_one :person > ... > end > > class Person < Sequel::Model > one_through_one :account > ... > end > > *Problem:* > Now, for me the problem comes because (as the documentation states: > *one_through_one > associations do not have any modification methods added.*) > With the above models I can query if an account has a person or a person > has an account; but there are no methods to associate a person with an > account (or account with a person). Before carefully reading the > documentation I would have expected one_through_one to setup the same > setters as the one_to_one association (but set the keys in the join table > instead of the right or left table). However one_through_one only sets up > getters. > The main reason Sequel doesn't setup setter methods is that nobody has requested setter methods yet. I'm not opposed to adding them to one_through_one associations. > > *Solutions?:* > I've thought of three potential ways to provide setters on my models so > that I can associate people and accounts, but I want to know if I'm missing > something and off in the weeds.... > > Option 1: (seems easy but wrong) > * Add many_to_many relationships to both models in addition to the > one_through_one. > * I tried this, and it works, but it doesn't seem 'honest' when reading > the code and implies the relationships are many_to_many. > class Account < Sequel::Model > many_to_many :people > one_through_one :person > ... > end > > class Person < Sequel::Model > many_to_many :accounts > one_through_one :account > ... > end > * But now in code I end up with account.add_person(person) and/or > person.add_account(account) both of which imply the standard many-to-many > relationship. > * So, I'd end up using the many_to_many setters to set (the one > account/person) and the one_through_one getters to access the related > account/person. > * I'd think if this was the right solution Jeremy would have documented > that. > This is wrong because: account.add_person(person1) account.add_person(person2) would result in an error instead of setting the person for the account to person2. > Both of the next options require that I create a model for the join table > (AccountsPeople) which I'd rather not do because I don't really 'care' > about that model - I just want the relationships. > Option 2: > * Create the AccountsPeople model with associations to Account and Person. > class AccountsPerson < Sequel::Model > many_to_one :account > many_to_one :person > end > > Now I need to get or create an account and a person and then associate > them using a new AccountsPerson instance like this: > AccountsPerson.create(account: account, person: person) > -or- > ap = AccountsPerson.new > ap.person = get_the_person() > ap.account = get_the_account() > ap.save > This requires my client code to know and understand there is an > AccountsPerson model when I don't think it should (it's just a > relationship). > Wrong for the same reason. > Option 3: > * Seems the most correct way, but also the most code/work > * Extend option 2 by adding my own setters on Account and Person, like so: > class Account < Sequel::Model > one_through_one :person > > def person=(person) > AccountsPerson.create(account: self, person: person) > end > end > > class Person < Sequel::Model > one_through_one :account > > def account=(account) > AccountsPerson.create(account: account, person: self) > end > end > > From a client (application) code point of view the 3rd options seems the > cleanest. The fact that an AccountsPerson model exists is limited to > Person and Account and any application code doesn't need to know about it. > Also wrong for the same reason. I'm just wondering if I've missed something (likely) in Sequel that makes > the use of one_through_one more simple and obvious. I'm certainly not > opposed to implementing option 3 - I implemented all 3 options so I could > clearly understand my problem and write this question. It just seems that > after implementing option 3 why would I need to use one_through_one at all > - I may as well just write the getters also and make the code > more symmetrical. > > If one_though_one doesn't provide any setter capability for the > relationship, then what's the 'right' way to do this and is one_through_one > intended to be a read-only association? If so, it would be helpful to have > that clearly stated in the documentation with an example of how to create > the relationship. More likely, I've missed something and just need to > understand it. > Currently, it is intended to be a read-only association, since it doesn't provide modification methods. This is documented (http://sequel.jeremyevans.net/rdoc/files/doc/association_basics_rdoc.html#label-Methods+Added). The reason the one_through_one association was added to Sequel was because it was a simple modification to the many_to_many support. The setter methods for one_through_one would be new code, and few users need them, so I decided not to implement them when adding the initial support. Considering it's been almost 2 years since one_through_one and you are the first person to request setter methods for one_through_one, that's not looking like a bad decision in hindsight. I don't think supporting the setter methods is that difficult. The Account#person= setter method is basically calling the equivalent code: if current_person != person remove_person(current_person) if current_person add_person(person) if person end I'll certainly consider a patch that implements them, with the usual provisos (include specs, include integration tests, update documentation). I'll add it to my todo list, but I can't promise it any time soon, and certainly I won't have a chance to implement it before the next release. Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
