How to query table col for multiple values

Question

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:

What's the smart way to do this query? (There's no way it's what I just came up with.)
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)

Do you want "last year" to be from 1/1 to 12/31 or from a year-ago-today to today? In either case, databases support searching between ranges of dates, using something like "date >= startdate and date <= enddate". — the Tin Man
– the Tin Man, Commented Jul 21, 2013 at 2:27
I'm looking to do a query from the most recent month end to a year-ago, not necessarily a Jan-Dec calendar year. I hadn't thought about this strategy: "date >= startdate and date <= enddate", but it seems like an elegant solution. — Ben Downey
– Ben Downey, Commented Jul 21, 2013 at 2:33
I'm not sure it's elegant, but it's how I've always seen it done. — the Tin Man
– the Tin Man, Commented Jul 21, 2013 at 2:42

bratsche · Accepted Answer · 2013-07-21 07:28:00Z

1

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)

answered Jul 21, 2013 at 7:28

bratsche

2,66421 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Casper Over a year ago

ActiveRecord finders supports an even more compact IN form: where(month_end: months).

Collectives™ on Stack Overflow

How to query table col for multiple values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related