Bob Nicholson Bob Nicholson - 1 year ago 77
Ruby Question

ActiveRecord select by field comparison

I'm trying to select records where two fields do not have equal values.
(where field A does not have the same value as field B). This involves alias_attributes. I would like to be able to use the alias names and not the cryptic database names so that the rails code can be isolated from the database names.


alias_attribute :item_amount, :fielda
alias_attribute :item_applied_amount, :fieldb

This codes works:

query = where('fielda != fieldb')

But I'd rather have something like:

query = where.not(item_amount: item_applied_amount:)

Answer Source

IMHO you cannot do that, because the database knows nothing about the aliases defined in your model.

Furthermore even if the aliases were working, where(item_amount: :item_applied_amount) would be translate to fielda = 'fieldb' in SQL. That means: The value of the fieldacolumn must equal the string "fieldb" (not the value of the column fieldb)

But you can use attribute_alias(name) to get the original name and use the returned strings to build a proper query:

where("#{attribute_alias(:item_applied)} != #{attribute_alias(:item_applied)}")

Or you might want to define a scope to improve readablity:

scope :without_matching_amounts, -> { where('fielda != fieldb') }

and use it like this:

query = Model.without_matching_amounts
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download