Lucky Logo

#Query Objects

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.

#Running 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

#Select shortcuts

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_user = UserQuery.first
last_user = UserQuery.last
specific_user = UserQuery.find(4)
all_users = UserQuery.all

#Lazy loading

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:

For example:

# The query is not yet run
query = UserQuery.new
query.name("Sally")
query.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

#Simple Selects

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 use COLUMNS.

#Select all

SELECT COLUMNS FROM users

users = UserQuery.new.all

#Select first

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 last

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?

#Select by primary key

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 / distinct on

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)

#Where Queries

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).

#A = B

Find rows where A is equal to B.

SELECT COLUMNS FROM users WHERE users.age = 54

UserQuery.new.age(54)

#A = B AND C = D

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!

#A != B

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 like eq, gt, lt, and in.

#A IS NULL / IS NOT NULL

Find rows where A 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

#A gt/lt B

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 > '2019-08-25 03:14:10 +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 < '2019-05-26 03:14:10 +00:00'

UserQuery.new.updated_at.lt(3.months.ago)

#A in / not in (B)

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"])

#A like / iLike B

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")

#Ordering

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

#Pagination

To do paginating, you’ll use a combination of limit and offset. You can also use this formula to help.

page = 1
per_page = 12

limit = per_page
offset = per_page * (page - 1)

UserQuery.new.limit(limit).offset(offset)

#Limit

SELECT COLUMNS FROM users LIMIT 1

UserQuery.new.limit(1)

#Offset

SELECT COLUMNS FROM users OFFSET 20

UserQuery.new.offset(20)

#Aggregates

#Count

SELECT COUNT(*) FROM users

total_count = UserQuery.new.select_count

#Avg / Sum

SELECT AVG(users.age) FROM users

UserQuery.new.age.select_average

SELECT SUM(users.age) FROM users

UserQuery.new.age.select_sum

#Min / Max

SELECT MIN(users.age) FROM users

UserQuery.new.age.select_min

SELECT MAX(users.age) FROM users

UserQuery.new.age.select_max

#Associations and Joins

When you have a model that is associated to another, your association is a method you can use to return those records.

#Associations

Each association defined on your model will have a method prefixed with where_ that takes a query from the association.

You can use this to help refine your association.

# SELECT COLUMNS FROM users 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 including has_one, and belongs_to take a block in the same format.

#Inner joins

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 an INNER JOIN, but you can also use inner_join_{{association_name}} for clarity

#Left joins

SELECT COLUMNS FROM users LEFT JOIN tasks ON users.id = tasks.user_id

UserQuery.new.left_join_tasks

#Right joins

SELECT COLUMNS FROM users RIGHT JOIN tasks ON users.id = tasks.user_id

UserQuery.new.right_join_tasks

#Full joins

SELECT COLUMNS FROM users FULL JOIN tasks ON users.id = tasks.user_id

UserQuery.new.full_join_tasks

#Preloading

In development and test environemnts Lucky requries 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 productionm and users will never see an error.

To preload, just call preload_{association name} on the query:

UserQuery.new.preload_tasks

#Customizing how associations are preloaded

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 users's tasks, and the tasks'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.

#without preloading

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!

#No results

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

#Named Scopes

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")

#Using with associations

class UserQuery < Uery::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

#Deleting Records

#Delete one

Deteling a single record is actually done on the model directly. Since each query returns an instance of the model, you can just call delete on that record.

user = UserQuery.find(4)

# DELETE FROM users WHERE users.id = 4
user.delete

#Bulk delete

If you need to bulk delete a group of records based on a where query, you can use delete at the end of your query. This returns the number of records deleted.

# DELETE FROM users WHERE banned_at IS NOT NULL
UserQuery.new.banned_at.is_not_nil.delete

#Truncate

If you need to delete every record in the entire table, you can use truncate.

TRUNCATE TABLE users

UserQuery.truncate

You can also truncate your entire database by calling truncate on your database class.

AppDatabase.truncate

This method is great for tests; horrible for production. Also note this method is not chainable.

#Complex Queries

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.

#Debugging Queries

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]