query.rb

Path: lafcadio/query.rb
Last Update: Tue Mar 21 08:02:19 PST 2006

Overview

By passing a block to ObjectStore, you can write complex, ad-hoc queries in Ruby. This involves a few more keystrokes than writing raw SQL, but also makes it easier to change queries at runtime, and these queries can also be fully tested against the MockObjectStore.

  big_invoices = Invoice.get { |inv| inv.rate.gt( 50 ) }
  # => runs "select * from invoices where rate > 50"

This a full-fledged block, so you can pass in values from the calling context.

  date = Date.new( 2004, 1, 1 )
  recent_invoices = Invoice.get { |inv| inv.date.gt( date ) }
  # => runs "select * from invoices where date > '2004-01-01'"

Building and accessing queries

To build a query and run it immediately, call DomainObject.get and pass it a block:

  hwangs = User.get { |u| u.lname.equals( 'Hwang' ) }

You can also call ObjectStore#[ plural domain class ] with a block:

  hwangs = ObjectStore.get_object_store.users { |u|
    u.lname.equals( 'Hwang' )
  }

If you want more fine-grained control over a query, first create it with Query.infer and then build it, using ObjectStore#query to run it.

  qry = Query.infer( User ) { |u| u.lname.equals( 'Hwang' ) }
  qry.to_sql # => "select * from users where users.lname = 'Hwang'"
  qry = qry.and { |u| u.fname.equals( 'Francis' ) }
  qry.to_sql # => "select * from users where (users.lname = 'Hwang' and
                   users.fname = 'Francis')"
  qry.limit = 0..5
  qry.to_sql # => "select * from users where (users.lname = 'Hwang' and
                   users.fname = 'Francis') limit 0, 6"

Using Query.infer, you can also set order_by and order_by_order clauses:

  qry = Query.infer(
    SKU,
    :order_by => [ :standardPrice, :salePrice ],
    :order_by_order => :desc
  ) { |s| s.sku.nil? }
  qry.to_sql # => "select * from skus where skus.sku is null order by
                   standardPrice, salePrice desc"

Query inference operators

You can compare fields either to simple values, or to other fields in the same table.

  paid_immediately = Invoice.get { |inv|
    inv.date.equals( inv.paid )
  }
  # => "select * from invoices where date = paid"

Numerical comparisons: lt, lte, gte, gt

lt, lte, gte, and gt stand for "less than", "less than or equal", "greater than or equal", and "greater than", respectively.

  tiny_invoices = Invoice.get { |inv| inv.rate.lte( 25 ) }
  # => "select * from invoices where rate <= 25"

These comparators work on fields that contain numbers, dates, and even references to other domain objects.

  for_1st_ten_clients = Invoice.get { |inv|
    inv.client.lte( 10 )
  }
  # => "select * from invoices where client <= 10"
  client10 = Client[10]
  for_1st_ten_clients = Invoice.get { |inv|
    inv.client.lte( client10 )
  }
  # => "select * from invoices where client <= 10"

Equality: equals

  full_week_invs = Invoice.get { |inv| inv.hours.equals( 40 ) }
  # => "select * from invoices where hours = 40"

If you’re comparing to a domain object you should pass in the object itself.

  client = Client[99]
  invoices = Invoice.get { |inv| inv.client.equals( client ) }
  # => "select * from invoices where client = 99"

If you’re comparing to a boolean value you don’t need to use equals( true ).

  administrators = User.get { |u| u.administrator.equals( true ) }
  administrators = User.get { |u| u.administrator } # both forms work

Matching for nil can use nil?

  no_email = User.get { |u| u.email.nil? }

Inclusion: in and include?

Any field can be matched via in:

  first_three_invs = Invoice.get { |inv| inv.pk_id.in( 1, 2, 3 ) }
  # => "select * from invoices where pk_id in ( 1, 2, 3 )"

A TextListField can be matched via include?

  aim_users = User.get { |u| u.im_methods.include?( 'aim' ) }
  # => "select * from users where user.im_methods like 'aim,%' or
        user.im_methods like '%,aim,%' or user.im_methods like '%,aim' or
        user.im_methods = 'aim'"

Text comparison: like

  fname_starts_with_a = User.get { |user| user.fname.like( /^a/ ) }
  # => "select * from users where fname like 'a%'"
  fname_ends_with_a = User.get { |user| user.fname.like( /a$/ ) }
  # => "select * from users where fname like '%a'"
  fname_contains_a = User.get { |user| user.fname.like( /a/ ) }
  # => "select * from users where fname like '%a%'"
  james_or_jones = User.get { |user| user.lname.like( /J..es/ ) }
  # => "select * from users where lname like 'J__es'"

Please note that although we’re using the Regexp operators here, these aren’t full-fledged regexps. Only ^, $, and . work for this.

Compound conditions: & and |

  invoices = Invoice.get { |inv|
    inv.hours.equals( 40 ) & inv.rate.equals( 50 )
  }
  # => "select * from invoices where (hours = 40 and rate = 50)"
  client99 = Client[99]
  invoices = Invoice.get { |inv|
    inv.hours.equals( 40 ) | inv.rate.equals( 50 ) |
      inv.client.equals( client99 )
  }
  # => "select * from invoices where (hours = 40 or rate = 50 or client = 99)"

Note that both compound operators can be nested:

  invoices = Invoice.get { |inv|
    inv.hours.equals( 40 ) &
      ( inv.rate.equals( 50 ) | inv.client.equals( client99 ) )
  }
  # => "select * from invoices where (hours = 40 and
  #     (rate = 50 or client = 99))"

Negation: not

  invoices = Invoice.get { |inv| inv.rate.equals( 50 ).not }
  # => "select * from invoices where rate != 50"

This can be used directly against boolean and nil comparisons, too.

  not_administrators = User.get { |u| u.administrator.not }
  # => "select * from users where administrator != 1"
  has_email = User.get { |u| u.email.nil?.not }
  # => "select * from users where email is not null"

Query caching via subset matching

Lafcadio caches every query, and optimizes based on a simple subset calculation. For example, if you run these statements:

  User.get { |u| u.lname.equals( 'Smith' ) }
  User.get { |u| u.lname.equals( 'Smith' ) & u.fname.like( /John/ ) }
  User.get { |u| u.lname.equals( 'Smith' ) & u.email.like( /hotmail/ ) }

Lafcadio can tell that the 2nd and 3rd queries are subsets of the first. So these three statements will result in one database call, for the first statement: The 2nd and 3rd statements will be handled entirely in Ruby. The result is less database calls with no extra work for the programmer.

Required files

delegate  

[Validate]