shirt.rb
[ id, size, color ]
As the input I get value params[:size] which equals for example 170. Data in shirts table size column stores in format - "160-180" (so it is a string)
How can I preform query like:
Shirt.where("parse_first_number(size) > ? AND parse_second_number(size) < ?", params[:size], params[:size])
I'd highly recommend you improve the database schema by having two values: min_size and max_size. You would then be able to do a much simpler query:
Shirt.where("min_size <= ? AND max_size >= ?", params[:size], params[:size])
The problem as you've proposed it is possible, but much more complicated.
size attribute, which is not easy to migrate also (although I bet it probably is simple...), then you could temporarily leave the data duplicated.Shirt.all.each {|s| s.update_attributes(min_size: s.size.split('-').first, max_size: s.size.split('-').last }
That's basically an issue with your stored data column type. If you store a string, the database cannot convert it to a number and compare it. One way would be to go with another database that support ranges but the more convenient way is to use either two columns (size_from, size_to) or store a named variant of your size and keep a valued constant in your code like
class Shirt
SIZES = [ { large: (160..180), ... ].freeze
Let me reiterate that this is bad design and you should use separate columns.
With that said, there is no database agnostic solution. Here is one for postgres. Note that this is ugly and slow:
size_in_bounds_condition = <<-SQL
CAST(split_part(coalesce(size, '0-0'), '-', 1) AS integer) < ?
AND
CAST(split_part(coalesce(size, '0-0'), '-', 2) AS integer) > ?
SQL
Shirt.where(size_in_bounds_condition, params[:size], params[:size])
Also ugly and slow(this is the essence of technical debt). But it might work for now if the Shirts table is small.
shirt_size = 170
shirts = [
{id:1, size:"160-180", color:"red"},
{id:2, size:"180-200", color:"red"},
{id:3, size:"160-180", color:"blue"}
]
shirts.select do |s|
min, max = s[:size].split("-").map(&:to_i)
max > shirt_size && min < shirt_size
end
=> [{:id=>1, :size=>"160-180", :color=>"red"}, {:id=>3, :size=>"160-180", :color=>"blue"}]
Rails accepts a Range as value in a #where hash condition:
sizes = "160-180" # this comes from your DB I presume
min, max = sizes.split('-').collect(&:to_i)
Shirt.where(size: min..max)
If you want to check if the parameter provided is within range, it's just as simple:
def valid_size?
sizes = "160-180" # again from your shirt in DB
min, max = sizes.split('-').collect(&:to_i) # when you get tired of this line, it means it's time to refactor it
# collapse the following into one line
params[:size] && # check for presence
params[:size].between?(min, max) && # check if in range
params[:size] # I guess you want to return the size if valid, rather than only a boolean
end
# Alternative checks, that don't need the presence check:
# - (min..max).member?(params[:size])
# - (min..max).include?(params[:size])
# I choose what I find more explicit and readable
Finally, I agree you should migrate the DB to have min and max size stored as two separate integers. So as usual, write a test for your function, then code the simplest solution to get the tests green. At that point, by all means, go ahead and refactor your DB :)
# migration
def up
add_column :shirts, :min_size, :integer
add_column :shirts, :max_size, :integer
Shirt.find_each do |shirt|
min, max = shirt.size.split('-').collect(&:to_i) # for the last time :)
shirt.min_size = min
shirt.max_size = max
shirt.save!
end
remove_column :shirts, :size
end
def down
add_column :shirts, :size, :string
Shirt.find_each do |shirt|
size = "#{shirt.min_size}-#{shirt.max_size}"
shirt.size = size
shirt.save!
end
remove_column :shirts, :min_size, :integer
remove_column :shirts, :max_size, :integer
end
