I have a SQL query that I am running on a SQLite database. It works fine to retrieve records in a date range for Saturday & Sunday between 7AM and 6PM.
When I run the query it returns an array of hashes. I want to convert it to an ActiveRecord Query so it returns an array of objects that I can more easily operate on.
ActiveRecord::Base.connection.execute("select * from reports where datetime between '2015-03-25' and '2015-04-12' and strftime('%w', datetime) IN ('0','6') and strftime('%H', datetime) >= '07' and strftime('%H:%M', datetime) I've looked at the docs and am uncertain how to go about this, any help would be very much appreciated!
Update: Getting pretty close with this:
Report.where("datetime >= ? AND datetime <= ?", '2015-03-25 21:15:00', '2015-04-27 07:56:00').where("cast(strftime('%H', datetime) as int) >= ? AND cast(strftime('%H', datetime) as int) <= ?", 07,17).where("cast(strftime('%w', datetime) as int) = ?", 0)
Still need to sneak in the option of 1 (in addition to 0) on the last where clause. I am guessing it could be cleaned up as well. Still open to input!
For this complicated SQL query string, I think you should use find_by_sql of ActiveRecord. Document here: http://api.rubyonrails.org/classes/ActiveRecord/Querying.html#method-i-find_by_sql.
Report.find_by_sql("select * from reports where datetime between '2015-03-25' and '2015-04-12' and strftime('%w', datetime) IN ('0','6') and strftime('%H', datetime) >= '07' and strftime('%H:%M', datetime)