Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find records included in range of numbers (min - max)

Course.rb

min_age: integer
max_age: integer

Student age comes from params[:age] - for example 15, that means student is 15 years old and he looks for courses that will cover his age:

I have courses:

id    min_age   max_age
------------------------
1     5          15
2     10         25
3     10         55
4     20         40

Question:

How can I find all records where min_age and max_age covers the age param value? If student says he is 15 years old the courses he should see is:

1, 2, and 3 as these are the ones who covers this age.

More, I need to use this in search model that creates a search record when when someone searches for courses and the results returned back are users (tutors who offer these courses).

  def find_courses
    users = User.joins(:courses).where("courses.lesson ILIKE ?", "%#{lesson}%")
    # failed attempt:
    users = users.where('course.min_age >= :age or courses.max_age <= :age', age: age)
  end

Thank you for your time.

Based on accepted answer:

Course.where('min_age <= :age AND max_age >= :age', age: 18)

the above sql will require for both conditions to be true in order to display the record:

id    min_age   max_age
------------------------
1     5  true  + 15 false = false
2     10 true  + 25 true  =  true
3     10 true  + 55 true  =  true
4     20 false + 40 true  =  false

That will return records with id: 2 and 3

like image 763
rmagnum2002 Avatar asked Mar 04 '14 10:03

rmagnum2002


3 Answers

Change the greater than/less than symbols and use AND

Course.where('min_age <= :age AND max_age >= :age', age: 18)
like image 172
Andreas Lyngstad Avatar answered Nov 07 '22 20:11

Andreas Lyngstad


Your condition should be

def find_courses
  user_courses = User.joins(:courses).where('courses.lesson ILIKE ?', "%#{lesson}%")
  user_courses.where(':age >= courses.min_age and :age <= courses.max_age', age: age)
end
like image 44
Achrome Avatar answered Nov 07 '22 18:11

Achrome


By implementing the resource in a more Rails like way, the structure will allow easy querying:

in routes.rb:

resources :users, only: :show do
  resources :courses, only: :index
end

in CoursesController#index:

@courses = current_user.available_courses

in User model:

def available_courses
  Course.where('min_age <= ? and max_age >= ?', age, age)
end

Also in the way of logic and reusability I suggest giving a User a date_of_birth:datetime attribute and setting a method in the User model to return it's age.

like image 1
dennis Avatar answered Nov 07 '22 18:11

dennis