Chapter 6. Querying

Table of Contents

Building and accessing queries
Query inference operators
Numerical comparisons: lt, lte, gte, gt
Equality: equals
Inclusion: in and include?
Text comparison: like
Compound conditions: & and |
Negation: not
Hands-on query construction
Query caching via subset matching
Eager loading

When using Lafcadio, you can pass in a block to DomainObject.get in order to 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

Most commonly, you'll access the query language through DomainObject.get, which builds a query and runs it immediately.

# runs "select * from users where first_name = 'Jane'"
janes = User.get { |u| u.first_name.equals( 'Jane' ) }

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. This can come in handy if you're doing complex query construction, for example if you're dealing with an advanced search with many possible search options.

qry = Query.infer( User ) { |u| u.last_name.equals( 'Hwang' ) }
qry.to_sql # => "select * from users where users.last_name = 'Hwang'"
qry = qry.and { |u| u.first_name.equals( 'Francis' ) }
qry.to_sql
# => "select * from users where (users.last_name = 'Hwang' and users.first_name = 'Francis')"
qry.limit = 0..5
qry.to_sql
# => "select * from users where (users.last_name = 'Hwang' and users.first_name = '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"