Gem available, install with: gem install johnbender-rquery

0.2 additions

The key bit is the ability to add multiple operations to the same line connected with | or & operators. In this way you can form extremely complex queries, where as before you were limited to whatever you could get out a series of anded logic. The only stipulation is that you you have to wrap the operations with parenthesis if they are on the same line, or ruby will complain about a syntax error.

#All operations need to be on the same line and in parens
#either the | operator or the & operator can be used on a singel line
User.where do |user|
  (user.age.is > 20) | (user.age.in 16,18)
end

The above example should be easy to follow but there is one important gotcha with including &’s and |’s on the same line. Because the & operator has precedence in a string of operations if you want both | and & on the same line you need to force precedence for the | where it needs to be evaluated before the & in the resulting query.

#the & takes precedence here and will be grouped with the contains "Alice" which will be
#or'd with the contains "George"
#=> name contains "George" or (name contains "Alice and age from 20 to 30)
User.where do |user|
  (user.name.contains "George") | (user.name.contains "Alice") & (user.age.from 20..30)
end

In this case the query writer wanted to get the users who’s names contained either George or Alice and then filter with an age range. What it’ll actually do is find the users who’s name contains Alice and have an age between 20 and 30, and in add in anyone with a name like George. To fix this you can add more parens :(

#to correct the above to the more intuitive version add parens to force precedence of the
#contains operations
#=> (name contains "George" or name contains "Alice) and age from 20 to 30
User.where do |user|
 ((user.name.contains "George") | (user.name.contains "Alice")) & (user.age.from 20..30)
end

Or you can simply move the and’d operation to the next line as the operations are evaluated top down as the ruby block is executed and they are grouped using and.

#in this sutation it would be cleaner and easier to just move the and'd statement down
#a line as all seperate lines are and'd and lines have precedence from top to bottom
#additionaly operations on seperate lines don't need parens
User.where do |user|
 (user.name.contains "George") | (user.name.contains "Alice")
 user.age.from 20..30
end

The biggest change here is simply being able to use the | instead of the default for each line (and). And finally, if you checked out RQuery before, you’ll have noticed I’m passing in an object that represents the model being queried. The advantage is there’s no class polution, and the not so obvious advantage is that it will throw and nice verbose and descriptive exception if you try and use an attribute that doesn’t exist for a given model.

#should you attempt to use and attribute that doesn't exist for a given model
#rquery will tell you before it's sent to the db
User.where do |user|
  user.ssn.is == "123-45-6789"
end
# RQuery::AttributeNotFoundError: The field 'ssn' doesn't exist for this object
#       from /Users/johnbender/Projects/rquery/lib/rquery/attribute_collection.rb:28:in `method_missing'
#       from (irb):24
#       from /Users/johnbender/Projects/rquery/lib/rquery/active_record/base.rb:16:in `where'
#       from /Users/johnbender/Projects/rquery/lib/rquery/active_record/base.rb:11:in `synchronize'
#       from /Users/johnbender/Projects/rquery/lib/rquery/active_record/base.rb:11:in `where'
#       from (irb):23

My specs cover most of the code and functionality for the activerecord extension (still the only piece I’ve worked on) and you can still use the symbol syntax if you want.

#environment config
RQuery.use_symbols

#example of using symbols, you can see more at the RQuery page on my site.
User.where do
 (:name.contains "George") | (:name.contains "Alice")
 :age.from 20..30
end

With RSpect

This started out with writing a ruby library for talking to Mnesia using grove. It struck me that it would be nice to have a way to write the same query and have it translated by an adapter to either SQL or the JSON structure that grove uses. Why write the query twice when they do nearly the same thing? Thus I started writting my own mini-dsl for building queries in ruby (see here).

The result, at least the Rails piece (where), is rquery. Named after rspec because some of the code was inspired by how rspec implements its basic should method, it aims to be easy to read and easy to use.

The big advantage, outside of providing a generic way of querying data in ruby which is down the road, comes in the form of readability in your rails controllers. When using SQL snippets with the find method and ActiveRecord, the code isn’t _complex_ but I think rquery makes it a bit more readable.

#Users who's last logon was in '05
 
#ActiveRecord
User.find(:all, :conditions => ["last_logon between ? and ?", "2005-01-01", "2005-12-31"])
 
#rquery
User.where  {
    :last_logon.between "2005-01-01", "2005-12-31" 
}

It adds a couple instance methods to the Symbol class, which it uses to represent column names. It supports most common operators (>, < , ==, etc), between, from, in, and contains in the affirmative:

#where :foo is a column of some table
ActiveRecord::Base.where{
    :foo.is == "bar"
    :foo.is > 1
    :foo.is < 2
    :foo.is >= 3
    :foo.is < = 4
    :foo.contains "bar"
    :foo.between 10..20
    :foo.from 10, 20
    :foo.in 1,2,3,4,5
}

It supports ==, in, between, and from in the negative:

ActiveRecord::Base.where{
    :foo.is_not == "bar"
    :foo.is_not.between 10..20
    :foo.is_not.from 10, 20
    :foo.is_not.in 1,2,3,4,5
}

Also, you can specify a limiting option on the returned results:

ActiveRecord::Base.where(:first){
    :foo.between 1..200
}
 
ActiveRecord::Base.where(:last){
    :foo.between 1..200
}

Other things

There are some things to note if you want to take a look at the source.

• It uses class variables to store each statement in the where block, and as a result requires a mutex. I couldn’t figure out another way to keep the syntax this close to english and avoid using globalish state.
• To configure an adapter you have to assign to RQuery.adapter, which seems clunky. I just wasn’t sure how else to get that done. It should really be assigned based on what’s in the rails db config, at least for the rails extension. Input is welcome.
• The OperationSerializer should really define its methods based on what is defined in the adapter. The adapter is really sparse and requires little effort, so this would make it much more extensible. It will be refactored, but I haven’t figured out how to accomplish this goal and also allow for flexible input. For example, between and from can take an arguments list, an array of elements or a range, but as a consequence require some specific logic to work.

Otherwise fork away, check out the video, install the gem (github: johnbender-rquery), and/or please leave me any feedback. I’m all about learning.

Once you click play, the little HD icon will hopefully pop-up on the bottom right.

Soho Cab Ride – The Ballistic Brothers