3

Long story short, I have models relationships defined like this:

Project has_many :groups
Group has_many :items

My goal is to make item names unique inside the project scope. Since :items table does not have project_id foreign key defined, simply writing
add_index :items, :name, unique: { scope: :project_id } will not work. I'm new to rails, am i missing something? How can this problem be solved?

Alex Lomia
  • 6,705
  • 12
  • 53
  • 87
  • You can only scope on the columns in that table, so unless you add project_id to items, this can't be done in the DB. It can however be done via validation. – omarvelous Sep 17 '15 at 20:34
  • What is the best practice of solving such issues? Won't adding `project_id` to the `items` table result in a field duplication? Since the `project_id` can already be accessed through `item.group.project_id`. – Alex Lomia Sep 17 '15 at 20:42
  • The best case would be to do this via validations. `validates :name, :uniqueness => {scope: :project}` – omarvelous Sep 17 '15 at 20:44
  • Unfortunately `Unknown column 'test_cases.project'` error is shown when trying to solve the issue via validations – Alex Lomia Sep 17 '15 at 21:00
  • My apologies, you will need to add `belongs_to :group` and `belongs_to :project, through: :group` to Items – omarvelous Sep 17 '15 at 21:02
  • 1
    Sorry, that doesn't work either - `Unknown key: :through` is given when trying to use `belongs_to :project, through: :group` in `Item` model. It is mentioned in the best answer of this [question](http://stackoverflow.com/questions/4021322/belongs-to-through-associations) that the `belongs_to` association can't have a `through:` option. – Alex Lomia Sep 17 '15 at 21:38
  • @AlexanderLomia instead of `belongs_to :project, through: :group`, try `has_one :project, through: :group`. – steel Sep 17 '15 at 22:13
  • Once you've set up your relationship correctly, a scope should work. http://stackoverflow.com/questions/5129702/ruby-on-rails-activerecord-has-many-through-uniqueness-validation – steel Sep 17 '15 at 22:13
  • Thanks guys, but i think that there might be a better way to solve this issue – Alex Lomia Sep 18 '15 at 13:30

1 Answers1

2

I think the best solution is to include project_id into items table and make (project_id, item_name) an unique index. To fill project_id column automatically, you can use before_save callback:

class Item
  before_save :fill_project_id_field

  private
  def fill_project_id_field
    self.project_id = self.group.project_id
  end
end

You can of course use validations, but it will not work well. As well as triggers (database side). They both assume you are the only one who works with database in the given moment. It's not always true, and you will end-up with data integrity problems, following this way.

dimakura
  • 7,575
  • 17
  • 36
  • Do you mean `add_index :items, :name, unique { scope: project_id }` by saying to make (`project_id`, `item_name`) a unique index? – Alex Lomia Sep 18 '15 at 13:29
  • I mean `add_index(:items, [:project_id, :item_name], unique: true)`. I've never seen `add_index` with `scope` before. Is it working too? – dimakura Sep 18 '15 at 13:33
  • Seems like I've confused syntax with validations, thanks for pointing that out – Alex Lomia Sep 18 '15 at 14:40