squeel

http://erniemiller.org/projects/squeel/

* KeyPaths
Person.joins(:articles => {:comments => :person}) => Person.joins(articles.comments.person)
-> they can also exist in the context of a hash

* Predicates

SQL	Predication	Operator	Alias
=	eq	        ==	
!=	not_eq		!=	
LIKE	matches		=~		like
NOT LIKEdoes_not_match	!~		not_like
<	lt		<	
<=	lteq		<=		lte
>	gt		>	
>=	gteq		>=		gte
IN	in		>>	
NOT IN	not_in		<<	

usage:
Person.where(:name => 'Joe Blow')
Person.where{{name => 'Joe Blow'}}
Person.where{{name.eq => 'Joe Blow'}}
Person.where{name.eq 'Joe Blow'}
Person.where{name == 'Joe Blow'}
-> all the same

Person.where{(name =~ 'Ernie%') & (salary < 50000) | (name =~ 'Joe%') & (salary > 100000)}

* Predicate aliases
Squeel.configure do |config|
  config.alias_predicate :is_less_than, :lt
end
 
Person.where{salary.is_less_than 50000}.to_sql

* Compound Conditions
names = ['Ernie%', 'Joe%', 'Mary%']
Person.where('name LIKE ? OR name LIKE ? OR name LIKE ?', *names)     =>    Person.where{name.like_any names}

-> you can add _any or _all to any predicate method

* Subqueries
You can supply an ActiveRecord::Relation as a value for a predicate in order to use a subquery. So, for example:

awesome_people = Person.where{awesome == true}
Article.where{author_id.in(awesome_people.select{id})}

* Joins
Person.joins{articles.outer}
=> SELECT "people".* FROM "people"
   LEFT OUTER JOIN "articles" ON "articles"."person_id" = "people"."id"
Note.joins{notable(Person).outer}
=> SELECT "notes".* FROM "notes"
   LEFT OUTER JOIN "people"
     ON "people"."id" = "notes"."notable_id"
     AND "notes"."notable_type" = 'Person'

Note.joins{notable(Person).articles}
=> SELECT "notes".* FROM "notes"
   INNER JOIN "people" ON "people"."id" = "notes"."notable_id"
     AND "notes"."notable_type" = 'Person'
   INNER JOIN "articles" ON "articles"."person_id" = "people"."id"

* Functions
-> they can be called like a ruby method
Person.select{coalesce(name, '<no name given>')}
=> SELECT coalesce("people"."name", '<no name given>') FROM "people"

* SQL OPERATORS
...