Cj Monteclaro Cj Monteclaro -4 years ago 139
SQL Question

Using find_by_sql in range gives error: undefined method `value_for_database' for "2017-01-01":String

This error showed up when I was trying to search from a range of date.

This is my model:

def self.search(search)
if search
@policies = Policy.find_by_sql("acct_ent_date IN ?", start_date..end_date)

Answer Source

You rarely need to drop down to writing raw SQL in rails (e.g. using the find_by_sql method) - especially for such a simple query as this.

Instead, you can just write the following and ActiveRecord will correctly convert it to valid SQL syntax for you:

# If you are looking for a list of all matching entries:
Policy.where(acct_ent_date: start_date..end_date)

# If you only wish to fetch the FIRST matching entry:
Policy.find_by(acct_ent_date: start_date..end_date)

This will generate SQL like the following:

SELECT `policies`.* FROM `policies` WHERE (`policies`.`acct_ent_date` BETWEEN xxxxx AND yyyyy)

The key problem with your original (raw SQL) code is that you are using WHERE IN syntax - which is really just shorthand for multiple OR conditions. This does not make sense to use with a Range (start_date..end_date) object, as this is not a discrete list (i.e. an Array).

If you were to attempt to convert your object into an array, you would see an error something like this:

(start_date..end_date).to_a # => TypeError: can't iterate from Time
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download