broken create, save, update, and destroy on Join record when using Postgres UUIDs in Rails

Question

I'm creating uids using

create_table :users, { id: false } do |t|
    t.uuid :uid, default: 'uuid_generate_v4()'
    ... other columns

and setting self.primary_key = :uid in the models.

In general this works fine with ActiveRecord and I write has_many and belongs_to associations fine. However, when crossing a join table (i.e. has_many ... through:, I need to write custom SQL to get records.

I've figured out that I can in general do this by writing custom SQL, i.e. SELECT * FROM main_table JOIN join_table ON main_table.uid = cast(join_table.uid AS uuid) WHERE condition=true)

I've just recently realized that ActiveRecord's create, destroy, save and update dont work on the join model.

I have patched the four methods so they work, but it's too complex a sequence for my taste and probably unoptimal. Here are my patches:

def save(*args)
    # save sometimes works if it is called twice,
    # and sometimes works the first time but says theres an error
    super(*args) unless (super(*args) rescue true)
end

Sometimes, save issues a ROLLBACK the first time with no explanation. Then it works the second time. In other situations (I'm not sure why, possibly when updating), the first time it goes through successfully but if called a second time raises a TypeError. See here for another question about this error which doesn't have any answers for how to save a join when using uid instead of id. Here are my other (working) patches.

def create(*args)
    attrs = args[0]
    raise( ArgumentError, "invalid args to bucket list create" ) unless attrs.is_a?(Hash)
    bucket_list_photo = self.class.new(
        attrs
    )
    bucket_list_photo.save
    bucket_list_photo = BucketListPhoto.find_by(
        bucket_list_uid: bucket_list_photo.bucket_list_uid,
        photo_uid: bucket_list_photo.photo_uid
    )
    return bucket_list_photo
end

def update(*args)
    # similar bug to save
    attrs = args[0]
    raise( ArgumentError, "invalid args to bucket list update" ) unless attrs.is_a?(Hash)
    bucket_list_uid = self.bucket_list_uid
    photo_uid = self.photo_uid
    due_at = self.due_at
    self.destroy
    bucket_list_photo = self.class.new(
        {
            bucket_list_uid: bucket_list_uid,
            photo_uid: photo_uid,
            due_at: due_at
        }.merge(attrs)
    )
    bucket_list_photo.save
    bucket_list_photo = self.class.find_by(
        photo_uid: photo_uid,
        bucket_list_uid: bucket_list_uid
    )
    return bucket_list_photo # phew
end

def destroy
    # patching to fix an error on #destroy, #destroy_all etc.
    # the problem was apparently caused by custom primary keys (uids)
    # see https://stackoverflow.com/a/26029997/2981429
    # however a custom fix is implemented here
    deleted_uids = ActiveRecord::Base.connection.execute(
        "DELETE FROM bucket_list_photos WHERE uid='#{uid}' RETURNING uid"
    ).to_a.map { |record| record['uid'] }
    raise "BucketListPhoto not deleted" unless (
        (deleted_uids.length == 1) && (deleted_uids.first == uid)
    )
    ActiveRecord::Base.connection.query_cache.clear
    # since, the cache isnt updated when using ActiveRecord::Base.connection.execute,
    # reset the cache to ensure accurate values, i.e. counts and associations. 
end

I even ensured that self.primary_key = :uid in all my models.

I also tried replacing uid with id everywhere and verified that all the specs were passing (though I left in the patch). However, it still failed when I removed the patch (i.e. renaming the uid columns to id did not fix it).

EDIT

In response to some comments I've tried the activeuuid gem (where i got stuck on an error) and decided to totally switch over to ids. This is basically for simplicity's sake since I have pressure to launch this app ASAP.

Still, even with this fix I am required to patch save, create, and update. Actually the delete patch no longer works and I had to remove it (relying on the original). I would definitely like to avoid having to make these patches and I am keeping the bounty open for this reason.

Answer 1

There are pros and cons to retaining both id and uuid. For JSON APIs that expose uuid, using Concerns would be a Rails-ish implementation.

app/models/user.rb

class User < ActiveRecord::Base

    include UserConcerns::Uuidable

end

app/models/concerns/user_concerns/uuidable.rb

module UserConcerns::Uuidable
    extend ActiveSupport::Concern

    included do
        before_save :ensure_uuid!
    end


    def ensure_uuid!
        self.uuid = generate_uuid if uuid.blank?
    end

    def generate_uuid
        # your uuid using uuid_generate_v4() here
    end

    def to_param
        uuid
    end
end

Above implementation leaves out the uuid generation but I think above answer has a link to that.

Answer 2

I have provided 1 solution to generate UUID, I knew you switched back to id now. find link for UUID

Yes I agree we can not perform CRUD on joins table why don't you use active records relations to perform CRUD operations.

The important thing to understand is the convention by which Rails implements relationships using ActiveRecord. A book has many characters, and each character belongs to a book, so:

class Book < ActiveRecordBase
  has_many :characters
end

class Character < ActiveRecordBase
  belongs_to :book
end

Rails now assumes that the characters table will have a foreign key called book_id, which relates to the books table. To create a character belonging to a book:

@book = Book.new(:name=>"Book name")
@character = @book.characters.build(:name=>"Character name")

Now when @book is saved (assuming both @book and @character are valid), a row will be created in both the books and the characters tables, with the character row linked through book_id.

To show that a character also belongs to a user, you could add that relationship to the Character model:

class Character < ActiveRecordBase
  belongs_to :book
  belongs_to :user
end

Thus Rails now expects characters to also have foreign key called user_id, which points to a users table (which also needs a User model). To specify the user when creating the character:

@book = Book.new(:name=>"Book name")
@character = @book.characters.build(:name=>"Character name",:user=>current_user)

You can also assign the foreign key by calling the corresponding method on the object:

@character.user = current_user

This all works because it follows the Rails conventions for naming models and tables.