Querying
The query macro and where clause combine to give you full control over your query.
Where
Where is using a QueryBuilder that allows you to chain where clauses together to build up a complete query.
1
posts = Post.where(published: true, author_id: User.first!.id)
Copied!
It supports different operators:
1
Post.where(:created_at, :gt, Time.local - 7.days)
Copied!
Supported operators are :eq, :gteq, :lteq, :neq, :gt, :lt, :nlt, :ngt, :ltgt, :in, :nin, :like, :nlike
Alternatively,
#where, #and, and #or accept a raw SQL clause, with an optional placeholder (? for MySQL/SQLite, $ for Postgres) to avoid SQL Injection.1
# Example using Postgres adapter
2
Post.where(:created_at, :gt, Time.local - 7.days)
3
.where("LOWER(author_name) = quot;, name)
4
.where("tags @> '{"Journal", "Book"}') # PG's array contains operator
Copied!
This is useful for building more sophisticated queries, including queries dependent on database specific features not supported by the operators above. However, clauses built with this method are not validated.
Order
Order is using the QueryBuilder and supports providing an ORDER BY clause:
1
Post.order(:created_at)
Copied!
Direction
1
Post.order(updated_at: :desc)
Copied!
Multiple fields
1
Post.order([:created_at, :title])
Copied!
With direction
1
Post.order(created_at: :desc, title: :asc)
Copied!
Group By
Group is using the QueryBuilder and supports providing an GROUP BY clause:
1
posts = Post.group_by(:published)
Copied!
Multiple fields
1
Post.group_by([:published, :author_id])
Copied!
Limit
Limit is using the QueryBuilder and provides the ability to limit the number of tuples returned:
1
Post.limit(50)
Copied!
Offset
Offset is using the QueryBuilder and provides the ability to offset the results. This is used for pagination:
1
Post.offset(100).limit(50)
Copied!
All
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:
1
posts = Post.all("WHERE name LIKE ?", ["Joe%"])
2
if posts
3
posts.each do |post|
4
puts post.name
5
end
6
end
7
8
# ORDER BY Example
9
posts = Post.all("ORDER BY created_at DESC")
10
11
# JOIN Example
12
posts = Post.all("JOIN comments c ON c.post_id = post.id
13
WHERE c.name = ?
14
ORDER BY post.created_at DESC",
15
["Joe"])
Copied!
Customizing SELECT
The
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:1
class CustomView < Granite::Base
2
connection pg
3
4
column id : Int64, primary: true
5
column articlebody : String
6
column commentbody : String
7
8
select_statement <<-SQL
9
SELECT articles.articlebody, comments.commentbody
10
FROM articles
11
JOIN comments
12
ON comments.articleid = articles.id
13
SQL
14
end
Copied!
You can combine this with an argument to
all or first for maximum flexibility:1
results = CustomView.all("WHERE articles.author = ?", ["Noah"])
Copied!
Exists?
The
exists? class method returns true if a record exists in the table that matches the provided id or criteria, otherwise false.If passed a
Number or String, it will attempt to find a record with that primary key. If passed a Hash or NamedTuple, it will find the record that matches that criteria, similar to find_by.1
# Assume a model named Post with a title field
2
post = Post.new(title: "My Post")
3
post.save
4
post.id # => 1
5
6
Post.exists? 1 # => true
7
Post.exists? {"id" => 1, :title => "My Post"} # => true
8
Post.exists? {id: 1, title: "Some Post"} # => false
Copied!
The
exists? method can also be used with the query builder.1
Post.where(published: true, author_id: User.first!.id).exists?
2
Post.where(:created_at, :gt, Time.local - 7.days).exists?
Copied!
Last modified 1yr ago