Tips and tricks

Squeel Tips and Tricks

Have a handy pattern you've adopted that showcases how Squeel can make your code more readable, or allow you to do things that you couldn't easily do with SQL strings? Put it here. To start things off, a sample from the README.

Arbitrary number of AND/ORed conditions against a single column

In standard ActiveRecord, to handle an arbitrary list of potential matches on a single database column, you might do:

Person.where((['name LIKE ?'] * names.size).join(' OR '), *names)

With Squeel, you can use the *_any predicates:

Person.where{name.like_any names}

For AND, just use the *_all predicates, instead.

Use __send__ to dynamically reference columns/associations

Squeel leaves a few standard Object methods available inside its DSL, and one of them is __send__. It's useful because you can create Stubs dynamically. For example, you can dynamically create conditions against lists of columns:

def self.containing_term_in_any_columns(term, *search_columns)
  where{search_columns.map {|col| __send__(col).matches "%#{term}%"}.inject(&:|)}
end

Inverting a set of conditions (or: how to NOT)

Given you have a set of conditions in Squeel:

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

To invert the set that this query will return, use the unary operator -:

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

Dynamic Queries

For simple AND queries use multiple calls to where:

q = Person.where{name =~ 'Ernie%'}
q = q.where{salary < my{max_salary}} if max_salary

For OR or nested queries:

where{
  q = (name =~ "Ernie%")
  q |= (salary < my{max_salary}) if my{max_salary}
  q
}

Alternatively:

where{[(name =~ "Ernie%"), (salary < my{max_salary} if my{max_salary})].compact.inject(&:|)}

.include / Preloading in complex queries

Preloading with .include generates joins to fetch the columns of the specified relation. This can make reasoning about joins more confusing.

To avoid this one can use the little-mentioned ActiveRelation.preload method instead, which performs preloading through separate queries.