Recently I was browsing Django documentation deliberately looking for some ideas to cross-pollinate to Rails. F() and Q() objects looked like nice little hacks so I decided to implement them for Rails.
Even though it's not nearly as powerful as his Star Trek namesake
it's still pretty interesting to look at (original docs available
here).
It tackles the problem of complex SQL conditions building.
Usually most issues arise from the fact that simple hash based conditions
don't allow for negated or ORed conditions. There are plenty of
other projects aimed to mitigate these restrictions among others (a while
ago I assembled the collection of these):
Squirrel,
Ambition,
dm-sugar-glider,
ez_where,
RQuery,
Searchlogic,
Sequel,
probably most recent one is
MetaWhere.
There are different tricks above libraries employ
instance_eval and/or method_missing letting you
write
[ruby]
# Squirrel
Playlist.find(:all) do
any do
name == "Party Mix"
total_length > 3600
end
end
[/ruby]
or
[ruby]
# RQuery
User.where do |user|
(user.age > 20) | (user.age.in 16,18)
end
[/ruby]
Hash and/or Symbol objects)
[ruby]
# Sequel
Artist.filter(:name.like('Y%') & ({:b=>1} | ~{:c=>3}))
# SELECT * FROM artists WHERE name LIKE 'Y%' AND (b = 1 OR c != 3)
[/ruby]
While there is nothing technically wrong with the approaches listed above,
it's interesting to see what can be done without resorting to any of these
tricks. The Q() object fits there very well. It let's you build ORed,
ANDed, and negated conditions without dropping to writing SQL (drawing
on simple and well known hash-based syntax). Below is the illustration
of Q() based syntax and the corresponding SQL fragments generated by it
[ruby]
~Q(:user_id => nil) # => user_id IS NOT NULL
Q(:user_id => nil) | Q(:id => nil) # => user_id IS NULL OR id IS NULL
~(Q(:user_id => nil) & Q(:id => nil)) # => user_id IS NOT NULL AND id IS NOT NULL
[/ruby]
Ruby 1.9 allows ! operator overloading leading to more
natural syntax for negated conditions
[ruby]
!Q(:user_id => nil) # => user_id IS NOT NULL
[/ruby]
Original docs. It let's you reference your database columns and do
simple calculations on them from your conditions and update statements.
The concept is pretty simple and can be illustrated with a few
examples
[ruby]
# Simple column reference
User.where(:updated_at => F(:created_at))
# instead of writing it in SQL like: User.where("updated_at = created_at")
# Column reference with calculation
User.where(:updated_at => F(:created_at) + 1)
# instead of User.where("updated_at = created_at + 1")
# Calculation with references also work
User.where(:updated_at => F(:created_at) + F(:updated_at))
# instead of User.where("updated_at = created_at + updated_at")
# Use in update statement
User.update_all(:id_copy => F(:id))
# instead of User.update_all("id_copy = id")
[/ruby]
Nice side effect of using this is that you get atomic operations for free
[ruby]
user = User.find(1)
user.views = F(:views) + 1 #
user.save
[/ruby]
Whenever the save is called it's the current `views`
that will be incremented (not the value that that was loaded with
User.find).
You can get the code from my eponymous gem like this [ruby] > gem install dolzenko > irb [/ruby]