Writing a Rails Array Query

Question

I need to write a validation to make sure that new records created in our application do not have duplicate names. I'm told that validates_uniqueness will not work in this instance because the application already uses it to validate the creation of the original name. This validation will ensure that when the record is updated with a new name, that it too, is original...it's sort of an anti-scope validation. The query also needs to be case insensitive so I will need to write it using MYSQL's UPPER() function, which is why I'll need to use an array query and not a hash.

Here's a sketch of the pseudo code.

Query the CUSTOMER database. Check to ensure the UPDATED_RECORD to see if its NAME is unique. If it's NOT unique check to see if it's ID belongs to record that's being updated. If it is, that's OK as it's the same ID and should be allowed to keep the same name. If the query finds the same name that has a different ID associated with it, then an error message should be displayed such as "This Name is already in use by XXXX."

I'm fairly new to Rails and have been having a bear of a time getting this structured correctly.

Here's some clarification on why I don't think validates_uniqueness will work here.

I think the issue is having multiple versions of the same record, as long as updated record is a new version of the same record (it has the same record ID), then that's valid. But if a new record version is updated with a name that's already used by an existing record (with a different record ID, then that shouldn't be allowed. For example, having two term version records, {:record_id => 100, :name => "Test"} & {:record_id => 100, :name => "Test"}, should be allowed since those two records are versions of the same term. But having two records, {:record_id => 100, :name => "Test"} & {:record_id => 201, :name => "Test"}, should not be allowed since that would result in two versions sharing the same name but associated with different terms.

So you stored the name into the database, and then check if the name is unique??? Why don't check the existence before inserting into the database??? — PeterWong
– PeterWong, Commented Jan 23, 2012 at 18:37
Regarding your clariication: You should just not allow random duplicate data in your db. — David Grayson
– David Grayson, Commented Jan 24, 2012 at 16:58

MrDanA · Accepted Answer · 2012-01-24 17:08:10Z

1

I don't understand why you can't use validates_uniqueness. You said it's already used to validate the creation of the original name, which I am guessing is a column different than the one you want to now check, but that doesn't matter.

class MyModel < ActiveRecord::Base
    validates_uniqueness_of :original_name
    validates_uniqueness_of :new_name, :case_sensitive => false
end

When you create a new record, or update an existing one, these validations will pass. You can also set a uniqueness validation in the migration by adding an index on it. This gives extra security.

EDIT

Regarding your clarification, it might be easier to add a custom validation, as follows:

class MyModel < ActiveRecord::Base
    validate :check_names

    private

    def check_names
        existing = MyModel.find_by_name(self.name)
        unless existing.nil?
            if existing.record_id != self.record_id
                self.errors[:base] << "This name already exists for a different record_id"
            end
        end
    end
end

edited Jan 24, 2012 at 17:08

answered Jan 23, 2012 at 18:43

MrDanA

11.6k2 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

acdanger Over a year ago

I added an edit as to why I'm having trouble figuring out how validates_uniqueness will work here.

MrDanA Over a year ago

See my edit for a different idea based on your new clarification.

acdanger Over a year ago

That looks promising. One of the things I was also struggling with was how to write a SQL function for to make the query uppercase, and thus case insensitive. Any help on that would be awesome.

MrDanA Over a year ago

You're welcome. Be sure to mark the question as answered for anyone else who has a similar question ni the future. As for the case insensitivity, you can use my_string.downcase or .upcase in order to keep strings in one case. You can either down(or up) case items just when checking, or when saving as well.

Collectives™ on Stack Overflow

Writing a Rails Array Query

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related