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 )
}
# => runs "select * from invoices where date = paid"

Numerical comparisons: lt, lte, gte, gt

lt, lte, gte, 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 ) }
# => runs "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 )
}
# => runs "select * from invoices where client <= 10"
client10 = Client[10]
for_1st_ten_clients = Invoice.get { |inv|
  inv.client.lte( client10 )
}
# => runs "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"