Setting up a model

Let’s create a User model with lucky gen.model User and then add some columns. The name is a String and the age is an Int32?. The age column allows null because the type is nilable (designated by the ?).

Note: we’ll assume we ran a migration that created a name and age column.

# src/models/user.cr
class User < BaseModel
  table :users do
    column name : String
    column age : Int32?
  end
end

You can add a ? to the end of any type to mark it as nilable.

Column types

You can use the following types in your columns.

Querying the database

When you define a model, Lucky creates a query class that you can use to get users from the database. The class is always called {ModelName}::BaseQuery .

To use it, let’s create a query object that inherits from the one generated by Lucky.

# src/queries/user_query.cr
class UserQuery < User::BaseQuery
end

User::BaseQuery has methods for querying and retrieving users such as:

It also has methods for ordering and adding where clauses. These are generated based on the columns defined in your model.

For the model we defined Lucky would generate:

Distinct on

UserQuery.new.distinct_on(&.name)

Where clauses

You can use the methods generated by Lucky to create where clauses.

# SELECT * FROM users where name='Paul';
UserQuery.new.name("Paul")

# SELECT * FROM users where age=28;
UserQuery.new.age(28)

Parsing query params

Lucky will accept the type defined in the model (e.g. Int32 for age) or a String. If you pass it a String then Lucky will attempt to parse it and use the type defined for the column.

For example:

# Lucky parses the string and use an Int32 in the query
UserQuery.new.age("30")

# Won't accept other types
# Fails at compile time because you can't pass a Float64
UserQuery.new.age(30.0)

Note that this will change in the future but this is the current behavior. In the future only the defined type (Int32 in this example) will work. You will then need to manually parse it. Don’t worry though, Lucky will add some helpers to actions and the model to make this easy and maintainable.

Ordering results

UserQuery.new.age.desc_order
# or
UserQuery.new.age.asc_order

Chaining methods

You can chain where clauses and most other query methods.

UserQuery.new.age.gt(28).age.desc_order

Running the query

The query does not actually hit the database until a method is called to fetch a result or iterate over results.

Some of 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

Finding by ID

id = 1
UserQuery.new.find(id)

# You can also chain methods before calling find, first, etc.
UserQuery.new.age(30).find(1)

Ensuring no results are returned

UserQuery.new.none

This can be helpful when under certain conditions you want the resultd to be empty.

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

Shorthand methods

There are a few methods that are used so often that we’ve added shortcut methods:

id = 1
UserQuery.find(id)
UserQuery.first
UserQuery.last

Type specific query methods

The previous section mostly focused on simple equality queries, but you can also do more advanced queries depending on the column type.

All types

String

Int32 and Time

Examples

UserQuery.new.age.gte(18).name.lower.is("sally")

Deleting a record

user = UserQuery.new.find(id)
user.delete

Using scopes

Generally it’s best to name your query methods so they are easy to understand and reuse across your app. In Lucky, this is done by extracting methods on the query object.

Here’s an example:

class UserQuery < User::BaseQuery
  def adults
    age.gte(18)
  end

  def search(name)
    ilike("#{name}%")
  end
end

UserQuery.new.adults.search("Sal")

Using associations

Right now you can define has_many, has_one, and belongs_to associations.

# Require the models we want to associate. If we don't we may get an
# "Undefined constant" error because Lucky didn't load the associated models yet
require "./supervisor"
require "./task"
require "./company"

class User < BaseModel
  table users do
    has_one supervisor : Supervisor
    has_many tasks : Task
    belongs_to company : Company
  end
end

# Will return the company associated with the User
UserQuery.new.preload_company.find(1).company

Making associations optional

Sometimes associations are not required. To do that add a ? to the end of the type.

belongs_to company : Company?

Make sure to make the column nilable in your migration as well: belongs_to Company?

Has many through (many-to-many)

Let’s say we want to have many tags that can belong to any number of posts.

Here are the models:

# This is what will join the posts and tags together
class Tagging < BaseModel
  table :taggings do
    belongs_to tag : Tag
    belongs_to post : Post
  end
end

class Tag < BaseModel
  table :tags do
    column name : String
    has_many taggings : Tagging
    has_many posts : Post, through: :taggings
  end
end

class Post < BaseModel
  table :posts do
    column title : String
    has_many taggings : Tagging
    has_many tags : Tag, through: :taggings
  end
end

The associations must be declared on both ends (the Post and the Tag in this example), otherwise you will get a compile time error

Preloading associations

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 in development and test, and users will never see an error in production.

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

PostQuery.new.preload_comments

Customizing how associations are preloaded

Sometimes you want to order preloads, or add where clauses. To do this, use the preload method on the query and pass a query object for the association.

PostQuery.new.preload(CommentQuery.new.published(true))

This is also how you would do nested preloads:

# Preload the post's comments, and the comment's author
PostQuery.new.preload(CommentQuery.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.

Loading associations 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 !:

post = PostQuery.first
# Returns the associated author and does not trigger a preload error
post.author!

Joining and querying associations

Let’s say you have a Post model with has many comments: Comment, the Comment model has a rating column that is an integer, and the highest rating is 5 and lowest is 1.

If we want to get all posts that have at least 1 highly rated comment, we could do something like this:

class PostQuery < Post::BaseQuery
  def with_highly_rated_comments
    # Join the comments
    join_comments

    # Get posts with at least one comment with rating greater than or equal to 4
    comments { |comment_query| comment_query.rating.gte(4) }

    # Or do the same thing with the block shorthand syntax
    # See "Short one-argument syntax" in https://crystal-lang.org/docs/syntax_and_semantics/blocks_and_procs.html
    comments(&.rating.gte(4))
  end
end

# Or do the same without making a scope method:
PostQuery.new.join_comments.comments(&.rating.gte(4))

These are all the supported join types for associations:

So if Project has_many tasks : Task you’d have join_tasks, inner_join_tasks, etc.

Next: Custom Queries