Combining params in Rails

Question

Hi im doing a filter section for "players" on my app..

Im filtering by "position" at this moment, but i need to filter by "birthday.year" in database the birthday is complete 1900-00-00

I actually do a good research before but i can "mix" or "combine" my params.. the best answer i found was here (so its not a duplicate) Rails: combining optional params into a query

im a noob in Rails,so i will appreciate any help im just doing the integration of a design..

Here is my code but how can i use minyear, and maxyear to filter by position and age, for example..

thanks!!

def index
    @candidates = Player.order("created_at DESC")

    position = params[:position]
    minyear = params[:minyear]
    maxyear = params[:maxyear]


    if position == 'goalkeeper'
  @candidates = @candidates.where(position:'goalkeeper') 
 elsif position == 'cedefense'
  @candidates = @candidates.where(position:'cedefense') 
 elsif position == 'ridefense'
  @candidates = @candidates.where(position:'ridefense') 
 elsif position == 'ledefense'
  @candidates = @candidates.where(position:'ledefense') 
 elsif position == 'defmedium'
  @candidates = @candidates.where(position:'defmedium') 
 elsif position == 'ofemedium'
  @candidates = @candidates.where(position:'ofemedium') 
 elsif position == 'rimedium'
  @candidates = @candidates.where(position:'rimedium') 
 elsif position == 'lemedium'
  @candidates = @candidates.where(position:'lemedium') 
 elsif position == 'offensive'
  @candidates = @candidates.where(position:'offensive') 
 elsif position == 'scoach'
  @candidates = @candidates.where(position:'scoach') 
 elsif position == 'sprepf'
  @candidates = @candidates.where(position:'sprepf')                 
 else
  @candidates = Player.all
 end

After a lot of research i come up with this

 position = params[:position]
    minyear = params[:minyear]
    maxyear = params[:maxyear]

  if  params[:position].nil? 
  @candidates = Player.all
  elsif  !params[:position].nil? && params[:minyear].nil?
  @candidates = @candidates.where("position = ?", position) 
  elsif !params[:minyear].nil?
  @candidates = @candidates.where("position = ? and birthday = ?", position, minyear )                 
 else
  @candidates = Player.all
 end

The only problem now is that birthday as i said before has a full format, im just interested just in the year... how can i solve this?

thanks in advance

Nevermind it works like this

@candidates = @candidates.where("position = ? and birthday < ?", position, minyear )

Thanks to Alex D i ha this now,

@candidates = Player.scoped # for Rails 3
 if params[:position].present?
   @candidates = @candidates.where(position: position)
 end


 if year = params[:year]
   date = Date.new(year)
   # this will find all people whose birthday is within the given year
   # using YEAR(birthday) will likely cause a full table scan;
   #   it's better to use a range query
   @candidates = @candidates.where("birthday >= ? AND birthday <= ?", Date.new(minyear), Date.new(maxyear).end_of_year)
 end

if u have date field u can extract year simply by using sql `.where("extract (year from birthday) = #{your_value}")` — Gopal S Rathore, Dec 07 '13 at 05:21
@Gopalrathore, good point -- but the DBMS may use a full table scan for that. My answer shows a way which will use indexes (if the right indexes are there). — Alex D, Dec 07 '13 at 05:22

score 1 · Accepted Answer · edited Dec 07 '13 at 23:04

1

First of all, you can improve your code if you know that Active Record's where and other similar methods are chainable:

 @candidates = Player.order('created_at DESC')
 # if you don't want to set a default order, you can use Player.scoped in Rails 3
 # I forget what it is for Rails 4. Maybe just Player.all.
 # In Rails 3, .all returns an Array, which doesn't allow you to chain additional
 #   where conditions, etc.
 if params[:position].present?
   @candidates = @candidates.where(position: position)
 end
 if params[:minyear].present?
   @candidates = @candidates.where(birthday: minyear)
 end

Now the second part: you actually want to match on the birthday year. There are a couple ways to do this, but this is the way which can benefit from database indexes if you have the right indexes in place:

 if year = params[:year]
   date = Date.new(year)
   # this will find all people whose birthday is within the given year
   # using YEAR(birthday) will likely cause a full table scan;
   #   it's better to use a range query
   @candidates = @candidates.where("birthday >= ? AND birthday <= ?", date, date.end_of_year)
 end

Since your param is called minyear, I'm guessing you may actually want all the people whose birthday is during the given year or later. In that case:

 @candidates = @candidates.where("birthday >= ?", Date.new(year))

Or if it's minyear and maxyear:

 @candidates = @candidates.where("birthday >= ? AND birthday <= ?", Date.new(minyear), Date.new(maxyear).end_of_year)

edited Dec 07 '13 at 23:04

the Tin Man

158,662
42
215
303

answered Dec 07 '13 at 05:21

Alex D

29,755
7
80
126

1

Cool, and can simplify that long conditional with: `positions = %w(goalkeeper cedefense ridefense ledefense defmedium ofemedium rimedium lemedium offensive scoach sprepf)` then `if positions.include?(position)` – Leo Gallucci Dec 07 '13 at 05:25
My only question on this will be.. if the normal syntax on post works i mean ?position=value . right? and how can i post for example "date" wich are the names of params for date and date.end – Moncho Chavez Dec 07 '13 at 05:25
ABout your edit, i have minyear and maxyear. so you can set a range.. thats the idea, kind of lost on the last part – Moncho Chavez Dec 07 '13 at 05:30
For the last line to work (minyear && maxyear) i have to change the condition of if year = params[:year] for if year = params[:maxyear] or, im just missing that part, for the rest is working Like charm1!!, will see my code at the bottom of question, im editing – Moncho Chavez Dec 07 '13 at 05:39
My only problem is i cant sent params now, it dosent display error but this dosent work in the URL &minyear=1900&maxyear=1912 .. it shows 1913 or 1985 years – Moncho Chavez Dec 07 '13 at 05:59
`if year = params[:year]` was just an example to give you the idea. In your case, it should be `if minyear = params[:minyear] && maxyear = params[:maxyear]`. – Alex D Dec 07 '13 at 06:01

score 1 · Answer 2 · edited Dec 07 '13 at 23:02

1

@candidates = @candidates.where("position = ? and year(birthday) < ?", position, minyear )

Try it, this will serve your purpose.

edited Dec 07 '13 at 23:02

the Tin Man

158,662
42
215
303

answered Dec 07 '13 at 05:25

Akshat

615
7
18

1

But i still find there is scope for optimisation and you must work on it. Good Work!! otherwise – Akshat Dec 07 '13 at 05:26

Combining params in Rails

2 Answers2