Virtual blocks fail silently

[janko]

I wanted to do regex mathching inside a virtual block, and I wrote this:

DB[:questions].where{title =~ /#{query}/i}

The query didn't work, and after reading the section about virtual blocks I saw that there is no operator for matching regexes inside of virtual blocks. What surprised me is that Sequel didn't raise any errors. What it did is simply ignore that WHERE clause in the final query:

SELECT * FROM "questions"

Is this an intentional part of the design? Is it possible to make Sequel raise an error for mistakes like these? Because this way writing virtual blocks is very brittle.

[jeremyevans]

You don't need a special operator for regexp matching in virtual row blocks, since they can be specified via a hash: DB[:questions].where(:title=>/#{query}/i). Your example is not specific to virtual row blocks, it applies to Sequel generally: DB[:questions].where(Sequel.expr(:title) =~ /#{query}/i)

It's possible to support what you want fairly easily:

class Sequel::SQL::GenericExpression
  def =~(regexp)
    Sequel.expr(self=>regexp)
  end
end

I'm not sure what the backwards compatibility effects would be, but I'm guessing they would be minimal, so that's something I would consider. If you think that should be added, please open up a thread on the sequel-talk Google Group and see what the communities' thoughts are.

Sequel doesn't raise an error in this case, because the =~ call returns nil, so what you are doing is: DB[:questions].where{nil}, and Sequel treats where(nil) as a no-op.

Virtual row blocks don't add any features to Sequel, they are purely a way to make code less verbose. In general, they just offer a way to easily create Sequel::SQL::Identifier, Sequel::SQL::QualifiedIdentifier, and Sequel::SQL::Function instances. They don't override standard ruby behavior. Your issue here is you are calling the =~ method on an object that doesn't override the default ruby Object#=~ behavior of returning nil.

[janko]

Wow, I did not know that =~ is defined on Object. Then I totally get why this behaviour happens, I thought it was something general. Thank you for the explanation!

Yeah, I might suggest adding =~ to Sequel::SQL::GenericExpression. I would normally use a simple hash: where(title: /#{query}/i), but I want to use the virtual row because I need to write an OR statement.