Sequel model over two joined tables

Question

I have a legacy PostgreSQL database, which has a single model split into two tables, with 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'm unable to change database structure.

I want to use this as a single Sequel model. Retreiving data from 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, saving the model fails:

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

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

Is there a way I could make Sequel seamlessly issue two UPDATE statements? (Same question holds for INSERTs, too).

score 4 · Accepted Answer · answered Jun 14 '12 at 16:20

You can have a Sequel model that uses a joined dataset, but there's no easy way to save such a model.

Personally, I would use a many_to_one relationship, 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 haven't tested that, but something like it should work. If you have problems with it, you should probably post on the sequel-talk Google Group.

Sequel model over two joined tables

1 Answers1