Rails validation on jsonb array element uniqueness

Question

I have a an Author model and a Book model.

An author can have many books, and a Book can also have many authors. Hence, they are linked through a third model, AuthorBook.

This model have three fields: author_id, book_id, but also: roles.

Roles is a jsonB array containing the role(s) of this author relative to this book. It can be for example "main_author", "corrector", "translator", etc. The author can have 1, many, or all roles.

I want an AuthorBook, for a given Book, to only allow for one Author having a certain role. There can be only one "main_author", "corrector", etc.

I tried to implement a validation such as this one:

class AuthorBook < ApplicationRecord
  validate :uniqueness_of_author_role_for_book

  def uniqueness_of_author_role_for_book
    others_records = AuthorBook.where(book_id:).where.not(user_id:)

    return if others_records.blank?

    other_records.each do |record|
      errors.add(:roles, "#{record.roles.join(',')} already exists on another user") if roles_already_assigned?(record)
    end
  end

  private

  def roles_already_assigned?(record)
    roles.map(&:to_s).intersect?(record.roles)
  end
end

Sadly there is an edge case where this doesn't work.

if I update the author from author1 to author2 for AuthorBook, with the same role; this snippet of code will detect the existence of author1, which has an AuthorBook for this book with that very same role, and the validation will fail.

It should not fail, because after the update, author1 will not have that role anymore, and it should be valid.

There is probably a better way to do this but I can't seem to pinpoint it.

Answer 1

An alternative approach is to have one AuthorBook record for each role instead of the array column. Then you can simply use standard uniqueness validation with a scope on book_id

class AuthorBook
  validates :role, uniqueness: { scope: :book_id }