CODEWITHSUNDEEP
sqlruby-on-railsrubyactiverecord
Ben Downey
Ben Downey

Reputation: 2665

How to query table col for multiple values

I've done a horrible thing...

I've got a table that contains account balances. It has attributes for the account_id, the amount, and the month_end date. I wanted to query that table for the balances for the last twelve months. But there's gotta be a better way to do it than this.

scope :recent_balances, lambda { { :conditions => 
  ["month_end = ? OR month_end = ? OR month_end = ? OR month_end = ? OR month_end = ? OR month_end = ? OR month_end = ? OR month_end = ? OR month_end = ? OR month_end = ? OR month_end = ? OR month_end = ?", 
  last_month_end.to_s, 
  (last_month_end-1.month).end_of_month.to_s,
  (last_month_end-2.month).end_of_month.to_s,
  (last_month_end-3.month).end_of_month.to_s,
  (last_month_end-4.month).end_of_month.to_s,
  (last_month_end-5.month).end_of_month.to_s,
  (last_month_end-6.month).end_of_month.to_s,
  (last_month_end-7.month).end_of_month.to_s,
  (last_month_end-8.month).end_of_month.to_s,
  (last_month_end-9.month).end_of_month.to_s,
  (last_month_end-10.month).end_of_month.to_s,
  (last_month_end-11.month).end_of_month.to_s ] } }

private

def self.last_month_end
  last_month_end ||= (Date.today - 1.month).end_of_month
end

My questions are:

  1. What's the smart way to do this query? (There's no way it's what I just came up with.)
  2. How can I modify this query to make it more flexible? I'd like to be able to pass in a particular number of months to the query (e.g. query for six months of balances or for 24 months of balances)

Upvotes: 0

Views: 61

Answers (1)

bratsche
bratsche

Reputation: 2674

months = (1..11).map { |m| last_month_end.advance(months: 0 - m) }

where("month_end IN (?)", months)

Or something like that. That would be a more readable and maintainable way of doing what you did above. As someone else said, you can check if a date is in between a start and an end date in SQL.

where("date >= ? AND date <= ?", start_date, end_date)

or (better yet)

where("date BETWEEN ? AND ?", start_date, end_date)

Upvotes: 1

Related Questions