CODEWITHSUNDEEP
ruby-on-rails
John
John

Reputation: 275

Searching value range rails

How can I add another search value in the scope and add it into the terms.map and pass it on to the where query, I want to have min and a max value?

  scope :search_query, lambda { |query|
  return nil  if query.blank?

  # condition query, parse into individual keywords
  terms = query.downcase.split(/\s+/)

  # replace "*" with "%" for wildcard searches,
  # append '%', remove duplicate '%'s
  terms = terms.map { |e|
    (e.gsub('*', '%') + '%').gsub(/%+/, '%')
  }
  num_or_conds = 2
  where(
    terms.map { |term|
      "(LOWER(students.first_name) LIKE ? OR LOWER(students.last_name) LIKE ?)"
    }.join(' AND '),
    *terms.map { |e| [e] * num_or_conds }.flatten
  )
}

What i would like to do

.where(column_name BETWEEN #{value1} AND #{value2})

Upvotes: 0

Views: 90

Answers (1)

max
max

Reputation: 102423

You can chain scopes in rails by just calling where repeatedly:

Thing.where(a: 1).where(b: 2)
# SELECT things.* FROM things WHERE things.a = ? AND things.b = ?

You can also use .merge to merge scopes:

Thing.where(a: 1).merge(Thing.where(b: 2))

Use a range create a BETWEEN query:

Thing.where(foo: (1..10))
# SELECT things.* FROM things WHERE foo BETWEEN 1 AND 10

This also works for dates and times.

Another thing to bear in mind in that scope is just syntactic sugar for class methods. So if your method does not fit in a one-liner you should use the the "classical" method definition:

class Student < ApplicationRecord
  def self.search_query(query)
    scope = self.all

    terms = query.downcase.split(/\s+/)
    terms = terms.map { |e|
      (e.gsub('*', '%') + '%').gsub(/%+/, '%')
    }

    self.all.tap do |scope|
      terms.each do |term|
        scope.merge(
          self.where("(LOWER(students.first_name) LIKE :t OR LOWER(students.last_name) LIKE :t)", t: term)
        )
      end
    end
  end
end

Upvotes: 1

Related Questions