CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresqlruby-on-rails-5
The Whiz of Oz
The Whiz of Oz

Reputation: 7043

Picking objects with ranges using input number in Rails

I have objects that have a min/max age range on them:

name: "Event 1"
type: Package # inheritance
min_age: 1
max_age: 8
category_id: 2 # Birthday

I would like to select and return these objects based on user preference of age and type by looking if the object's age range satisfies the inputted number.

Currently I am stuck with:

Controller code:

age = params[:age]
item_type = params[:item_type]
@all_items = Item.where("categories.name = ?", 'birthday')
@events = @all_items.where("(type: #{item_type}) AND ...here I need to lookup if the #{age} variable is within the object range")

Now the problem is that I need to pick a birthday event by inputted user number, having Range on the object itself. Is there a way of doing that without going through every single object?

Upvotes: 1

Views: 45

Answers (1)

Andrey Deineko
Andrey Deineko

Reputation: 52357

@events = @all_items.where("type = #{item_type} AND min_age >= #{age} AND max_age <= #{age}")

I suppose the associations look as follows:

Item has_many :categories
Category belongs_to :item

In this case 

Item.where("categories.name = ?", 'birthday')

will most likely fail because categories are not joined with something like

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "categories"

Here is what you'll want to do:

Item.joins(:categories)
    .where(categories: { name: 'birthday' })
    .where("items.type = '#{item_type}' AND items.min_age >= #{age} AND items.max_age <= #{age}")
    .group('items.id')

Using PostgreSQL's BETWEEN might make the query look nicer and shorter:

Item.joins(:categories)
    .where(categories: { name: 'birthday' })
    .where("items.type = '#{item_type}' AND #{age} BETWEEN items.min_age AND items.max_age")
    .group('items.id')

Upvotes: 3

Related Questions