Rails: ActiveRecord using where with a custom comparator

Question

I have a model called Update which contains column :version which is a string.

class Update < ApplicationRecord
  validates_presence_of :version
end

The version column contains string values such as 1.0.1. Using Gem::Version, you can compare version numbers like this:

Gem::Version.new('2.1') > Gem::Version.new('2.0.1')

I'd like to do a query where I can get all of the records with version above a certain number. Is there a way to pass a custom method to .where so that I can use the version comparator? Such as:

class Update < ApplicationRecord
  def version_is_greater_than(that_version)
    Gem::Version.new(self.version) > Gem::Version.new(that_version)
  end
end

...

Update.where(&:version_is_greater_than)

As you can see, the aforementioned code does not work because I need to pass a version string into that method which I can't seem to do via &:. Is there a better way to do this so that I can just compare the values within the .where call? I am trying to stay away from splitting out the major/minor/etc. numbers because I'd just be reinventing the wheel there.

I'm using Postgres. Simple `1.2.3` format is acceptable. This has nothing to do with any of my gem versions - but I thought that the comparator was at least good enough for my needs. — Manu Kanthan, Jan 24 '17 at 21:43
I think it is easy enough to do this `Update.all.select { |u| Gem::Version.new(u.version) > Gem::Version.new("2.0") }` but I was just wondering if there was a way to combine it with `.where` as to filter the results based on other attributes as well. — Manu Kanthan, Jan 24 '17 at 22:04

score 1 · Accepted Answer · answered Jan 24 '17 at 23:51

You can't use Ruby methods inside the database and everything in a where is executed inside the database. You can, however, implement simple major.minor.patch version number comparisons inside the database.

PostgreSQL supports array columns and arrays compare element by element so things like:

array[1,2,3] < array[1,5,0]

work as expected inside the database (i.e. the same as [1,2,3] < [1,5,0] in Ruby).

One approach would be to convert your string column to an array of integers, send the version number into the database as an array, and let the database do the comparison. A migration like:

connection.execute(%q{
  alter table updates
  alter column version
  set type int[] using regexp_split_to_array(version, '\\.')::int[]
})

should take care of the database. Then you could say:

def version_is_greater_than(that_version)
  self.version > that_version.split('.').map(&:to_i)
end

or query the database:

def self.version_is_greater_than(that_version)
  where('version > array[?]', that_version.split('.').map(&:to_i))
end

You'd need to update the code that reads and writes versions to account for the new type of the version column as well.

Alternatively, you could leave version as a string in the database and use regexp_split_to_array in a where call when you need to compare things:

where(%q{
  regexp_split_to_array(version, '\\.')::int[] > array[?]
}, that_version.split('.').map(&:to_i))

There is PL/Ruby that allows you to write stored procedures in Ruby but it hasn't been updated for seven years and assumes that Ruby 1.8.7 is current. We can pretend that it doesn't exist.

score 0 · Answer 2 · edited May 23 '17 at 12:16

No, you cannot use it this way.

& operator turns a method into a proc, and pass it the argument when you invoke a method that takes a block (like those enumerable methods). Check out this thread for additional info.

what is the functionality of "&: " operator in ruby?

The where method does not take block. When you call where(...), you are essentially calling WhereChain.new(...). Refeter this here:

https://github.com/rails/rails/blob/master/activerecord/lib/active_record/relation/query_methods.rb

What you are trying to do can be accomplished by wrapping the method into a sql object, or simply a method that returns a sql string. But unfortunately, I don't think sql (mysql in particular) has comparison operation like >, < for strings (or varchar). So you can't do it natively in sql, which means, you won't be able to generate a sql string and pass it to the where clause to achieve this. Refer to sql doc here:

https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_greater-than

Given the use case, there are very limited number of versions for a particular gem update, so imho, it's safe and fast to just get all the records and operate on the ruby level.

Rails: ActiveRecord using where with a custom comparator

2 Answers2