CODEWITHSUNDEEP
ruby-on-railsdatabasepostgresql
Philippe Dionne
Philippe Dionne

Reputation: 103

How to store an Integer and its relational operator in Postgresql

I have a Measure model with a value attribute who needs to be able to handle these kind of values: 1, 0.11111, >=1, <=0.11111, >1, <1. The logic here is that sometimes I have a discrete value and sometimes a range, depending of what is measured.

I would like to find a solution to store the values while keeping the information related to their relational operator. Also, I would prefer a solution where calculations are made at the database level instead than at the application level.

Upvotes: 0

Views: 339

Answers (1)

Max Williams
Max Williams

Reputation: 32943

Add a new field to your measure model to store the relational type as a string (eg called 'comparison_method'), like ">=", "<" etc. You could add validation to make sure it's in the acceptable list of comparison operators. Store value as a float.

When it comes to testing, bear in mind that "<=", "<", ">=", ">", "==" and "!=" are just methods, like anything else in ruby. There's some special syntactic sugar which allows you to say

2 >= 1.2

but this is actually equivalent to calling a method called ">=" on 2, and passing it 1.2 - like doing this, in other words:

2.>=(1.2)

Another way to call methods in ruby is to use the "send" method. For example instead of saying 

@user.name

you can say

@user.send("name")

and instead of saying

@user.name = "foo"

you can say 

@user.send("name=", "foo")

So, in our case that means that we can say that 

2.>=(1.2)

is equivalent to 

2.send(">=", 1.2)

So, with your saved value and comparison_method, you can test these against another number like so: let's say that @measure has value = "1.111" and comparison_method = ">=". Let's say you have some other number @foo (equal to 2, for example) and you want to test if @foo passes the test in @measure. You would say, for example in the controller:

if @foo.send(@measure.comparison_method, @measure.value)

which is like saying

if @foo >= 1.1111

but in a data-driven way.

You could put the above code into an instance method in Measure, like so:

def passes(x)
    x.send(self.comparison_method, self.value)
end

Now you can say, in the controller, 

if @measure.passes(@foo)

which looks cleaner and is more DRY.

EDIT - incorporating the above into a database search, to do the test at the db level

#load a Measure, eg via params[:id]
@measure = Measure.find(params[:id])
#let's say that @measure.value = "1.111" and @measure.comparison_method = ">="
records = Foo.find(:all, :conditions => ["bar #{@measure.comparison_method} #{@measure.value}"])
#which is like saying 
#@foos = Foo.find(:all, :conditions => ["bar >= 1.111"])

Upvotes: 1

Related Questions