When you generate a model,
Avram will create a Query class for you in ./src/queries/{model_name}_query.cr
.
This class will inherit from {ModelName}::BaseQuery
. (e.g. with a User
model you get
a User::BaseQuery
class).
# src/queries/user_query.cr
class UserQuery < User::BaseQuery
end
Each column defined on the model will also generate methods for the query object to use. This gives us a type-safe way to query on each column. All of the query methods are chainable for both simple and more complex queries.
When you run any query, Avram will return an instance, array of instances, nil
, or
raise an exception (e.g. Avram::RecordNotFoundError
).
For our examples, we will use this User
model.
class User < BaseModel
table :users do
# `id`, `created_at`, and `updated_at` are predefined for us
column name : String
column age : Int32
column admin : Bool
has_many tasks : Task
end
end
By default, all query objects include the Enumerable(T)
module, which means methods
like each
, and map
may be used.
All query methods are called on the instance of the query object, but there’s also a few class methods for doing quick finds.
first
- Returns the first record. Raise Avram::RecordNotFoundError
if no record is found.first?
- Returns the first record. Returns nil
if no record is found.find(id)
- Returns the record with the primary key id
. Raise Avram::RecordNotFoundError
if no record is found.last
- Returns the last record. Raise Avram::RecordNotFoundError
if no record is found.last?
- Returns the last record. Returns nil
if no record is found.first_user = UserQuery.first
last_user = UserQuery.last
specific_user = UserQuery.find(4)
all_users = UserQuery.new
The
find
method requires aprimary_key
.view
models that need this method will need to implement it.
The query does not actually hit the database until a method is called to fetch a result or iterate over results.
The most common methods are:
first
find
each
For example:
# The query is not yet run
query = UserQuery.new.name("Sally").age(30)
# The query will run once `each` is called
# Results are not cached so a request will be made every time you call `each`
query.each do |user|
pp user.name
end
Queries are immutable. Whenever a method is called on a query, it returns a new copy of itself with the condition added. The query the method was called on will not be changed.
query = UserQuery.new.name("Wendy")
new_query = query.age(40)
query.to_sql #=> SELECT COLUMNS FROM users WHERE users.name = 'Wendy';
new_query.to_sql #=> SELECT COLUMNS FROM users WHERE users.name = 'Wendy' AND users.age = 40;
When doing a
SELECT
, Avram will select all of the columns individually (i.e.users.id, users.created_at, users.updated_at, users.name, users.age, users.admin
) as opposed to*
. However, for brevity, we will useCOLUMNS
.
SELECT COLUMNS FROM users
users = UserQuery.new
SELECT COLUMNS FROM users ORDER BY users.id ASC LIMIT 1
# raise Avram::RecordNotFound if nil
user = UserQuery.new.first
# returns nil if not found
user = UserQuery.new.first?
SELECT COLUMNS FROM users ORDER BY users.id DESC LIMIT 1
# raise Avram::RecordNotFound if nil
user = UserQuery.new.last
# returns nil if not found
user = UserQuery.new.last?
Selecting the user with id = 3
.
SELECT COLUMNS FROM users WHERE users.id = 3 LIMIT 1
# raise Avram::RecordNotFound if nil
user = UserQuery.new.find(3)
SELECT DISTINCT COLUMNS FROM users
UserQuery.new.distinct
Select distinct rows based on the name
column SELECT DISTINCT ON (users.name) FROM users
UserQuery.new.distinct_on(&.name)
The WHERE
clauses are the most common used in SQL. Each of the columns generated by the model
will give you a method for running a WHERE
on that column. (e.g. the age
can be queried using
age(30)
which produces the SQL WHERE age = 30
).
Find rows where A
is equal to B
.
SELECT COLUMNS FROM users WHERE users.age = 54
UserQuery.new.age(54)
In some cases, the value you pass in may be nilable. If you pass in a nil
value,
Avram will raise an exception.
For these cases, you would use the nilable_eq
method.
UserQuery.new.age.nilable_eq(potential_age_or_nil_value)
Find rows where A
is equal to B
and C
is equal to D
.
SELECT COLUMNS FROM users WHERE users.age = 43 AND users.admin = true
UserQuery.new.age(43).admin(true)
All query methods are chainable!
Find rows where A
is equal to B
or A
is equal to C
.
SELECT COLUMNS FROM users WHERE users.name = 'Alfred' OR users.name = 'Bruce'
UserQuery.new.name("Alfred").or(&.name("Bruce"))
OR
queries can become quite complex. If you need to wrap conditions, you can use
the where(&)
method. This will take a block, and wrap any query chain inside with
parenthesis ()
.
SELECT COLUMNS FROM users WHERE users.likes_bats = true OR (users.first_name = 'Kate' AND users.last_name = 'Kane')
UserQuery.new.likes_bats(true).or do |or|
or.where do |where|
where.first_name("Kate").last_name("Kane"))
end
end
Find rows where A
is not equal to B
.
SELECT COLUMNS FROM users WHERE users.name != 'Billy'
UserQuery.new.name.not.eq("Billy")
The
not
method can be used to negate other methods likeeq
,gt
,lt
, andin
.
Find rows where A
is nil
using is_nil.
SELECT COLUMNS FROM users WHERE users.name IS NULL
UserQuery.new.name.is_nil
Find rows where A
is not nil
.
SELECT COLUMNS FROM users WHERE users.name IS NOT NULL
UserQuery.new.name.is_not_nil
Find rows where (casting A
to LOWER/UPPER) is equal to B
SELECT COLUMNS FROM users WHERE LOWER(users.name) = 'gar'
UserQuery.new.name.lower.eq("gar")
SELECT COLUMNS FROM users WHERE UPPER(users.name) = 'GAR'
UserQuery.new.name.upper.eq("GAR")
Find rows where (trimming blankspace from A
) is equal to B
SELECT COLUMNS FROM posts WHERE TRIM(posts.title) = 'First Post'
PostQuery.new.title.trim.eq("First Post")
Find rows where (casting A
to a DATE()) is equal to B
SELECT COLUMNS FROM schedules WHERE DATE(schedules.starts_at) = '2017-03-11'
ScheduleQuery.new.starts_at.as_date.eq(Time.utc.to_s("%F"))
The dates are compared with Strings, so a date formatted String object must be passed in.
Find rows where the length of string A
is equal to B
SELECT COLUMNS FROM users WHERE LENGTH(users.username) = 2
UserQuery.new.username.length.eq(2)
Find rows where the reverse of string A
is equal to B
SELECT COLUMNS FROM users WHERE REVERSE(users.username) = 'tacocat'
UserQuery.new.username.reverse.eq("tacocat")
Find rows where the ABS of the number A
is equal to B
SELECT COLUMNS FROM reports WHERE ABS(reports.amount) = 400
ReportQuery.new.amount.abs.eq(400)
Find rows where the FLOOR number A
is equal to B
SELECT COLUMNS FROM reports WHERE FLOOR(reports.rating) = 5
ReportQuery.new.rating.floor.eq(5)
Find rows where the CEIL number A
is equal to B
SELECT COLUMNS FROM reports WHERE CEIL(reports.rating) = 5
ReportQuery.new.rating.ceil.eq(5)
Find rows where A
is greater than or equal to (>=) B
.
WHERE users.age >= 21
UserQuery.new.age.gte(21)
Find rows where A
is greater than B
.
WHERE users.created_at > '2024-12-09 10:35:52 +00:00'
UserQuery.new.created_at.gt(1.day.ago)
Find rows where A
is less than or equal to B
.
WHERE users.age <= 12
UserQuery.new.age.lte(12)
Find rows where A
is less than B
.
WHERE users.updated_at < '2024-09-10 10:35:52 +00:00'
UserQuery.new.updated_at.lt(3.months.ago)
Find rows where A
is between C
and D
.
WHERE users.updated_at >= '2024-12-07 10:35:52 +00:00' AND users.updated_at <= '2024-12-09 10:35:52 +00:00'
UserQuery.new.updated_at.between(3.days.ago, 1.day.ago)
Find rows where A
is in the list B
.
WHERE users.name IN ('Bill', 'John')
UserQuery.new.name.in(["Bill", "John"])
Find rows where A
is not in the list B
.
WHERE users.name NOT IN ('Sally', 'Jenny')
UserQuery.new.name.not.in(["Sally", "Jenny"])
Find rows where A
is in the array B
WHERE 'Gold' = ANY (users.badges)
UserQuery.new.badges.includes("Gold")
Find rows where A
is like (begins with) B
.
WHERE users.name LIKE 'John%'
UserQuery.new.name.like("John%")
WHERE users.name ILIKE 'jim'
UserQuery.new.name.ilike("jim")
class User < BaseModel
enum Role
Basic
Admin
end
enum Color
Red
Blue
Green
Yellow
end
column role : User::Role
column colors : Array(User::Color)
end
UserQuery.new.role(User::Role::Admin)
UserQuery.new.colors.includes(User::Color::Green)
When you only need to know if there’s any records that match your query
you can use the any?
method.
# returns `true` if there's at least 1 record
UserQuery.new.any?
The opposite is none?
which will return true
if there’s no records that
match your query.
# returns `true` if there's no records
UserQuery.new.none?
Postgres supports several time units with the EXTRACT()
function. Avram supports all of these:
Century
, Day
, Decade
, Dow
, Doy
, Epoch
, Hour
, Isodow
, Isoyear
, Julian
, Microseconds
, Millennium
, Milliseconds
, Minute
, Month
, Quarter
, Second
, Timezone
, TimezoneHour
, TimezoneMinute
, Week
, Year
.
Find rows that were created on a July 4th:
WHERE EXTRACT(month FROM created_at) = 7 AND EXTRACT(day FROM created_at) = 4
UserQuery.new.created_at.extract_month.eq(7).created_at.extract_day.eq(4)
For each supported time unit, you can either pass it as an enum value to
extract
(i.e.extract(Avram::ChronoUnits::Year)
) or append the lowercase version toextract_
(i.e.extract_julian
)
These are special query methods designed for quering against JSON::Any
/ jsonb
type columns.
Find rows where the jsonb field A
has the key B
.
SELECT COLUMNS FROM users WHERE users.preferences ? 'theme'
UserQuery.new.preferences.has_key("theme")
Find rows where the jsonb field A
has any of the keys B
or C
SELECT COLUMNS FROM users WHERE users.preferences ?| '{"theme", "style"}'
UserQuery.new.preferences.has_any_keys(["theme", "style"])
Find rows where the jsonb field A
has all of the keys B
and C
SELECT COLUMNS FROM users WHERE users.preferences ?& '{"theme", "size"}'
UserQuery.new.preferences.has_all_keys(["theme", "size"])
Find rows where the jsonb field A
includes the jsonb data B
SELECT COLUMNS FROM users WHERE users.preferences @> '{"theme":"dark"}'
UserQuery.new.preferences.includes({theme: "dark"})
Find rows where the jsonb field A
is in the jsonb data B
SELECT COLUMNS FROM users WHERE users.preferences <@ '{"theme":"dark","size":"large","audio":"on","transparentBg":true}'
UserQuery.new.preferences.in({theme: "dark", size: "large", audio: "on", transparentBg: true})
The
JSON::Any
includes
andin
methods accept any structure that responds toto_json
For more complex queries, and as an escape hatch to support queries beyond Avram’s support, you can pass raw strings to
where
and use the ?
character as a value placeholder. The arguments after the first are the values to be replaced in the query.
UserQuery.new.where("preferences->>'theme' = ? AND preferences->>'version' = ?", "rose", "v2")
Due to the placeholder character being a
?
, some jsonb operations won’t work here. (e.g.jsonb ? text
)
Return rows ordered by the age
column in descending (or ascending) order.
SELECT COLUMNS FROM users ORDER BY users.age DESC
UserQuery.new.age.desc_order
# or for asc order
UserQuery.new.age.asc_order
Sort records placing NULL values first or last
SELECT COLUMNS FROM users ORDER BY users.age DESC NULLS FIRST
UserQuery.new.age.desc_order(:nulls_first)
# Also sort with NULLS LAST
UserQuery.new.age.desc_order(:nulls_last)
Return rows in a random order.
SELECT COLUMNS FROM users ORDER BY RANDOM()
UserQuery.new.random_order
Appending a specific order after a random order will use that specific order. (e.g.
UserQuery.new.random_order.username.desc_order
will order by username descending)
Return rows grouped by the age
column.
SELECT COLUMNS FROM users GROUP BY users.age, users.id
UserQuery.new.group(&.age).group(&.id)
Using the Postgres GROUP BY function can be confusing. Read more on postgres aggregate functions.
This section has been moved to its own pagination guide.
SELECT COUNT(*) FROM users
# This will return an Int64.
# The value will be 0 if there are no records.
UserQuery.new.select_count
SELECT AVG(users.age) FROM users
# This will return a Float64 | Nil.
# The value will be nil if there are no records.
UserQuery.new.age.select_average
# This will return a Float64.
# The value will be 0 if there are no records.
UserQuery.new.age.select_average!
SELECT SUM(users.age) FROM users
# Returns an Int64 for integer columns, or a Float64 for float columns
# Returns nil if there are no records
UserQuery.new.age.select_sum
# Returns an Int64 for integer columns, or a Float64 for float columns
# Returns 0 if there are no records
UserQuery.new.age.select_sum!
SELECT MIN(users.age) FROM users
UserQuery.new.age.select_min
SELECT MAX(users.age) FROM users
UserQuery.new.age.select_max
select_min
and select_max
will return a union type of the column and Nil
.
For example, if the column type is an Int32
the return type will be Int32 | Nil
.
When your query is grouped, and you want to return the count of each group,
you can use the group_count
method.
# {[32, "Daniel"] => 1, [32, "Taylor"] => 2, [44, "Shakira"] => 1}
UserQuery.new.group(&.age).group(&.name).group_count
When you have a model that is associated to another, your association is a method you can use to return those records.
Each association defined on your model will have a method prefixed with where_
that takes a
query from the association. This method will add an inner join for you.
You can use this to help refine your association.
# SELECT COLUMNS FROM users INNER JOIN tasks ON users.id = tasks.user_id WHERE tasks.title = 'Clean up notes'
UserQuery.new.where_tasks(TaskQuery.new.title("Clean up notes"))
This will return all users who have a task with a title “Clean up notes”.
You can continue to scope this on both the User
and Task
side.
This example shows the
has_many
association, but all associations includinghas_one
, andbelongs_to
use the same format.
By default, using the where_
methods will use INNER JOIN
, but you have the option
to configure this by passing false
to the auto_inner_join
argument, and specifying
a different join method.
UserQuery.new
.left_join_tasks
.where_tasks(
TaskQuery.new.title("Clean up notes"),
auto_inner_join: false)
SELECT COLUMNS FROM users INNER JOIN tasks ON users.id = tasks.user_id
UserQuery.new.join_tasks
By default the
join_{{association_name}}
method will be anINNER JOIN
, but you can also useinner_join_{{association_name}}
for clarity
SELECT COLUMNS FROM users LEFT JOIN tasks ON users.id = tasks.user_id
UserQuery.new.left_join_tasks
SELECT COLUMNS FROM users RIGHT JOIN tasks ON users.id = tasks.user_id
UserQuery.new.right_join_tasks
SELECT COLUMNS FROM users FULL JOIN tasks ON users.id = tasks.user_id
UserQuery.new.full_join_tasks
In development and test environments Lucky requires preloading associations. If you forget to preload an association, a runtime error will be raised when you try to access it. In production, the association will be lazy loaded so that users do not see errors.
This solution means you will find N+1 queries as you develop instead of in production and users will never see an error.
To preload, just call preload_{association name}
on the query:
UserQuery.new.preload_tasks
Sometimes you want to order preloads, or add where clauses. To do this, use the
preload_{{association_name }}
method on the query, and pass a query object for the association.
UserQuery.new.preload_tasks(TaskQuery.new.completed(false))
This is also how you would do nested preloads:
# Preload the user's tasks, and the task's author
UserQuery.new.preload_tasks(TaskQuery.new.preload_author)
Note that you can only pass query objects to
preload
if the association is defined, otherwise you will get a type error.
There are situations where you have an existing record and it does not have the associations preloaded that are needed. Instead of loading the association separately, you can add an association after the fact, instead.
user = UserQuery.find(user_id)
# Preload the user's tasks
user_with_tasks = UserQuery.preload_tasks(user)
It can even be used to load associations on a collection of records.
users = UserQuery.new.age(30)
# Preload the users' tasks
users_with_tasks = UserQuery.preload_tasks(users)
Sometimes you have a single model and don’t need to preload items. Or maybe you can’t preload because the
model record is already loaded. In those cases you can use the association name with !
:
task = TaskQuery.first
# Returns the associated author and does not trigger a preload error
task.user!
Reloading a model can be useful when you’ve loaded a model, but then there is a change to the data.
author = AuthorQuery.find(5)
# Let's say the Author's profile picture is hidden
author.hide_avatar #=> true
# If this database value is updated...
SaveAuthor.update!(author, hide_avatar: false)
# We can reload to get the new value
author.reload.hide_avatar #=> false
When calling the reload
method on the model, the original
instance is not updated.
# The new value grabbed from the reloaded model
author.reload.hide_avatar #=> false
# The original value is still in place
author.hide_avatar #=> true
The
reload
method requires aprimary_key
.view
models that need this method will need to implement it.
You can also use the reload
method to preload associations. For example, if you
have a post, and want to preload comments, you can use reload
with a block.
# `post` is a recently updated record.
# We want to get all of the author names that commented on this `post`.
# This is not preloaded, and can lead to performance issues
post.comments.map(&.author.name)
# Preload the comments and authors for better performance
post.reload(&.preload_comments(CommentQuery.new.preload_authors))
.comments.map(&.author.name)
Read up on preloading associations for more information.
Avram gives you a none
method to return no results. This can be helpful when under
certain conditions you want the results to be empty.
UserQuery.new.none
This method does not return an empty array immediately. You can still chain other query methods, but it will always return no records. For example:
UserQuery.new.none.first
will never return a result
Chaining multiple query methods can be hard to read, tedious, and error prone. If you are making a complex query more than once, or want to give a query a label, named scopes are a great alternative.
class UserQuery < User::BaseQuery
def adults
age.gte(18)
end
def search(name)
ilike("#{name}%")
end
end
UserQuery.new.adults.search("Sal")
class UserQuery < User::BaseQuery
def recently_completed_admin_tasks
task_query = TaskQuery.new.completed(true).updated_at.gte(1.day.ago)
admin(true).where_tasks(task_query)
end
end
# Then to use it
UserQuery.new.recently_completed_admin_tasks
When adding an associated query (like task_query
), Avram will handle adding the join
for you. By default, this is an INNER JOIN
, but if you need to customize that, you can
set the auto_inner_join
option to false
.
def recently_completed_admin_tasks
task_query = TaskQuery.new.completed(true).updated_at.gte(1.day.ago)
# Tell the `where_tasks` to skip adding the `inner_join` so we can
# use the `left_join_tasks` instead.
admin(true)
.left_join_tasks
.where_tasks(task_query, auto_inner_join: false)
end
You can also set defaults for your query objects which could be an ordering, named scope, or whatever you may need.
class AdminQuery < User::BaseQuery
def initialize
defaults &.admin(true).name.asc_order
end
end
# Will always query WHERE admin = true ORDER BY name ASC
AdminQuery.new
The
defaults
method is private scoped. It’s only meant to be used in theinitialize
method of your class.
If you need to remove parts of the SQL query after the query has been built, Avram gives you a few reset methods for that.
The reset_where
method takes a block where you call the name of the column you want to remove
from your query.
# SELECT * FROM users WHERE name = 'Billy' AND signed_up < '2 days ago'
user_query = UserQuery.new.name("Billy").signed_up.lt(2.days.ago)
# The `name = 'Billy'` is removed
# SELECT * FROM users WHERE signed_up < '2 days ago'
user_query.reset_where(&.name)
user_query = UserQuery.new.age.desc_order
# This will remove the `ORDER BY age DESC`
user_query.reset_order
user_query = UserQuery.new.limit(10)
# This will remove the `LIMIT 10`
user_query.reset_limit
user_query = UserQuery.new.offset(25)
# This will remove the `OFFSET 25`
user_query.reset_offset
If you need more complex queries that Avram may not support, you can run raw SQL.
Avram is designed to be type-safe. You should use caution when using the non type-safe methods, or raw SQL.
Sometimes you may need to double check that the query you wrote outputs the SQL you expect.
To do this, you can use the to_sql
method which will return an array with the query, and args.
UserQuery.new
.name("Stan")
.age(45)
.limit(1)
.to_sql #=> ["SELECT COLUMNS FROM users WHERE users.name = $1 AND users.age = $2 LIMIT $3", "Stan", 45, 1]
You can also use the to_prepared_sql
method to combine your query and args. This is helpful when
you need to copy and paste your query in to psql
directly during development when working with more complex queries.
UserQuery.new
.where_posts(PostQuery.new.published(true).tags(["crystal", "lucky"]))
.limit(10)
.to_prepared_sql
#=> "SELECT COLUMNS FROM users INNER JOIN posts ON users.id = posts.user_id WHERE posts.tags = '{"crystal", "lucky"}' LIMIT 10"
If you’d prefer to see every query that is being run in your server logs, you can configure Avram’s log level like this:
# This is often set in `config/log.cr`
Avram::QueryLog.dexter.configure(:info)