Continue model over two joined tables

Question

I have an outdated PostgreSQL database, which has one model, divided into two tables, with a one-to-one mapping between them.

CREATE TABLE auth_user ( id SERIAL, username VARCHAR(30), email VARCHAR(75), password VARCHAR(64), first_name VARCHAR(75), last_name VARCHAR(75) ) CREATE TABLE user_profile ( user_id INTEGER REFERENCES auth_User.id, phone VARCHAR(32) ) 

Unfortunately, I cannot change the structure of the database.

I want to use this as one Sequel model. Returning data from the database works as expected:

 class User < Sequel::Model end # Variant 1: using LEFT JOIN #User.set_dataset DB[:auth_user].left_join(:user_profile, :user_id => :id) # Variant 2: using two FROM tables User.set_dataset DB[:auth_user, :user_profile]\ .where(:auth_user__id => :user_profile__user_id) user = User[:username => "root"] # This works. 

However, the model is not saved:

 user.set :first_name => "John" user.save # This fails. 

If I use the first variant of the data set (with left_join ), I get the error " Need multiple FROM tables if updating/deleting a dataset with JOINs ". If I use the second option, it still fails: " PG::Error: ERROR: column "phone" of relation "auth_user" does not exist LINE 1: ..."email" = ' [e-mail] ', "password" = '!', "phone"... "

Is there a way to get Sequel to easily issue two UPDATE statements? (The same question remains for INSERT).

Answer 1

You may have a Sequel model that uses a unified dataset, but there is no easy way to save such a model.

Personally, I would use many_to_one relationships, nested attributes and hooks for what you want:

 class UserProfile < Sequel::Model(:user_profile) end class User < Sequel::Model(:auth_user) many_to_one :user_profile, :key=>:id, :primary_key=>:user_id plugin :nested_attributes nested_attributes :user_profile def phone user_profile.phone end def phone=(v) user_profile.phone = v end def user_profile if s = super s else self.user_profile_attributes = {} super end end def before_destroy user_profile.destroy super end def before_create user_profile super end def after_update super user_profile.save end end 

I have not tested this, but something like this should work. If you have a problem with this, you should probably post the conversation in the Google group.