Topic: Rails 3 MySQL Concat two fields with comparison

I am working on a site in which I am going to notify users when their credit cards have become expired. I am trying to write up a simple scope (for testing at first) but am having some issues with writing the SQL to come up with the results.

scope :expired, lambda { where("concat_ws('/', exp_month, exp_year) <= ?", "#{Date.today.month}/#{Date.today.year}") }

The above scope is returning all kinds of records. I am unsure of what I am doing wrong.

Re: Rails 3 MySQL Concat two fields with comparison

You can try mysql functions:

scope :expired, lambda { where("DATE(concat_ws('-', exp_year, exp_month, 1)) <= DATE(concat_ws(YEAR(CURDATE()), MONTH(CUDATE()), 1)" }

Last edited by KindBug (2012-05-29 07:11:28)

Re: Rails 3 MySQL Concat two fields with comparison

The issue of that approach is that on each row mysql will call functions.
So lambda with comparing data is better I think:

scope :expired, lambda { where("DATE(concat_ws('-', exp_year, exp_month, 1)) <= ?", "#{Date.today.month}/01/#{Date.today.year}".to_date) }

Last edited by KindBug (2012-05-29 07:16:13)