Sequel model over two joined tables

left-join, model, orm, ruby, sequel

Solution

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.

Problem

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).

Original source