| name | grant-querying-and-scopes |
| description | Grant ORM query builder API including where clauses, operators, WhereChain, OR/NOT groups, scopes, aggregations, raw SQL, and Enumerable integration. |
| user-invocable | false |
Grant Querying and Scopes
Query Execution Model
Queries in Grant are lazy -- they build up a query description but do not execute until a terminal method is called:
# Building query (NOT executed yet)
query = User.where(active: true).order(:name)
# These are terminal methods that trigger execution:
users = query.select # Returns Array(User)
first = query.first # Returns User?
count = query.count # Returns Int64 (SQL COUNT)
exists = query.exists? # Returns Bool
Where Conditions
Basic WHERE
User.where(status: "active") # Equality
User.where(status: "active", verified: true) # Multiple conditions (AND)
User.where({status: "active", age: 25}) # Hash syntax
Operator Syntax
Post.where(:views, :gt, 100) # >
Post.where(:price, :lteq, 50.0) # <=
Post.where(:created_at, :gt, 7.days.ago)
Supported operators: :eq, :neq, :gt, :lt, :gteq, :lteq, :nlt, :ngt, :ltgt, :in, :nin, :like, :nlike
WhereChain Methods
Call where without arguments to access the WhereChain:
User.where.like(:email, "%@gmail.com") # LIKE
User.where.not_like(:name, "test%") # NOT LIKE
User.where.gt(:age, 18) # >
User.where.lt(:age, 65) # <
User.where.gteq(:score, 80) # >=
User.where.lteq(:price, 100) # <=
User.where.is_null(:deleted_at) # IS NULL
User.where.is_not_null(:verified_at) # IS NOT NULL
User.where.between(:age, 25..35) # >= 25 AND <= 35
User.where.not_in(:id, [1, 2, 3]) # NOT IN
User.where.not(:role, "guest") # != 'guest'
EXISTS / NOT EXISTS Subqueries
User.where.exists(
Post.where("posts.user_id = users.id")
)
User.where.not_exists(
Post.where("posts.user_id = users.id").where.gt(:created_at, 30.days.ago)
)
Raw SQL Conditions
Post.where("LOWER(title) LIKE ?", ["%crystal%"])
User.where("age * 2 > ?", [50])
# PostgreSQL: use $ placeholder
Post.where("metadata->>'key' = $", ["value"])
OR and NOT Conditions
OR Groups
User.where(role: "admin").or { |q| q.where(role: "moderator") }
# SQL: WHERE role = 'admin' OR role = 'moderator'
User.where(verified: true)
.or do |q|
q.where(role: "admin").where.gt(:level, 10)
end
# SQL: WHERE verified = true OR (role = 'admin' AND level > 10)
NOT Groups
User.not { |q| q.where(status: "banned") }
# SQL: WHERE NOT (status = 'banned')
User.where(country: "US")
.not { |q| q.where(status: "suspended") }
.where.gteq(:age, 18)
Ordering, Limiting, Grouping
Order
User.order(:name) # ASC default
User.order(created_at: :desc) # Explicit DESC
Post.order(featured: :desc, created_at: :desc) # Multiple
Limit and Offset
Post.limit(10)
Post.offset(40).limit(20) # Pagination: page 3, 20 per page
Distinct
User.distinct.select(:country)
User.distinct.count(:email)
Group By and Having
Order.group_by(:status)
Order.group_by(:customer_id)
.having("COUNT(*) > ?", [5])
.select("customer_id, COUNT(*) as order_count")
Joins
# Inner join via association
Post.joins(:author).where("users.active = ?", [true])
# Multiple joins
Comment.joins(:post, :user)
# Left join (include records without associations)
User.left_joins(:posts).where("posts.id IS NULL")
# Raw JOIN
Order.joins("INNER JOIN products ON products.id = orders.product_id AND products.active = true")
Eager Loading (N+1 Prevention)
posts = Post.includes(:author, :comments)
posts.each do |post|
puts post.author.name # No additional query
puts post.comments.size # No additional query
end
# Nested includes
User.includes(posts: [:comments, :tags])
Scopes
Defining Scopes
class Post < Grant::Base
scope :published, -> { where(published: true) }
scope :featured, -> { where(featured: true) }
scope :recent, -> { order(created_at: :desc) }
# Parameterized scopes
scope :by_author, ->(author_id : Int32) { where(author_id: author_id) }
scope :older_than, ->(date : Time) { where.lt(:created_at, date) }
# Complex scopes
scope :popular, -> {
where.gt(:views, 1000).where.gt(:likes, 100).order(views: :desc)
}
end
# Using scopes (chainable)
Post.published.recent.limit(10)
Post.by_author(current_user.id).featured
Default Scopes
class Product < Grant::Base
default_scope { where(active: true).where.is_null(:deleted_at) }
end
Product.all # Includes default scope
Product.unscoped.all # Bypasses default scope
Scope Composition
scope :active_admins, -> { active.admins }
def self.for_dashboard
active.verified.order(last_login: :desc)
end
Aggregations
User.count # Total count => Int64
User.where(active: true).count # Conditional count
User.distinct.count(:email) # Count distinct
Order.sum(:total) # => Float64
Order.where(status: "completed").sum(:total)
Product.average(:price) # => Float64? (nil if empty)
# Alias: Product.avg(:price)
Product.minimum(:price) # min
Product.maximum(:stock) # max
# Aliases: Product.min(:price), Product.max(:price)
Grouped Aggregations
User.group_by(:role).count
Order.group_by(:customer_id).sum(:total)
Query Merging and Duplication
# Merge two queries (WHERE = AND, ORDER/LIMIT from merged)
active = User.where(active: true)
admins = User.where(role: "admin")
active_admins = active.merge(admins)
# Duplicate a query to create variations
base = User.where(active: true).order(name: :asc)
recent = base.dup.where.gteq(:created_at, 7.days.ago)
verified = base.dup.where.is_not_null(:email_confirmed_at)
IN Subqueries
admin_ids = User.where(role: "admin").select(:id)
Post.where.in(:user_id, admin_ids)
# SQL: WHERE user_id IN (SELECT id FROM users WHERE role = 'admin')
Raw SQL
# Raw SQL with model instantiation
posts = Post.all("WHERE name LIKE ?", ["Joe%"])
post = Post.first("WHERE id = ?", [1])
# Custom SELECT with select_statement macro
class PostWithComments < Grant::Base
column id : Int64, primary: true
column title : String
column comment_count : Int32
select_statement <<-SQL
SELECT p.id, p.title, COUNT(c.id) as comment_count
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id, p.title
SQL
end
Enumerable Integration
Query::Builder includes Enumerable(Model), so all Crystal collection methods work directly on query chains:
# Transforming
titles = Post.where(published: true).map { |p| p.title }
emails = User.where(active: true).compact_map { |u| u.email }
# Filtering (in-memory, after SQL fetch)
featured = Post.where(published: true).select { |p| p.featured }
non_featured = Post.where(published: true).reject { |p| p.featured }
# Counting
Post.where(published: true).count # SQL COUNT => Int64
Post.where(published: true).count { |p| p.featured } # In-memory count
# Checking
Post.where(published: true).any? # SQL LIMIT 1
Post.where(published: true).any? { |p| p.title.includes?("Crystal") } # In-memory
# Aggregating
total = Order.where(status: "completed").sum { |o| o.total_amount }
oldest = User.where(active: true).min_by { |u| u.created_at }
role_counts = User.where(active: true).tally_by { |u| u.role }
# Partitioning
admins, others = User.where(active: true).partition { |u| u.role == "admin" }
# Converting
posts_array = Post.where(published: true).to_a
SQL-Optimized vs In-Memory
| Method | Without Block | With Block |
|---|
count / size | SQL COUNT => Int64 | In-memory iteration |
any? | SQL LIMIT 1 check | In-memory iteration |
select | Executes SQL query | In-memory filter |
map, reject, reduce, etc. | -- | In-memory iteration |
For large result sets, prefer SQL-level filtering with where clauses before using block-based methods.
Query Annotations
users = User.where(active: true).annotate("Dashboard#active_users").select
# SQL: /* Dashboard#active_users */ SELECT * FROM users WHERE active = true
Async Aggregations
count_result = User.async_count
sum_result = Order.async_sum(:total)
avg_result = Product.async_avg(:price)
total_users = count_result.await
total_revenue = sum_result.await
average_price = avg_result.await
