To get all jobs which invoice_number is a pure number I do:
Job.where("invoice_number REGEXP '^[[:digit:]]+$'")
Is it possible to do the same by specifying the regex in Ruby rather than MySQL ?
-
do you want to write it in ruby but them pass it to mysql, or do you want to to the regexp matching at the application layer?John Bachir– John Bachir2011-11-09 02:54:26 +00:00Commented Nov 9, 2011 at 2:54
-
I prefer the regex matching to be done at the database level, and I want to write the regex itself in Ruby.Misha Moroshko– Misha Moroshko2011-11-09 03:31:08 +00:00Commented Nov 9, 2011 at 3:31
-
So you're asking for a ruby-to-mysql regex translator?Mark Thomas– Mark Thomas2011-11-09 10:48:34 +00:00Commented Nov 9, 2011 at 10:48
-
@Mark: Yes, I was hoping there is something like that built in Rails.Misha Moroshko– Misha Moroshko2011-11-10 12:32:27 +00:00Commented Nov 10, 2011 at 12:32
-
1@Misha: That would perhaps be a good addition to ActiveRecord, but sadly it doesn't exist. Best thing is to cleanly tuck it away in a named scope, as per the second half of my answer, IMHO.Mark Thomas– Mark Thomas2011-11-13 14:57:46 +00:00Commented Nov 13, 2011 at 14:57
Add a comment
|
3 Answers
One way is
Job.all.select{|j| j =~ /^\d+$/}
but it will not be as efficient as the MySQL version.
Another possibility is to use a named scope to hide the ugly SQL:
named_scope :all_digits, lambda { |regex_str|
{ :condition => [" invoice_number REGEXP '?' " , regex_str] }
}
Then you have Job.all_digits.
Note that in the second example, you are assembling a query for the database, so regex_str needs to be a MySQL regex string instead of a Ruby Regex object, which has a slightly different syntax.
3 Comments
Misha Moroshko
Did you test the second version? I tried
Job.where(["invoice_number REGEXP '?'", /\d+/]) and it returned an error.
Mark Thomas
The named scope still uses the database, so you need to pass in a MySQL regex string rather than a ruby regex object.
Nathan Bashaw
Yeah I got caught on that too. I think you should clarify that a MySQL regex != a Ruby regex for beginners (like me)
Your best bet is either Regexp#tos or Regexp#source or Regexp#inspect.
But I can't think of why you would want to do this -- Ruby doesn't make it easy to compose Regexps programmatically (which is the only reason I can think of why one might want to compose at one level and submit it to another).
Comments
we can write like
scope :only_valid_email_record , :conditions=>["email_id ~ ?","^([a-zA-Z0-9_.'-])+@(([a-zA0-9-])+.)+([a-zA-Z0-9]{2,4})+$"]
It works fine in rails 3.
1 Comment
Mark Thomas
Sorry, this fails on http://louvre.museum/ and many others.