Polymorphic Association On UUID and Integer Fields

Question

Given tables with integer and uuid primary keys what is the best way to integrate a polymorphic join (has_many)? For example:

class Interest < ActiveRecord::Base
  # id is an integer
  has_many :likes, as: :likeable
end

class Post < ActiveRecord::Base
  # id is a UUID
  has_many :likes, as: :likeable
end

class User < ActiveRecord::Base
  has_many :likes
  has_many :posts, through: :likes, source: :likeable, source_type: "Post"
  has_many :interests, through: :likes, source: :likeable, source_type: "Interest"
end

class Like < ActiveRecord::Base
  # likeable_id and likeable_type are strings
  belongs_to :likeable, polymorphic: true
  belongs_to :user
end

Many queries work:

interest.likes
post.likes
user.likes

However:

user.interests

Gives:

PG::UndefinedFunction: ERROR: operator does not exist: integer = character varying LINE 1: ...interests" INNER JOIN "likes" ON "interests"."id" = "likes".... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. : SELECT "interests".* FROM "interests" INNER JOIN "likes" ON "interests"."id" = "likes"."likeable_id" WHERE "likes"."user_id" = $1 AND "likes"."likeable_type" = $2

What's the best way to include ensure the proper casting happens?

Answer 1

This is an old question, but here's my recommendation.

This is more of an architecture problem. Don't combine UUID ids and integer ids, it get's messy real fast. If you can, migrate the integer IDs to UUID or revert the uuids to integer ids.

My experience has been that the best solution is probably to make use of the rather nice Friendly ID gem: https://github.com/norman/friendly_id

In the off case this is broken in the future, it is basically just a slug generation/managemnet tool, the slug would use this kind of route path: posts/this-is-a-potential-slug instead of posts/1, but nothing prevents you from using posts/<UUID here> or posts/<alphanumeric string here>.

Typically if you are using UUIDs it's because you don't want to show the sequential integers. Friendly ID works well to avoid that issue.

Answer 2

There's no means to specify the necessary cast using Rails. Instead, add a generated column with the cast, and declare an extra belongs_to association to use it. For example, with this in a migration:

add_column :interests, :_id_s, 'TEXT GENERATED ALWAYS AS (id::text) STORED'
add_index :interests, :_id_s

and this in your models:

class Like
  belongs_to :_likeable_cast, polymorphic: true, primary_key: :_id_s, foreign_key: :likeable_id, foreign_type: :likeable_type

class User
  has_many :interests, through: :likes, source: :_likeable_cast, source_type: "Interest"

then user.interests joins through the alternative association, i.e. using the generated column with the cast.

I suggest using a column type of text rather than varchar for the likeable_id column, to avoid unnecessary conversions during the join and ensure the index is used.