The query macro and where clause combine to give you full control over your query.
Where is using a QueryBuilder that allows you to chain where clauses together to build up a complete query.
posts = Post.where(published: true, author_id: User.first!.id)
It supports different operators:
Post.where(:created_at, :gt, Time.now - 7.days)
Supported operators are :eq, :gteq, :lteq, :neq, :gt, :lt, :nlt, :ngt, :ltgt, :in, :nin, :like, :nlike
Order is using the QueryBuilder and supports providing an ORDER BY clause:
Post.order(created_at: :desc, title: :asc)
Limit is using the QueryBuilder and provides the ability to limit the number of tuples returned:
Offset is using the QueryBuilder and provides the ability to offset the results. This is used for pagination:
All is not using the QueryBuilder. It allows you to directly query the database using SQL.
When using the
all method, the selected fields will match the fields specified in the model unless the
select macro was used to customize the SELECT.
Always pass in parameters to avoid SQL Injection. Use a
? in your query as placeholder. Checkout the Crystal DB Driver for documentation of the drivers.
Here are some examples:
posts = Post.all("WHERE name LIKE ?", ["Joe%"])if postsposts.each do |post|puts post.nameendend# ORDER BY Exampleposts = Post.all("ORDER BY created_at DESC")# JOIN Exampleposts = Post.all("JOIN comments c ON c.post_id = post.idWHERE c.name = ?ORDER BY post.created_at DESC",["Joe"])
select_statement macro allows you to customize the entire query, including the SELECT portion. This shouldn't be necessary in most cases, but allows you to craft more complex (i.e. cross-table) queries if needed:
class CustomView < Granite:Baseadapter pgfield articlebody : Stringfield commentbody : Stringselect_statement <<-SQLSELECT articles.articlebody, comments.commentbodyFROM articlesJOIN commentsON comments.articleid = articles.idSQLend
You can combine this with an argument to
first for maximum flexibility:
results = CustomView.all("WHERE articles.author = ?", ["Noah"])